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:\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

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?

Isaac · Accepted Answer · 2015-10-30 19:17:28Z

10

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

answered Oct 30, 2015 at 19:17

Isaac

2653 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rick James · Accepted Answer · 2019-02-07 02:21:52Z

0

\u2013 is an "en dash". Perhaps some word processor is creating that? Perhaps you would be happy enough with a simple -?

See https://docs.sqlalchemy.org/en/latest/dialects/mysql.html#mysql-unicode

answered Feb 7, 2019 at 2:21

Rick James

144k15 gold badges144 silver badges255 bronze badges

Collectives™ on Stack Overflow

Another UnicodeEncodeError when using pandas method to_sql with MySQL

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related