[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#