python - Another UnicodeEncodeError when using pandas method to_sql with MySQL

Question

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?

Answer 1

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