to_sql throws latin-1 encoding error even though everything seems encoded in utf-8

[alybel]

I want to upload a file from csv to mysql with the following commands

 df = pandas.read_csv('test.csv', encoding = 'utf-8')
engine = sqlalchemy.create(<Mysql-Connection-URI>, encoding = 'utf-8')
df.to_sql('testtable', con = engine)

mysql has utf-8 as global encoding. If I upload the file manually to the database it goes without problems.

using to_sql throws the following errror:
UnicodeEncodeError: 'latin-1' codec can't encode character '\u015e'etc...

Any ideas, where Latin-1 becomes part of the games and how this could be fixed? Thanks for any advice!

[jorisvandenbossche]

What driver, driver version and pandas version are you using?

See also #10963 (there, providing ?charset=utf8 to the connection string solved the issue), and http://stackoverflow.com/questions/32235696/pandas-to-sql-gives-unicode-decode-error (which seems a bug in sqlalchemy pymysql dialect or the pymysql driver)

[alybel]

thanks! so funny, I figured it just by myself just before lunch and actually wrote the above answer you found on stackoverflow before I could return here. Thanks for the quick response!

[jorisvandenbossche]

Aha :-)

Maybe you raise the issue at sqlalchemy or pymysql (not sure which of the two is to blame that the encoding is not passed through properly)