Apply column encoding using sqlalchemy

Question

I use sqlalchemy to create my database schema. I have not succeeded in using utf-8, no matter what I tried.

Below is a minimal python script that recreates my problem:

from sqlalchemy import create_engine, Column, Unicode from sqlalchemy.ext.declarative import declarative_base engine = create_engine('mysql+mysqldb://user:password@localhost/multidic?charset=utf8', echo=True) Base = declarative_base() class MyTableName(Base): __tablename__ = "mytablename" test_column = Column(Unicode(2),primary_key=True) Base.metadata.create_all(engine) 

After running this script, when I look at the database, I see that the encoding is latin1 instead of utf-8:

 mysql> SHOW FULL COLUMNS FROM mytablename; +-------------+------------+-------------------+------+-----+---------+-------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +-------------+------------+-------------------+------+-----+---------+-------+---------------------------------+---------+ | test_column | varchar(2) | latin1_swedish_ci | NO | PRI | NULL | | select,insert,update,references | | +-------------+------------+-------------------+------+-----+---------+-------+---------------------------------+---------+ 1 row in set (0.00 sec) 

I tried changing the type of the created column ( String instead of Unicode ) and also tried to add the encoding = "utf8" argument to the create_engine call, but none of this worked .

So my question is:

How to ensure the use of a given character encoding (utf-8 in my case) in MySQL with sqlalchemy?

Thanks:)

Notes:

I am using sqlalchemy 0.7 and python 2.7; I can update one or both, but only if this is the only solution!

I have mysql 5 and it supports utf-8:

 mysql> show character set where charset="utf8"; +---------+---------------+-------------------+--------+ | Charset | Description | Default collation | Maxlen | +---------+---------------+-------------------+--------+ | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | +---------+---------------+-------------------+--------+ 1 row in set (0.00 sec) 

Answer 1

To specify a specific sort on a column, use the collation parameter for the data type:

 class MyTableName(Base): __tablename__ = "mytablename2" test_column = Column(Unicode(2), primary_key=True) test_column2 = Column(Unicode(2, collation='utf8_bin')) # ^^^^^^^^^^^^^^^^^^^^ 

Remember that MySQL understands this as a set of code points for describing the text, and the sort order with which the text will be indexed; regular suspects like "utf8" or "utf-8" will not be familiar with MySQL (use SHOW COLLATION to view the full list)

 mysql> show full columns from mytablename2; +--------------+------------+-------------------+------+-----+---------+-------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +--------------+------------+-------------------+------+-----+---------+-------+---------------------------------+---------+ | test_column | varchar(2) | latin1_swedish_ci | NO | PRI | NULL | | select,insert,update,references | | | test_column2 | varchar(2) | utf8_bin | YES | | NULL | | select,insert,update,references | | +--------------+------------+-------------------+------+-----+---------+-------+---------------------------------+---------+ 2 rows in set (0.00 sec) mysql> 

Answer 2

For me, the match option does not work.

My connection string:

db = create_engine('mysql+pymysql://user:pass@dbhost/schema?charset=utf8')

Pymysql executed utf8 set names due to encoding, and the database converted utf8 to table encoding, resulting in data loss.

If I left the encoding, the encoding was the default for latin1, and pymysql tried to encode my utf8 strings to latin1 before sending them to the database, thereby throwing UnicodeEncode errors.

This worked for me: session.execute(text("SET NAMES latin1")) so that the database assumes that the utf8 lines that I sent did not need to be converted.