python の sqlite3 で datetime 型を扱う方法

pythonの標準ライブラリ sqlite3 でdatetime型を(擬似的に)扱う方法を紹介します。

SQLite3 にはいわゆる日付型(datetime型)は存在しませんが、
Python側から見るとあたかもSQLiteがdatetime型をもっているかのような振る舞いをさせることができます。

とりあえずコード

こんなかんじで

import sqlite3
import datetime

# DBを開く。適合関数・変換関数を有効にする。
conn = sqlite3.connect(':memory:', 
detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)

# "TIMESTAMP"コンバータ関数 をそのまま ”DATETIME” にも使う
sqlite3.dbapi2.converters['DATETIME'] = sqlite3.dbapi2.converters['TIMESTAMP']

# カーソル生成
cur = conn.cursor()

# datetimeという型名のカラムを持つテーブルを作成
cur.execute("create table mytable(comment text, updated datetime);"

# datetimeなカラムに、文字列表現 と datetime でそれぞれ投入してみる。
cur.executemany("insert into mytable(comment, updated) value (?,?)",
        [["text_formated.", "2014-01-02 23:45:00"],
        ["datetime_class.", datetime.datetime(2014,3,4, 12,34,56)]])


ret = cur.execute("select * from mytable;")

for row in ret.fetchall():
  print "'%s'" % row[0], row[1], type(row[1])

## こんな↓感じで、datetimeとして宣言したカラムはdatetime型を返してくれる。
# text_formated. 2014-01-02 23:45:00 <type 'datetime.datetime'>
# datetime_class. 2014-03-04 12:34:56 <type 'datetime.datetime'>

datetime型に見えるのはPythonから見た時だけで、SQLiteデータベースに格納されている実際のデータ形式は "2014-01-02 23:45:00" や "2014-03-04 12:34:56" という単なる文字列データです。

実はpython標準ライブラリの sqlite3/dbapi2.py 内で、「timestamp」というSQL型名をPythonのdatetimeにしてくれる「変換(converter)関数」があらかじめ定義されています。

仕組み

この自動変換動作は sqlite3.connect() する際に detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES と指定すると有効になります。

converter関数のリストはsqlite3.dbapi2.convertersに入っているので、
sqlite3.dbapi2.converters['DATETIME'] = sqlite3.dbapi2.converters['TIMESTAMP'] というふうに、既に登録されている型名TIMESTAMP用のconverter関数をそのままDATETIME用としても登録します。

上記は datetime.datetime(YYYY,MM,DD,hh,mm,ss)[Python] -> "YYYY-MM-DD hhss"[sqlite3] についてしか説明してません。じゃあ上記の逆パターンである "YYYY-MM-DD hhss"[sqlite3] -> datetime.datetime(YYYY,MM,DD,hh,mm,ss)[Python] はどこでやってんだ、って話については省略します。ざっくりいうとdatetimeを投げた時にSQLite側に文字列として投入してくれる「適合(adapter)関数」というものが sqlite3/dbapi2.py 内であらかじめ定義されています。

詳しいことは、Python標準ライブラリ・リファレンスの 11.13.5.4. デフォルトの適合関数と変換関数 - SQLite を読むといいです。