使用sqlalchemy强制列编码 [英] enforce column encoding with sqlalchemy

问题描述

我正在使用sqlalchemy来创建我的数据库的模式。我没有成功执行使用utf-8，无论我尝试了什么。

I am using sqlalchemy to create the schema of my database. I have no success in enforcing the use of utf-8, no matter what I tried.

这是一个重现我的问题的最小的python脚本：

Here 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)

运行此脚本后，当我查看数据库时，我看到编码是latin1而不是utf-8：

After running this script, when I look into 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)

我已经尝试更改创建的列的类型（ String 而不是 Unicode ），以及尝试在调用 create_engine 中添加参数 encoding =utf8，但没有任何一个。

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

所以，我的问题是：

如何使用sqlalchemy在MySQL中强制使用给定的字符编码（在我的例子中为utf-8）？

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

谢谢：）

我正在使用sqlalchemy 0.7和python 2.7;我可以升级一个或两个，但只有这是唯一的解决方案！

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

我有mysql 5，它支持utf-8：

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)

推荐答案

要指定每列的特定排序规则，请使用 排序规则

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

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

请注意，MySQL将此作为一组代码点来描述文本以及文本将被索引的排序顺序;像utf8或utf-8这样的常见嫌疑人对于MySQL并不熟悉（使用 SHOW COLLATION 查看完整列表）

Mind that MySQL understands this as both the set of codepoints to describe the text as well as the sort order the text will be indexed with; the usual suspects like 'utf8' or 'utf-8' won't be familiar to MySQL (use SHOW COLLATION to see 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> 

这篇关于使用sqlalchemy强制列编码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！