[Bug]pandas.DataFrame导入到MySQL
import pandas as pd from sqlalchemy import create_engine df = pd.read_csv('C:/python/pyproject35/csv_20190108140044.csv', encoding='gb2312') engine = create_engine('mysql+pymysql://hostname:passwork@localhost/work') df.to_sql('worklyreport', con=engine, schema='work', if_exists='append', index=False, index_label=False)
Python3.5版本执行结果如下
Traceback (most recent call last): File "C:\python\Python35\lib\site-packages\sqlalchemy\engine\base.py", line 1170, in _execute_context context) File "C:\python\Python35\lib\site-packages\sqlalchemy\dialects\mysql\mysqldb.py", line 105, in do_executemany rowcount = cursor.executemany(statement, parameters) File "C:\python\Python35\lib\site-packages\pymysql\cursors.py", line 197, in executemany self._get_db().encoding) File "C:\python\Python35\lib\site-packages\pymysql\cursors.py", line 234, in _do_execute_many rows += self.execute(sql + postfix) File "C:\python\Python35\lib\site-packages\pymysql\cursors.py", line 170, in execute result = self._query(query) File "C:\python\Python35\lib\site-packages\pymysql\cursors.py", line 328, in _query conn.query(q) File "C:\python\Python35\lib\site-packages\pymysql\connections.py", line 517, in query self._affected_rows = self._read_query_result(unbuffered=unbuffered) File "C:\python\Python35\lib\site-packages\pymysql\connections.py", line 732, in _read_query_result result.read() File "C:\python\Python35\lib\site-packages\pymysql\connections.py", line 1075, in read first_packet = self.connection._read_packet() File "C:\python\Python35\lib\site-packages\pymysql\connections.py", line 684, in _read_packet packet.check_error() File "C:\python\Python35\lib\site-packages\pymysql\protocol.py", line 220, in check_error err.raise_mysql_exception(self._data) File "C:\python\Python35\lib\site-packages\pymysql\err.py", line 109, in raise_mysql_exception raise errorclass(errno, errval) pymysql.err.InternalError: (1366, "Incorrect string value: '\\xE5\\xB9\\xBF\\xE5\\xB7\\x9E...' for column 'city' at row 1") The above exception was the direct cause of the following exception: Traceback (most recent call last): File "C:\Program Files\JetBrains\PyCharm Community Edition 2018.3.2\helpers\pydev\pydevd.py", line 1741, in <module> main() File "C:\Program Files\JetBrains\PyCharm Community Edition 2018.3.2\helpers\pydev\pydevd.py", line 1735, in main globals = debugger.run(setup['file'], None, None, is_module) File "C:\Program Files\JetBrains\PyCharm Community Edition 2018.3.2\helpers\pydev\pydevd.py", line 1135, in run pydev_imports.execfile(file, globals, locals) # execute the script File "C:\Program Files\JetBrains\PyCharm Community Edition 2018.3.2\helpers\pydev\_pydev_imps\_pydev_execfile.py", line 18, in execfile exec(compile(contents+"\n", file, 'exec'), glob, loc) File "C:/python/pyproject35/venv/weeklyreport.py", line 17, in <module> df.to_sql('worklyreport', con=engine, schema='work', if_exists='append', index=False, index_label=False) File "C:\python\Python35\lib\site-packages\pandas\core\generic.py", line 2130, in to_sql dtype=dtype) File "C:\python\Python35\lib\site-packages\pandas\io\sql.py", line 450, in to_sql chunksize=chunksize, dtype=dtype) File "C:\python\Python35\lib\site-packages\pandas\io\sql.py", line 1127, in to_sql table.insert(chunksize) File "C:\python\Python35\lib\site-packages\pandas\io\sql.py", line 641, in insert self._execute_insert(conn, keys, chunk_iter) File "C:\python\Python35\lib\site-packages\pandas\io\sql.py", line 616, in _execute_insert conn.execute(self.insert_statement(), data) File "C:\python\Python35\lib\site-packages\sqlalchemy\engine\base.py", line 948, in execute return meth(self, multiparams, params) File "C:\python\Python35\lib\site-packages\sqlalchemy\sql\elements.py", line 269, in _execute_on_connection return connection._execute_clauseelement(self, multiparams, params) File "C:\python\Python35\lib\site-packages\sqlalchemy\engine\base.py", line 1060, in _execute_clauseelement compiled_sql, distilled_params File "C:\python\Python35\lib\site-packages\sqlalchemy\engine\base.py", line 1200, in _execute_context context) File "C:\python\Python35\lib\site-packages\sqlalchemy\engine\base.py", line 1413, in _handle_dbapi_exception exc_info File "C:\python\Python35\lib\site-packages\sqlalchemy\util\compat.py", line 265, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=cause) File "C:\python\Python35\lib\site-packages\sqlalchemy\util\compat.py", line 248, in reraise raise value.with_traceback(tb) File "C:\python\Python35\lib\site-packages\sqlalchemy\engine\base.py", line 1170, in _execute_context context) File "C:\python\Python35\lib\site-packages\sqlalchemy\dialects\mysql\mysqldb.py", line 105, in do_executemany rowcount = cursor.executemany(statement, parameters) File "C:\python\Python35\lib\site-packages\pymysql\cursors.py", line 197, in executemany self._get_db().encoding) File "C:\python\Python35\lib\site-packages\pymysql\cursors.py", line 234, in _do_execute_many rows += self.execute(sql + postfix) File "C:\python\Python35\lib\site-packages\pymysql\cursors.py", line 170, in execute result = self._query(query) File "C:\python\Python35\lib\site-packages\pymysql\cursors.py", line 328, in _query conn.query(q) File "C:\python\Python35\lib\site-packages\pymysql\connections.py", line 517, in query self._affected_rows = self._read_query_result(unbuffered=unbuffered) File "C:\python\Python35\lib\site-packages\pymysql\connections.py", line 732, in _read_query_result result.read() File "C:\python\Python35\lib\site-packages\pymysql\connections.py", line 1075, in read first_packet = self.connection._read_packet() File "C:\python\Python35\lib\site-packages\pymysql\connections.py", line 684, in _read_packet packet.check_error() File "C:\python\Python35\lib\site-packages\pymysql\protocol.py", line 220, in check_error err.raise_mysql_exception(self._data) File "C:\python\Python35\lib\site-packages\pymysql\err.py", line 109, in raise_mysql_exception raise errorclass(errno, errval) sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1366, "Incorrect string value: '\\xE5\\xB9\\xBF\\xE5\\xB7\\x9E...' for column 'city' at row 1") [SQL: 'INSERT INTO work.worklyreport (day, city, `V2ALLS1UUpIPMOS`, `V2ALLS1UDownRTPNum`, `V2ALLS1UUpRTPNum`, `V2ALLS1UDownRTPDiscardRate`, `V2ALLS1UUpRTPDiscardRate`) VALUES (%(day)s, %(city)s, %(V2ALLS1UUpIPMOS)s, %(V2ALLS1UDownRTPNum)s, %(V2ALLS1UUpRTPNum)s, %(V2ALLS1UDownRTPDiscardRate)s, %(V2ALLS1UUpRTPDiscardRate)s)'] [parameters: ({'day': '2019-01-07 00:00:00~2019-01-08 00:00:00', 'V2ALLS1UUpRTPNum': 1554413, 'V2ALLS1UUpIPMOS': 4.12, 'V2ALLS1UDownRTPNum': 679448, 'V2ALLS1UDownRTPDiscardRate': 0.25, 'V2ALLS1UUpRTPDiscardRate': 0.89, 'city': '广州市'}, {'day': '2019-01-07 00:00:00~2019-01-08 00:00:00', 'V2ALLS1UUpRTPNum': 35858, 'V2ALLS1UUpIPMOS': 3.81, 'V2ALLS1UDownRTPNum': 48569, 'V2ALLS1UDownRTPDiscardRate': 0.44, 'V2ALLS1UUpRTPDiscardRate': 0.54, 'city': '云浮市'}, {'day': '2019-01-07 00:00:00~2019-01-08 00:00:00', 'V2ALLS1UUpRTPNum': 115330, 'V2ALLS1UUpIPMOS': 4.13, 'V2ALLS1UDownRTPNum': 149272, 'V2ALLS1UDownRTPDiscardRate': 0.95, 'V2ALLS1UUpRTPDiscardRate': 1.19, 'city': '河源市'}, {'day': '2019-01-07 00:00:00~2019-01-08 00:00:00', 'V2ALLS1UUpRTPNum': 441160, 'V2ALLS1UUpIPMOS': 3.87, 'V2ALLS1UDownRTPNum': 486883, 'V2ALLS1UDownRTPDiscardRate': 0.9, 'V2ALLS1UUpRTPDiscardRate': 1.31, 'city': None}, {'day': '2019-01-07 00:00:00~2019-01-08 00:00:00', 'V2ALLS1UUpRTPNum': 46669, 'V2ALLS1UUpIPMOS': 4.15, 'V2ALLS1UDownRTPNum': 63970, 'V2ALLS1UDownRTPDiscardRate': 0.43, 'V2ALLS1UUpRTPDiscardRate': 0.52, 'city': '肇庆市'}, {'day': '2019-01-07 00:00:00~2019-01-08 00:00:00', 'V2ALLS1UUpRTPNum': 144781, 'V2ALLS1UUpIPMOS': 3.97, 'V2ALLS1UDownRTPNum': 137826, 'V2ALLS1UDownRTPDiscardRate': 0.3, 'V2ALLS1UUpRTPDiscardRate': 0.51, 'city': '茂名市'}, {'day': '2019-01-07 00:00:00~2019-01-08 00:00:00', 'V2ALLS1UUpRTPNum': 115433, 'V2ALLS1UUpIPMOS': 4.13, 'V2ALLS1UDownRTPNum': 156973, 'V2ALLS1UDownRTPDiscardRate': 1.24, 'V2ALLS1UUpRTPDiscardRate': 1.45, 'city': '汕尾市'}, {'day': '2019-01-07 00:00:00~2019-01-08 00:00:00', 'V2ALLS1UUpRTPNum': 185585, 'V2ALLS1UUpIPMOS': 4.11, 'V2ALLS1UDownRTPNum': 324682, 'V2ALLS1UDownRTPDiscardRate': 1.45, 'V2ALLS1UUpRTPDiscardRate': 1.31, 'city': '汕头市'} ... displaying 10 of 198 total bound parameter sets ... {'day': '2018-12-30 00:00:00~2018-12-31 00:00:00', 'V2ALLS1UUpRTPNum': 25479, 'V2ALLS1UUpIPMOS': 4.16, 'V2ALLS1UDownRTPNum': 16971, 'V2ALLS1UDownRTPDiscardRate': 0.23, 'V2ALLS1UUpRTPDiscardRate': 0.56, 'city': '肇庆市'}, {'day': '2018-12-30 00:00:00~2018-12-31 00:00:00', 'V2ALLS1UUpRTPNum': 22251, 'V2ALLS1UUpIPMOS': 4.23, 'V2ALLS1UDownRTPNum': 7594, 'V2ALLS1UDownRTPDiscardRate': 0.04, 'V2ALLS1UUpRTPDiscardRate': 0.21, 'city': '江门市'})] (Background on this error at: http://sqlalche.me/e/2j85)
#Python##数据库工程师##数据分析师##MySQL#