使用SQLAlchemy和pymysql，如何设置连接以利用utf8mb4? [英] Using SQLAlchemy and pymysql, how can I set the connection to utilize utf8mb4?

问题描述

我(艰难地)发现 MySQL的UTF8字符集只有3个字节.一些研究表明，可以通过更改表以使用

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.

我已经这样做了.我的数据库，表和列均已ALTER使用该字符集.但是，如果我的数据的unicode代码点大于U + FFFF，我仍然会收到此消息:

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

通过更改my.cnf来设置collation_server.我的问题是，如何更改连接之一?我目前使用SQL Alchemy和pymysql这样连接到数据库:

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)

通过SQL Alchemy连接时，我该怎么做从utf8_general_ci更改为utf8mb4_general_ci?

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

推荐答案

将connect_string更改为使用charset=utf8mb4:

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

这篇关于使用SQLAlchemy和pymysql，如何设置连接以利用utf8mb4?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！