Python: SQLAlchemy + mysqlclient (MySQLdb) でマルチバイト文字を扱う

今回は Python の O/R マッパーである SQLAlchemy と MySQL ドライバの mysqlclient を使ってマルチバイト文字を扱うときの注意点について書いてみる。ただし RDBMS については MySQL ではなく、代わりに MariaDB を使った。注意点というのを先に書いてしまうと、接続先 URL で charset を指定するのを忘れないようにしましょうというところ。

使った環境は次の通り。

$ cat /etc/redhat-release
CentOS Linux release 7.2.1511 (Core)
$ uname -r
3.10.0-327.18.2.el7.x86_64

MariaDB をセットアップする

まずは下準備として MariaDB をインストールして設定する。

CentOS 7 では標準の RDBMS が MySQL ではなく MariaDB になっている。

$ sudo yum -y install mariadb-server

インストールされたバージョンは次の通り。現在 MariaDB の安定版には 5.5 系、10.0 系、10.1 系がある。

$ rpm -qa | grep -i mariadb-server
mariadb-server-5.5.47-1.el7_2.x86_64

MariaDB のサービスを起動する。

$ sudo systemctl start mariadb
$ sudo systemctl enable mariadb

デフォルトでは文字コードがクライアントは utf8 でサーバは latin-1 になっている。

$ mysql -u root -e "show variables like 'character%'"
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

サーバについては、このままだと不都合があるので utf8mb4 にしたい。これは 4 バイト対応の UTF-8 文字コードセットで、ただの utf8 のスーパーセットになっている。

$ mysql -u root -e "show character set where Charset = 'utf8mb4'"
+---------+---------------+--------------------+--------+
| Charset | Description   | Default collation  | Maxlen |
+---------+---------------+--------------------+--------+
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci |      4 |
+---------+---------------+--------------------+--------+

サーバの文字コードが utf8mb4 になるよう設定ファイルを書き換える。

$ sudo sed -i -e "/^\[server\]$/a character-set-server=utf8mb4" /etc/my.cnf.d/server.cnf
$ grep -A 1 "\[server\]" /etc/my.cnf.d/server.cnf
[server]
character-set-server=utf8mb4

ついでにクライアントについても書き換えておく。

$ sudo sed -i -e "/^\[client\]$/a default-character-set=utf8mb4" /etc/my.cnf.d/client.cnf
$ grep -A 1 "\[client\]" /etc/my.cnf.d/client.cnf
[client]
default-character-set=utf8mb4

設定できたら MariaDB のサービスを再起動する。

$ sudo systemctl restart mariadb

文字コードが utf8mb4 になっていることを確認しよう。 character_set_system が utf8 のままだけど、ここは固定値らしいので気にしなくても良さそう。

$ mysql -u root -e "show variables like 'character%'"
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

今回の動作確認に使うデータベースを sample という名前で用意する。

$ mysql -uroot -e "CREATE DATABASE IF NOT EXISTS sample"

ちゃんとデフォルトの文字コードである utf8mb4 を使うようになっている。

$ mysql -uroot -e "SHOW CREATE DATABASE sample"
+----------+--------------------------------------------------------------------+
| Database | Create Database                                                    |
+----------+--------------------------------------------------------------------+
| sample   | CREATE DATABASE `sample` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+--------------------------------------------------------------------+

もし、グローバルの設定を変更せずに utf8mb4 を使いたいときはデータベースを作るときにも指定できる。

$ mysql -uroot -e "CREATE DATABASE IF NOT EXISTS sample CHARACTER SET utf8mb4"

あるいは、既にあるデータベースを変更しても良い。

$ mysql -uroot -e "ALTER DATABASE sample DEFAULT CHARACTER SET utf8mb4"

以上で MariaDB の設定はおわり。

SQLAlchemy と mysqlclient をインストールする

次は Python パッケージの設定に入る。

まずは CentOS7 にデフォルトで yum 管理の setuptools が入っていたら、それをアンインストールしておこう。

$ sudo yum -y remove python-setuptools

代わりに get-pip.py を使って最新版の pip と setuptools をインストールしてしまう。

$ curl https://bootstrap.pypa.io/get-pip.py | sudo python

次に pip を使って SQLAlchemy をインストールする。

$ sudo pip install SQLAlchemy

mysqlclient の拡張モジュールをビルドするのに必要なパッケージをインストールする。

$ sudo yum -y install mariadb-devel python-devel

そして mysqlclient をインストールする。

$ sudo pip install mysqlclient

マルチバイト文字を含むレコードを追加する

さて、ここからやっと本題に入れる。 SQLAlchemy を使った Python スクリプトを書いてマルチバイト文字を含むレコードを追加したい。

最初のサンプルコードとして、次のようなものを用意した。 User クラスが RDB のテーブルのモデルとなる。そのインスタンスに「山田太郎」という文字列を入れて永続化を試みている。ちなみに、これはまだ問題を抱えたプログラムになっている。

$ cat << 'EOF' > multibyte0.py
#!/usr/bin/env python
# -*- coding: utf-8 -*-

from __future__ import unicode_literals

from sqlalchemy.engine import create_engine
from sqlalchemy.ext.declarative.api import declarative_base
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.sql.schema import Column
from sqlalchemy.sql.sqltypes import Integer
from sqlalchemy.sql.sqltypes import Text

Base = declarative_base()


class User(Base):
    __tablename__ = 'users'

    # 主キー
    id = Column(Integer, primary_key=True)
    # 名前
    name = Column(Text, nullable=False)


def main():
    engine = create_engine(
        'mysql://root@localhost/sample',
        echo=True,
    )
    Base.metadata.create_all(engine)
    SessionMaker = sessionmaker(
        bind=engine,
        autocommit=True,
        expire_on_commit=False,
    )
    session = SessionMaker()
    with session.begin(subtransactions=True):
        user = User(name='山田太郎')
        session.add(user)


if __name__ == '__main__':
    main()
EOF

また、最初に __future__ パッケージの unicode_literals をインポートしているので、文字列リテラルはすべて unicode 型になっている点に注意が必要。 CentOS 7 の Python はバージョンが 2.7 系なのでマルチバイト文字を扱うときは、これをインポートしておいた方が良い。プログラムの中で扱う文字列を unicode 型に統一しておくと後からの手間が色々と少なくなる。

さて、それでは上記を実行してみよう。しかし、これは例外になる。

$ python multibyte0.py
...(省略)...
File "/usr/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
  cursor.execute(statement, parameters)
File "/usr/lib64/python2.7/site-packages/MySQLdb/cursors.py", line 207, in execute
  args = tuple(map(db.literal, args))
File "/usr/lib64/python2.7/site-packages/MySQLdb/connections.py", line 304, in literal
  s = self.escape(o, self.encoders)
File "/usr/lib64/python2.7/site-packages/MySQLdb/connections.py", line 222, in unicode_literal
  return db.literal(u.encode(unicode_literal.charset))
UnicodeEncodeError: 'latin-1' codec can't encode characters in position 0-3: ordinal not in range(256)

上記ではマルチバイト文字が含まれる unicode 型の文字列を latin-1 (ASCII) でエンコードしようとして例外になっている。

であれば、エンコード済みの UTF-8 を書き込めば良いのでは、という発想で行ってみる。今度は SQLAlchemy の Engine を作る段階で convert_unicode=True をつけてみよう。ちなみに、この修正でもまだ問題が残っている。

$ cat << 'EOF' > multibyte1.py
#!/usr/bin/env python
# -*- coding: utf-8 -*-

from __future__ import unicode_literals

from sqlalchemy.engine import create_engine
from sqlalchemy.ext.declarative.api import declarative_base
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.sql.schema import Column
from sqlalchemy.sql.sqltypes import Integer
from sqlalchemy.sql.sqltypes import Text

Base = declarative_base()


class User(Base):
    __tablename__ = 'users'

    # 主キー
    id = Column(Integer, primary_key=True)
    # 名前
    name = Column(Text, nullable=False)


def main():
    engine = create_engine(
        'mysql://root@localhost/sample',
        echo=True,
        convert_unicode=True,
    )
    Base.metadata.create_all(engine)
    SessionMaker = sessionmaker(
        bind=engine,
        autocommit=True,
        expire_on_commit=False,
    )
    session = SessionMaker()
    with session.begin(subtransactions=True):
        user = User(name='山田太郎')
        session.add(user)


if __name__ == '__main__':
    main()
EOF

実行してみよう。今度は例外にならない。

$ python multibyte1.py
...(省略)...
2016-06-07 23:20:43,771 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2016-06-07 23:20:43,772 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name) VALUES (%s)
2016-06-07 23:20:43,772 INFO sqlalchemy.engine.base.Engine ('\xe5\xb1\xb1\xe7\x94\xb0\xe5\xa4\xaa\xe9\x83\x8e',)
2016-06-07 23:20:43,773 INFO sqlalchemy.engine.base.Engine COMMIT

ちなみに、上記で使われたバイト列は「山田太郎」を UTF-8 でエンコードした内容と一致する。

$ python
>>> from __future__ import unicode_literals
>>> name = u'山田太郎'
>>> name.encode('utf-8')
'\xe5\xb1\xb1\xe7\x94\xb0\xe5\xa4\xaa\xe9\x83\x8e'

これでバッチリでしょうと思いながら MariaDB に入った内容を確認すると、見事に文字化けしている。

$ mysql -u root -D sample -e "SELECT * FROM users\G"
*************************** 1. row ***************************
  id: 1
name: å±±ç”°å¤ªéƒŽ

これは、後述する観測結果から、どうやら mysqlclient と MariaDB の接続に使われる文字コードが latin-1 (ASCII) になっているからのように思える。

じゃあ、どうしたら良いのかというと接続に使う URL の指定に一工夫が必要だった。データベース名の末尾に「?charset=utf8mb4」という形で文字コードを指定してやらなきゃいけない。

$ cat << 'EOF' > multibyte2.py
#!/usr/bin/env python
# -*- coding: utf-8 -*-

from __future__ import unicode_literals

from sqlalchemy.engine import create_engine
from sqlalchemy.ext.declarative.api import declarative_base
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.sql.schema import Column
from sqlalchemy.sql.sqltypes import Integer
from sqlalchemy.sql.sqltypes import Text

Base = declarative_base()


class User(Base):
    __tablename__ = 'users'

    # 主キー
    id = Column(Integer, primary_key=True)
    # 名前
    name = Column(Text, nullable=False)


def main():
    engine = create_engine(
        'mysql://root@localhost/sample?charset=utf8mb4',
        echo=True,
    )
    Base.metadata.create_all(engine)
    SessionMaker = sessionmaker(
        bind=engine,
        autocommit=True,
        expire_on_commit=False,
    )
    session = SessionMaker()
    with session.begin(subtransactions=True):
        user = User(name='山田太郎')
        session.add(user)


if __name__ == '__main__':
    main()
EOF

ちなみに Engine を作るときの convert_unicode=True については、あってもなくても良さそう。

実行してみる。今度はユニコード文字列がそのまま書き込まれているようだ。

$ python multibyte2.py
...(省略)...
2016-06-07 23:24:03,562 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2016-06-07 23:24:03,563 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name) VALUES (%s)
2016-06-07 23:24:03,563 INFO sqlalchemy.engine.base.Engine (u'\u5c71\u7530\u592a\u90ce',)
2016-06-07 23:24:03,564 INFO sqlalchemy.engine.base.Engine COMMIT

永続化された内容を確認しておこう。今度はちゃんと文字化けせずにレコードが入っている。

$ mysql -u root -D sample -e "SELECT * FROM users\G"
*************************** 1. row ***************************
  id: 1
name: å±±ç”°å¤ªéƒŽ
*************************** 2. row ***************************
  id: 2
name: 山田太郎

めでたしめでたし、と行きたいところだけど原因をもうちょっと調べておかないとね。「?charset=utf8mb4」があるときとないときで、何が変わっているのかを見てみよう。おそらく SQLAlchemy が操作する MySQL ドライバ (mysqlclient) の設定が変わるんだろうなとアタリをつけて調べていった。

ここからは mysqlclient のソースコードについて。どうやら mysqlclient では mysqlclient.connections.Connection をインスタンス化するときに charset の設定があるらしい。ちゃんと細部までソースコードを追うことはできていないけど、たぶんこれかな。

https://github.com/PyMySQL/mysqlclient-python/blob/master/MySQLdb/connections.py#L130,L133

Python の REPL で確認してみよう。まずは「?charset=utf8mb4」がないとき。作成した SQLAlchemy の Engine からは raw_connection() メソッドでドライバの生のコネクションをラップしたオブジェクトが得られる。

$ python
>>> from MySQLdb.constants import FIELD_TYPE
>>> engine = create_engine('mysql://root@localhost/sample')
>>> raw_conn = engine.raw_connection()

そこからさらに connection メンバを参照すると、ここに生のドライバが入っている。

>>> mysqldb_conn = raw_conn.connection

文字コードは character_set_name() というメソッドで取得した内容が使われる雰囲気。それを確認すると latin1 となっている。たしかに、これだとマルチバイト文字は扱えなさそう。根本的な問題はこれだったはず。

>>> mysqldb_conn.character_set_name()
'latin1'

次にあるとき。こちらはちゃんと utf8mb4 が指定されている。やはり接続先 URL に charset の指定があるかないかでドライバの設定が変わっていた。

>>> engine = create_engine('mysql://root@localhost/sample?charset=utf8mb4')
>>> raw_conn = engine.raw_connection()
>>> mysqldb_conn = raw_conn.connection
>>> mysqldb_conn.character_set_name()
'utf8mb4'

めでたしめでたし。