Using SQLAlchemy and pymysql, how can I set the connection to utilize utf8mb4?

Question

I discovered (the hard way) that MySQL's UTF8 character set is only 3 bytes. A bit of research shows I can fix this by changing the tables to utilize the utf8mb4 collation and get the full 4 bytes UTF should be.

I've done so. My database, tables and columns have all been ALTERed to utilize this charset. However, I still receive this message if I have data that has unicode code points larger than U+FFFF:

Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='"

I discovered I have the following settings:

> show variables like '%collation%';

collation_connection  utf8_general_ci
collation_database    utf8mb4_general_ci
collation_server      utf8mb4_general_ci

The collation_server was set by making changes to my.cnf. My question, is how do I change the connection one? I currently connect to the database using SQL Alchemy and pymysql like this:

connect_string = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(DB_USER, DB_PASS, DB_HOST, DB_PORT, DATABASE)
engine = create_engine(connect_string, convert_unicode=True, echo=False)
session = sessionmaker()
session.configure(bind=engine)

What can I do to change from utf8_general_ci to utf8mb4_general_ci when connecting via SQL Alchemy?

Interesting. I was looking for how to define the column in sqlalchemy. How did you do that? — Peter Moore
– Peter Moore, Commented Sep 13, 2021 at 17:54

doru · Accepted Answer · 2015-07-08 14:11:14Z

44

Change the connect_string to use charset=utf8mb4:

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

answered Jul 8, 2015 at 14:11

doru

9,1502 gold badges37 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ciasto piekarz Over a year ago

is it possible to pass DATABASE later to the engine?

Vladimir Osinskiy Over a year ago

The suggestion didn't work in my case, but the other suggestion helped, check the post stackoverflow.com/questions/3513773/…

Collectives™ on Stack Overflow

Using SQLAlchemy and pymysql, how can I set the connection to utilize utf8mb4?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related