Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

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:[email protected]/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:[email protected]/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:[email protected]/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:[email protected]/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:[email protected]/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?

like image 645
toto_tico Avatar asked Jul 24 '17 11:07

toto_tico


People also ask

What is Create_engine in SQLAlchemy?

The create_engine() method of sqlalchemy library takes in the connection URL and returns a sqlalchemy engine that references both a Dialect and a Pool, which together interpret the DBAPI's module functions as well as the behavior of the database.

Does pandas use SQLAlchemy?

Pandas in Python uses a module known as SQLAlchemy to connect to various databases and perform database operations.


2 Answers

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) 
like image 161
IT man Avatar answered Sep 19 '22 20:09

IT man


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.

like image 35
univerio Avatar answered Sep 21 '22 20:09

univerio