在MySQL上使用pandas方法to_sql时的另一个UnicodeEncodeError [英] Another UnicodeEncodeError when using pandas method to_sql with MySQL

问题描述

几天前我在堆栈溢出中发布了

I posted on stack overflow a few days ago with a similar problem (which was solved), and I'm not sure what the proper etiquette is here, but I'm making a new post.

基本上，当我尝试将pandas DataFrame写入MySQL数据库时，出现了UnicodeEncodeError.我可以使用以下代码重现该错误:

Basically, I am getting a UnicodeEncodeError when I try to write a pandas DataFrame to a MySQL database. I can reproduce the error with the following code:

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine('mysql://root:@localhost/testdb')
df = pd.DataFrame([[u'\u2013',2],['e',4]], index = ['a','b'], columns = ['c','d'])
df.to_sql('data', engine, if_exists = 'replace', index = False)

这是错误:

UnicodeEncodeError: 'latin-1' codec can't encode character u'\u2013' in position 0: ordinal not in range(256)

这是回溯的最后相关行:

And this is the last relevant line of the traceback:

C:\Anaconda\lib\site-packages\sqlalchemy\dialects\mysql\mysqldb.pyc in do_executemany(self, cursor, statement, parameters, context)
     93 
     94     def do_executemany(self, cursor, statement, parameters, context=None):
---> 95         rowcount = cursor.executemany(statement, parameters)
     96         if context is not None:
     97             context._rowcount = rowcount

当我之前遇到此问题时，这是由于pandas.io.sql中的错误所致，而解决方法是更改​​

When I was having this issue before, it was due to a bug in pandas.io.sql, and the fix was to change a few lines of code. This worked fine until I encountered characters outside the range of the latin-1 codec.

你们有什么建议吗?

推荐答案

好吧，在发布我的问题后的一个小时内，我已经知道了.在发布之前，也许我应该做更多的研究.

Well, within an hour of posting my question, I already figured it out. Maybe I should have done a bit more research before posting.

问题在于需要将sqlalchemy配置为使用utf-8编码.上面代码中的解决方案是将第3行更改为:

The problem is that sqlalchemy needs to be configured to use utf-8 encoding. The solution in the above code would be to change line 3 to:

engine = create_engine('mysql://root:@localhost/testdb?charset=utf8', encoding = 'utf-8')

这篇关于在MySQL上使用pandas方法to_sql时的另一个UnicodeEncodeError的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！