使用df.to_sql()使用pyodbc写入MySQL时出现未知编码

Question

我已经使用pyodbc和sqlalchemy创建了一个到MySQL数据库的连接。但当我使用pd.to_sql()时，它会给我一个错误。这个错误看起来像熊猫正在尝试进行一些编码。正在转换的参数为字符串数据类型，DATABASE编码为latin-1。但是，当我使用相同的连接执行connection.execute(insert query,params)时，它运行良好。此外，当我使用具有sqlalchemy和mysqlconnector的连接的pd.to_sql()时，它有效地工作。

params = urllib3.parse.quote_plus("DRIVER={MySQL ODBC 8.0 ANSI Driver};"
f"SERVER={host}:{port};"
f"DATABASE={db};"
f"UID={username};"
f"PWD={password};"
f"charset=utf8")
db_engine = create_engine(f"mysql+pyodbc:///?odbc_connect={params}")
connection = db_engine.connect()
# main_df is a pd.DataFrame(). It contains a long text field which is most of the time getting affected. 
# the error mostly come from this column. 
maindf = pd.DataFrame()
maindf['transcript'] = ['This is a sample 1', 'This is sample2']
maindf.to_sql("mytable", connection, if_exists="append", index=False, chunksize=1000)

错误如下：

File "C:ProgramDataAnaconda3envsnice_rpalibsite-packagessqlalchemyenginebase.py", line 1685, in _execute_context
self.dialect.do_executemany(
File "C:ProgramDataAnaconda3envsnice_rpalibsite-packagessqlalchemyenginedefault.py", line 713, in do_executemany
cursor.executemany(statement, parameters)
pyodbc.ProgrammingError: ('42000', "[42000] [MySQL][ODBC 8.0(a) Driver][mysqld-5.7.31-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '4' at line 1 (1064) (SQLParamData)")
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "E:/myusername/cx-index-score/nice_rpa/pipeline.py", line 17, in <module>
uploader.split_upload(os.path.abspath(Path('./datasets')))
File "E:myusernamecx-index-scorenice_rpaprocessandupload.py", line 163, in split_upload
self.writetosandbox()
File "E:myusernamecx-index-scorenice_rpaprocessandupload.py", line 216, in writetosandbox
self.maindf.to_sql("nice_daily_update", self.connection, if_exists="append",
File "C:ProgramDataAnaconda3envsnice_rpalibsite-packagespandascoregeneric.py", line 2779, in to_sql
sql.to_sql(
File "C:ProgramDataAnaconda3envsnice_rpalibsite-packagespandasiosql.py", line 590, in to_sql
pandas_sql.to_sql(
File "C:ProgramDataAnaconda3envsnice_rpalibsite-packagespandasiosql.py", line 1405, in to_sql
raise err
File "C:ProgramDataAnaconda3envsnice_rpalibsite-packagespandasiosql.py", line 1397, in to_sql
table.insert(chunksize, method=method)
File "C:ProgramDataAnaconda3envsnice_rpalibsite-packagespandasiosql.py", line 831, in insert
exec_insert(conn, keys, chunk_iter)
File "C:ProgramDataAnaconda3envsnice_rpalibsite-packagespandasiosql.py", line 748, in _execute_insert
conn.execute(self.table.insert(), data)
File "C:ProgramDataAnaconda3envsnice_rpalibsite-packagessqlalchemyenginebase.py", line 1200, in execute
return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
File "C:ProgramDataAnaconda3envsnice_rpalibsite-packagessqlalchemysqlelements.py", line 313, in _execute_on_connection
return connection._execute_clauseelement(
File "C:ProgramDataAnaconda3envsnice_rpalibsite-packagessqlalchemyenginebase.py", line 1389, in _execute_clauseelement
ret = self._execute_context(
File "C:ProgramDataAnaconda3envsnice_rpalibsite-packagessqlalchemyenginebase.py", line 1748, in _execute_context
self._handle_dbapi_exception(
File "C:ProgramDataAnaconda3envsnice_rpalibsite-packagessqlalchemyenginebase.py", line 1929, in _handle_dbapi_exception
util.raise_(
File "C:ProgramDataAnaconda3envsnice_rpalibsite-packagessqlalchemyutilcompat.py", line 211, in raise_
raise exception
File "C:ProgramDataAnaconda3envsnice_rpalibsite-packagessqlalchemyenginebase.py", line 1685, in _execute_context
self.dialect.do_executemany(
File "C:ProgramDataAnaconda3envsnice_rpalibsite-packagessqlalchemyenginedefault.py", line 713, in do_executemany
cursor.executemany(statement, parameters)
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [MySQL][ODBC 8.0(a) Driver][mysqld-5.7.31-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '33x03@333333x03@333333x03@' at line 1 (1064) (SQLParamData)")

如果您看到语法错误部分，它实际上应该是一个简单的字符串，即utf-8。数据库编码为"latin1"。有趣的是，即使每次数据和误差都是相同的；语法错误"；零件总是在变化。一次是4，然后是33\x03@333333\x03@333333\x03@'，每次运行时都会发生变化，但输入数据始终相同。

你知道如何停止熊猫预处理我的参数，然后再发送到数据库吗？如果这不可能，你能建议一个替代方案来有效地写多列(范围在1000秒内(吗？

Answer 1

我使用了错误的驱动程序。服务器中的驱动程序是MySQL ODBC 5.1 Driver，我在MySQL ODBC 8.0 ANSI Driver中使用。这就解释了奇怪的编码。

params = urllib3.parse.quote_plus("DRIVER={MySQL ODBC 5.1 Driver};"
f"SERVER={host}:{port};"
f"DATABASE={db};"
f"UID={username};"
f"PWD={password};"
f"charset=utf8")
db_engine = create_engine(f"mysql+pyodbc:///?odbc_connect={params}")
connection = db_engine.connect()