Search code examples
mysqlexcelutf-8pandaslatin1

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

I am loading data into a pandas dataframe from an excel workbook and am attempting to push it to a database when I get the above error.

I thought at first the collation of the database was at issue which I changed to utf8_bin

Next I checked the database engine create statement on my end which I added a parameter for the encoding too. 

engine = create_engine('mysql+pymysql://root@localhost/test', encoding="utf-8")

But neither of these things work I am still getting the error from the line:

df.to_sql("strand", engine, if_exists="append", index=False)

I checked if there was an encoding parameter for the to_sql method but this does not seem to be the case.

Solution

  • Apparently I needed to add ?charset-utf8 to the query string as well as the encoding variable which resulted in me ending up wht the engine create statement

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