如何配置sqlalchemy以正确存储表情符号？

Question

我在使用sqlalchemy 0.9.7的时候，想把表情符号存储到启用了utf8mb4的MySQL 5.5中。但是，不知道为什么，sqlalchemy把我的表情符号给搞没了，我也搞不清楚原因。在我尝试通过sqlalchemy保存数据之前，我能在对象中看到表情符号。可是保存之后，就出现了错误，表情符号显示成了????。

错误信息如下。注意，这些信息被调试消息包围，调试消息输出了 type(post.message) 和 post.message。

--------------------------------------------------------------------------------
DEBUG in __init__ [/mnt/hgfs/crw/dev/hyper/hyper/blueprint/chat/__init__.py:274]:
<type 'unicode'>
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
DEBUG in __init__ [/mnt/hgfs/crw/dev/hyper/hyper/blueprint/chat/__init__.py:275]:

--------------------------------------------------------------------------------
/mnt/hgfs/crw/dev/hyper/env/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py:436: Warning: Incorrect string value: '\xF0\x9F\x98\x83' for column 'message' at row 1
  cursor.execute(statement, parameters)
--------------------------------------------------------------------------------
DEBUG in __init__ [/mnt/hgfs/crw/dev/hyper/hyper/blueprint/chat/__init__.py:277]:
<type 'unicode'>
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
DEBUG in __init__ [/mnt/hgfs/crw/dev/hyper/hyper/blueprint/chat/__init__.py:278]:
????
--------------------------------------------------------------------------------

我的代码是这样的：

Base = declarative_base()

post = Table('post', Base.metadata,
        Column("id", Integer, primary_key=True),
        Column("message", UnicodeText),
        Column("created_at", DateTime),
        Column("updated_at", DateTime),
    )

mapper(Post, post)

我还需要做其他设置才能让这个工作吗？

更新：如果我这样做：

Base = declarative_base()

post = Table('post', Base.metadata,
        Column("id", Integer, primary_key=True),
        Column("message", UnicodeText),
        Column("created_at", DateTime),
        Column("updated_at", DateTime),
        mysql_engine='InnoDB',
        mysql_charset='utf8mb4'
    )

mapper(Post, post)

我得到了以下结果：

--------------------------------------------------------------------------------
DEBUG in __init__ [/mnt/hgfs/crw/dev/hyper/hyper/blueprint/chat/__init__.py:274]:
<type 'unicode'>
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
DEBUG in __init__ [/mnt/hgfs/crw/dev/hyper/hyper/blueprint/chat/__init__.py:275]:

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
DEBUG in __init__ [/mnt/hgfs/crw/dev/hyper/hyper/blueprint/chat/__init__.py:277]:
<type 'unicode'>
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
DEBUG in __init__ [/mnt/hgfs/crw/dev/hyper/hyper/blueprint/chat/__init__.py:278]:
????
--------------------------------------------------------------------------------

没有错误，但表情符号还是消失了。到底发生了什么？!

更新2：

我把调用的数据库URI从：

mysql+mysqldb://user:pass@localhost/datab?charset=utf8

改成

mysql+mysqldb://user:pass@localhost/datab

解决了这个问题。当我尝试使用 charset=utf8mb4&use_unicode=0 时，sqlalchemy抛出了一个错误。但这样做明智吗？根据这个链接，似乎并不明智！希望能得到对此解决方案的反馈。

Answer 1

下面是我让表情符号等正常工作的步骤。我使用的是 Python 3.5 / Flask / Flask-SQLAlchemy。

注意：这个解决办法假设你在开发的早期阶段，愿意通过运行 db.drop_all() 和 db.create_all() 来重新创建你的数据库。

1. 按照 这个指南 的建议，打开数据库控制台，运行 ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;。

2. 根据 这个回答 的建议，在你的 SQLALCHEMY_DATABASE_URI 字符串末尾添加 ?charset=utf8mb4。

   • 之前是：mysql+mysqlconnector://{username}:{password}@{hostname}/{databasename}
   • 之后是：mysql+mysqlconnector://{username}:{password}@{hostname}/{databasename}?charset=utf8mb4

3. 现在只需重新运行 db.drop_all() 和 db.create_all()。

Answer 2

这是我找到的一个解决方案。set_unicode 这个函数在连接池中第一次建立连接时被调用，而且只会被调用一次。SET NAMES 这个命令基本上是确保从你的代码到数据库存储的整个过程都使用正确的 UTF-8 字符集（utf8mb4）。只要确保整个过程都使用 utf8mb4，那么表情符号就可以正常存储和显示，因为它们只是普通的 Unicode 字符，位于 BMP 之外（基本多语言平面）。不幸的是，MySQL 只实现了 3 字节的 Unicode 编码用于 BMP，这就是为什么会出现问题。

你可以查看 SQLAlchemy 事件参考，了解其他相关的事件钩子，这些可以帮助你进行逐个连接的配置调整。

import logging
from sqlalchemy import event

logger = logging.getLogger(__name__)     

@event.listens_for(Pool, "connect")
def set_unicode(dbapi_conn, conn_record):
    cursor = dbapi_conn.cursor()
    try:
        cursor.execute("SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci'")
    except Exception as e:
        logger.debug(e)

更新：关于连接字符串中的额外选项，我更喜欢明确地指定 charset=utf8mb4，因为我环境的默认字符集是 utf8（这就是表情符号无法正确编码的原因）。但是，除非你在使用 Python 2.x 并且性能是个瓶颈，否则不要指定 use_unicode=0。