how to use `charset` and `encoding` in `create_engine` of SQLAlchemy (to create pandas dataframe)?

Question

I am very confused with the way charset and encoding work in SQLAlchemy. I understand (and have read) the difference between charsets and encodings, and I have a good picture of the history of encodings.

I have a table in MySQL in latin1_swedish_ci (Why? Possible because of this). I need to create a pandas dataframe in which I get the proper characters (and not weird symbols). Initially, this was in the code:

connect_engine = create_engine('mysql://user:password@1.1.1.1/db')
sql_query = "select * from table1"
df = pandas.read_sql(sql_query, connect_engine)

We started having troubles with the Š character (corresponding to the u'\u0160' unicode, but instead we get '\x8a'). I expected this to work:

connect_engine = create_engine('mysql://user:password@1.1.1.1/db', encoding='utf8')

but, I continue getting '\x8a', which, I realized, makes sense given that the default of the encoding parameter is utf8. So, then, I tried encoding='latin1' to tackle the problem:

connect_engine = create_engine('mysql://user:password@1.1.1.1/db', encoding='latin1')

but, I still get the same '\x8a'. To be clear, in both cases (encoding='utf8' and encoding='latin1'), I can do mystring.decode('latin1') but not mystring.decode('utf8').

And then, I rediscovered the charset parameter in the connection string, i.e. 'mysql://user:password@1.1.1.1/db?charset=latin1'. And after trying all possible combinations of charset and encoding, I found that this one work:

connect_engine = create_engine('mysql://user:password@1.1.1.1/db?charset=utf8')

I would appreciate if somebody can explain me how to correctly use the charset in the connection string, and the encoding parameter in the create_engine?

score 19 · Answer 1 · answered Jan 18 '18 at 07:38

19

encoding parameter does not work correctly.

So, as @doru said in this link, you should add ?charset=utf8mb4 at the end of the connection string. like this:

connect_string = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8mb4'.format(DB_USER, DB_PASS, DB_HOST, DB_PORT, DATABASE)

answered Jan 18 '18 at 07:38

IT man

433
5
8

This saved me in an unexpected way. Is there some other know issue where the driver/DBAPI picks latin1 and ignores server-side settings? I have this set on the server, not sure why I need to set it on the connector too to fix things. – Andrew May 15 '19 at 05:30

score 8 · Accepted Answer · answered Jul 24 '17 at 18:14

encoding is the codec used for encoding/decoding within SQLAlchemy. From the documentation:

For those scenarios where the DBAPI is detected as not supporting a Python unicode object, this encoding is used to determine the source/destination encoding. It is not used for those cases where the DBAPI handles unicode directly.

[...]

To properly configure a system to accommodate Python unicode objects, the DBAPI should be configured to handle unicode to the greatest degree as is appropriate [...]

mysql-python handles unicode directly, so there's no need to use this setting.

charset is a setting specific to the mysql-python driver. From the documentation:

This charset is the client character set for the connection.

This setting controls three variables on the server, specifically character_set_results, which is what you are interested in. When set, strings are returned as unicode objects.

Note that this applies only if you have latin1 encoded data in the database. If you've stored utf-8 bytes as latin1, you may have better luck using encoding instead.

thanks @univerio, I still get a bit lost because there is several names and I am not were to place them: *database -> python driver -> mysql-python interface (DBAPI?) -> SQLALchemy*. Is the driver the same as the interface and the DBAPI? — toto_tico, Jul 25 '17 at 07:23
@toto_tico The driver is the same thing as the DBAPI. (Well technically DBAPI is the interface defined by PEP 249, and the driver is the library that implements this interface, but there's no need to be this pedantic.) So it goes *database -> driver/DBAPI -> SQLAlchemy*. — univerio, Jul 25 '17 at 17:34

score 6 · Answer 3 · answered Mar 22 '18 at 10:51

I had the same problem. I just added ?charset=utf8mb4 at the end of the url.

Here is mine:

Before

SQL_ENGINE = sqlalchemy.create_engine('mysql+pymysql://'+MySQL.USER+':'+MySQL.PASSWORD+'@'+MySQL.HOST+':'+str(MySQL.PORT)+'/'+MySQL.DB_NAME)

After

SQL_ENGINE = sqlalchemy.create_engine('mysql+pymysql://'+MySQL.USER+':'+MySQL.PASSWORD+'@'+MySQL.HOST+':'+str(MySQL.PORT)+'/'+MySQL.DB_NAME + "?charset=utf8mb4")

W.Perrin · Answer 4 · 2018-03-08T09:43:49.583

3

This works for me .

from sqlalchemy import create_engine
from sqlalchemy.engine.url import URL

db_url = {
    'database': "dbname",
    'drivername': 'mysql',
    'username': 'myname',
    'password': 'mypassword',
    'host': '127.0.0.1',
    'query': {'charset': 'utf8'},  # the key-point setting
}

engine = create_engine(URL(**db_url), encoding="utf8")

edited Mar 08 '18 at 09:43

answered Mar 08 '18 at 09:21

W.Perrin

2,137
20
22

how to use `charset` and `encoding` in `create_engine` of SQLAlchemy (to create pandas dataframe)?

4 Answers4

Linked