Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
877 views
in Technique[技术] by (71.8m points)

python - 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.

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)

Here is the error:

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:Anacondalibsite-packagessqlalchemydialectsmysqlmysqldb.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

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.

Do you guys have any suggestions?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

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')

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...