SQLAlchemy+MySQL Unicode文字列の怪
Python2.Xで日本語を扱うときUnicode文字列への/からの変換時に面倒な問題があるらしく*1、コード内ではどちらかに統一したいと思うらしいです(そう思います)。しかしSQLAlchemyにはPlane/Unicodeどちらで渡す/から返される文字列はどちらなのか仕組みがよく分かりません。そこで分かる範囲内で少し(現象的に)調べてみました。
結果
以下のように推測します。
| create_engine()のconvert_unicode引数 | True | False |
|---|---|---|
| 渡す文字列 | Unicode型? | DB-APIが受け取れるもの? |
| 返る文字列(String経由) | Unicode型 | DB-APIが返したもの |
| 返る文字列(executeなどDB-API直) | DB-APIが返したもの | DB-APIが返したもの |
特に問題になるのはデータベースのドライバが返したものが何になるのかですが、MySQLdbとSQLiteについては以下のように推測します。
| DB-API | 条件 | 返る型 |
|---|---|---|
| SQLite | なし | unicode |
| MySQLdb | collate *2 がbinary(utf8_binなど) | str |
| MySQLdb | collateがbinary以外でuse_unicode=1 *3 | unicode |
| MySQLdb | collateがbinary以外でuse_unicode=0 | str |
collate値によってunicodeにならないのは不自然と思います。調べてみたら案の定
http://sourceforge.net/tracker/index.php?func=detail&aid=2837134&group_id=22307&atid=374932
バグ登録されてました。1年以上前に....(でもopen
つまりDB-APIのMySQLdbのバグでSQLAlchemyは無罪ということのようです。
根拠
以下の2サイトの情報を元に
http://technolize.blogspot.com/2010/11/sqlalchemy-collate-utf8bin.html
http://groups.google.com/group/sqlalchemy/browse_thread/thread/f4ba7ca61206fa7e/cb47da86cb7856f2
以下のように現象ベースで試しみました。
MySQLについては以下の2パターンで
(1)create database practice CHARACTER SET 'utf8';
(2)create database practice CHARACTER SET 'utf8' COLLATE 'utf8_bin';
以下のコードを実行して
#! -*- coding: utf-8 -*- from itertools import izip from sqlalchemy import Column, String, create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker Base = declarative_base() class Sample(Base): # Sampleクラスとsamplesテーブルとそのマッピングの定義 __tablename__ = 'samples' # テーブル名 __table_args__ = {'mysql_engine':'InnoDB'} # MySQLではInnoDB id = Column(String(10), primary_key=True) # IDカラムの定義 val = Column(String(10)) # VALカラムの定義 def __init__(self, id, val): self.id = id self.val = val # SQLiteとMySQLのデータベースの定義 engine_parameters = ( "sqlite:////home/user1/practice.sqlite", "mysql+mysqldb://root@localhost/practice?charset=utf8&use_unicode=0", "mysql+mysqldb://root@localhost/practice?charset=utf8&use_unicode=1", ) # エンジンの作成 engines = [] names = [] for param in engine_parameters: for conv in (True, False): engines.append(create_engine(param, echo=False, convert_unicode=conv)) scheme = param[0:5] + param[-1] names.append("%s-%s" % (scheme, conv)) # セッションクラスの作成とセッションオブジェクトの作成(SQLiteとMySQL両方) Sessions = [] sessions = [] for engine in engines: # create table Base.metadata.create_all(engine) # セッション生成 Sessions.append(sessionmaker(bind=engine)) sessions.append(Sessions[-1]()) # SQLの実行 for name, session in izip(names, sessions): # insert into samples values(xxx, 'val1') session.add(Sample(u'文字列ID', u"文字列VAL")) selector = session.query(Sample) value = selector.filter(Sample.id == u"文字列ID").one().val print name, "query", type(value), value (value,), = session.execute("select val from samples where id=:id", {"id": u"文字列ID"}) print name, "execute", type(value), value session.rollback() session.close()
以下のようになりました。
(1)collate指定なし
sqlite-True query <type 'unicode'> 文字列VAL sqlite-True execute <type 'unicode'> 文字列VAL sqlite-False query <type 'unicode'> 文字列VAL sqlite-False execute <type 'unicode'> 文字列VAL mysql0-True query <type 'unicode'> 文字列VAL mysql0-True execute <type 'str'> 文字列VAL mysql0-False query <type 'str'> 文字列VAL mysql0-False execute <type 'str'> 文字列VAL mysql1-True query <type 'unicode'> 文字列VAL mysql1-True execute <type 'unicode'> 文字列VAL mysql1-False query <type 'unicode'> 文字列VAL mysql1-False execute <type 'unicode'> 文字列VAL
(2)collate指定がbinary
sqlite-True query <type 'unicode'> 文字列VAL sqlite-True execute <type 'unicode'> 文字列VAL sqlite-False query <type 'unicode'> 文字列VAL sqlite-False execute <type 'unicode'> 文字列VAL mysql0-True query <type 'unicode'> 文字列VAL mysql0-True execute <type 'str'> 文字列VAL mysql0-False query <type 'str'> 文字列VAL mysql0-False execute <type 'str'> 文字列VAL mysql1-True query <type 'str'> 文字列VAL mysql1-True execute <type 'str'> 文字列VAL mysql1-False query <type 'str'> 文字列VAL mysql1-False execute <type 'str'> 文字列VAL
つまり
- SQLiteは全てがunicodeです
- MySQLはuse_unicode=1かつcollateデフォルトで全てunicodeです。
- MySQLはuse_unicode=1かつcollateがbinaryで全てstrです。
- MySQLはuse_unicode=0かつconvert_unicode=Falseでは全てstrです。
- MySQLはuse_unicode=0かつconvert_unicode=Trueではqueryはunicodeです。
- MySQLはuse_unicode=0かつconvert_unicode=Trueではexecuteはstrです。
ということになり、辻褄が合うように一般化すると最初の推測になる、というわけです。
