文章/答案/技术大牛

发布

社区首页 >问答首页 >在使用df.to_sql()通过pyodbc写入MySQL时出现未知编码

问在使用df.to_sql()通过pyodbc写入MySQL时出现未知编码
EN

Stack Overflow用户

提问于 2021-04-19 22:48:59

回答 1查看 138关注 0票数 1

我已经使用pyodbc和sqlalchemy创建了一个到MySQL数据库的连接。但是当我使用pd.to_sql()时，它会给我一个错误。这个错误看起来像是pandas试图做一些编码。要转换的参数是字符串数据类型，数据库编码是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:\ProgramData\Anaconda3\envs\nice_rpa\lib\site-packages\sqlalchemy\engine\base.py", line 1685, in _execute_context
    self.dialect.do_executemany(
  File "C:\ProgramData\Anaconda3\envs\nice_rpa\lib\site-packages\sqlalchemy\engine\default.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:\myusername\cx-index-score\nice_rpa\processandupload.py", line 163, in split_upload
    self.writetosandbox()
  File "E:\myusername\cx-index-score\nice_rpa\processandupload.py", line 216, in writetosandbox
    self.maindf.to_sql("nice_daily_update", self.connection, if_exists="append",
  File "C:\ProgramData\Anaconda3\envs\nice_rpa\lib\site-packages\pandas\core\generic.py", line 2779, in to_sql
    sql.to_sql(
  File "C:\ProgramData\Anaconda3\envs\nice_rpa\lib\site-packages\pandas\io\sql.py", line 590, in to_sql
    pandas_sql.to_sql(
  File "C:\ProgramData\Anaconda3\envs\nice_rpa\lib\site-packages\pandas\io\sql.py", line 1405, in to_sql
    raise err
  File "C:\ProgramData\Anaconda3\envs\nice_rpa\lib\site-packages\pandas\io\sql.py", line 1397, in to_sql
    table.insert(chunksize, method=method)
  File "C:\ProgramData\Anaconda3\envs\nice_rpa\lib\site-packages\pandas\io\sql.py", line 831, in insert
    exec_insert(conn, keys, chunk_iter)
  File "C:\ProgramData\Anaconda3\envs\nice_rpa\lib\site-packages\pandas\io\sql.py", line 748, in _execute_insert
    conn.execute(self.table.insert(), data)
  File "C:\ProgramData\Anaconda3\envs\nice_rpa\lib\site-packages\sqlalchemy\engine\base.py", line 1200, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "C:\ProgramData\Anaconda3\envs\nice_rpa\lib\site-packages\sqlalchemy\sql\elements.py", line 313, in _execute_on_connection
    return connection._execute_clauseelement(
  File "C:\ProgramData\Anaconda3\envs\nice_rpa\lib\site-packages\sqlalchemy\engine\base.py", line 1389, in _execute_clauseelement
    ret = self._execute_context(
  File "C:\ProgramData\Anaconda3\envs\nice_rpa\lib\site-packages\sqlalchemy\engine\base.py", line 1748, in _execute_context
    self._handle_dbapi_exception(
  File "C:\ProgramData\Anaconda3\envs\nice_rpa\lib\site-packages\sqlalchemy\engine\base.py", line 1929, in _handle_dbapi_exception
    util.raise_(
  File "C:\ProgramData\Anaconda3\envs\nice_rpa\lib\site-packages\sqlalchemy\util\compat.py", line 211, in raise_
    raise exception
  File "C:\ProgramData\Anaconda3\envs\nice_rpa\lib\site-packages\sqlalchemy\engine\base.py", line 1685, in _execute_context
    self.dialect.do_executemany(
  File "C:\ProgramData\Anaconda3\envs\nice_rpa\lib\site-packages\sqlalchemy\engine\default.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 '33\x03@333333\x03@333333\x03@' at line 1 (1064) (SQLParamData)")

如果您看到语法错误部分，那么它实际上应该是一个简单的字符串，即utf-8。Databse编码为“latin1”。有趣的是，尽管每次数据和错误都是相同的，但“错误语法”部分总是变化的。有一次，它是'4'，然后是'33\x03@333333\x03@333333\x03@‘，它在每次运行时都会改变，尽管输入数据总是相同的。

你知道怎么阻止pandas在发送到db之前对我的参数进行预处理吗？如果这是不可能的，请您建议一个替代方案，以有效地写入多列(范围在1000秒)？

python

mysql

pandas

sqlalchemy

pyodbc

回答 1

Stack Overflow用户

回答已采纳

发布于 2021-04-21 17:04:58

我用错了驱动程序。服务器中的驱动程序是我在MySQL ODBC 8.0 ANSI Driver中使用的MySQL ODBC 5.1 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()

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/67164247

复制

相似问题

问在使用df.to_sql()通过pyodbc写入MySQL时出现未知编码
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在使用df.to_sql()通过pyodbc写入MySQL时出现未知编码EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在使用df.to_sql()通过pyodbc写入MySQL时出现未知编码
EN