Python2.5でSQLite3を使ってみる。

SQLiteとは

Python2.5には、標準でSQLiteという軽量データベースが含まれている。
SQLiteは、JAVAでいうところのHSQLDBやApache Derbyのような感じのようである。
SQLLiteはデーモンやサービスではなく、スタンドアロンでのみ動作する。（DerbyやHSQLDBは組み込みモードとサーバーモードの両方をもつが。）
また、データベースのストアもディスク上の単一ファイルになる。
Windowsでいうところの、ACCESS95〜2000で使われていた、JETデータベースエンジンのMDBファイルのような感じであろうか。
要するに単なるライブラリである。
ライセンスは、なんとパブリックドメイン（著作権放棄）である。
そのため、Pythonに限らず、C/C++、Perl、PHPなどに広く組み込まれている。
Python2.4まではアドオンとして別途インストールする必要があったらしいが、Python2.5からは標準装備となっていた。
標準ライブラリとして使えることは、大きな意味があると思う。
Python2.5の実行環境さえセットアップすればよいので、データベースを使うスクリプトを作ることも、利用することも、配布することも、どれも大幅に敷居がさがること間違いない。
（JAVAもXMLパーサをバンドルしてヒットしたように、Derbyあたりをバンドルしてくれればいいのに。（J2EEのSDKにはバンドルされているが。））
そこで、さっそく試してみた。
Python2.5では、sqlite3というモジュールをインポートするだけで準備完了である。

基本的な使い方(DMLとトランザクション処理)

SQLiteは、DerbyやHSQLDBと同様に「インメモリ・データベース」として動作することができる。
揮発性の、コネクションを閉じるとデータも消えてしまうものだが、テストプログラムや、スタンドアロンアプリケーションの中で一時データの集計などを行う場合には便利だと思われる。
この「インメモリ・データベース」で、基本的な使い方を実験してみた。
私自身がPython使いではないので無駄（もしかすれば間違いも）あるかもしれないが、
使ってみた感触では、意外と簡単というか、APIはよく出来ている。

#!/usr/bin/env python
# -*- coding: utf-8 -*-

# Python2.5以降のみ
# 13.13 sqlite3 -- DB-API 2.0 interface for SQLite databases および、
# http://www.python.org/dev/peps/pep-0249/
# を参照のこと。

# SQLiteの利用
import sqlite3

# テーブル内容の一覧表示
def dump(conn):
    cur = conn.cursor()
    try:
        cur.execute("select idx, val from testtbl order by idx")
        for row in cur:
            # カーソルは、fetchone,fetchallなどで取得できるほか、
            # それ自身をレコードのリストとして列挙可能である。
            print row
    finally:
        cur.close()


# インメモリデータベースとしてコネクションを作成する。
conn = sqlite3.connect(":memory:")

# 指定がないばあいは、最初のSQL文実行でトランザクションが開始する。（beginは必要ない。）
# Autocommitにするばあいは、isolation_levelをNoneにする。
try:
    # DDLを実行する。
    # executescript()はセミコロンで区切り複数のSQL文を流し込める。
    # (コネクション・オブジェクトに定義されているexecuteメソッドは、一時カーソルを作成して
    # 実行する、コンビニエンスメソッドである。）
    # SQLite3は、Integer/Real/Text/BLOB(Binary Large Object)のみサポート。
    # それ以外の型は、類推して、いずれかに割り当てられる。
    conn.executescript("""create table testtbl(
        idx integer primary key,
        val varchar2(512));""")

    # 「カーソル」は読み取りに限らず、SQLの実行のすべてを行う。
    # （トランザクションは、コネクションで制御する。）
    cur = conn.cursor()
    finished = False
    try:
        for n in range(1, 100):
            # 「？」はバインド変数で、引数をしてタプルなどの列挙可能なオブジェクトを渡す。
            # （タプルのタプルを渡すと、複数行の操作になる。）
            # SQLite3は「INTEGER PRIMARY KEY」のカラムを空にすると自動で割当てる。（ROWID）
            # http://www.sqlite.org/faq.html#q1
            cur.execute("insert into testtbl(val) values(?)", (str(n),))
        finished = True
        conn.commit()
    except:
        if not finished:
            # commitに失敗した場合は、トランザクションはロールバックされて無効になっている（はず）
            conn.rollback()
        raise
    finally:
        cur.close()

    # 内容の確認
    dump(conn)
except Exception, ex:
    print ex
finally:
    conn.close()

#
# インメモリデータベースをもう一度開いてみる。(テーブルが未定義と言われる ＝ 消えている。)
conn2 = sqlite3.connect(":memory:")
try:
    dump(conn2)
except Exception, ex:
    print ex
finally:
    conn2.close()

マルチスレッドでのデータベースへの書き込みアクセス

次に、実際にファイルに書き出しながらマルチスレッドでアクセスを試してみた。
SQLiteはスレッドセーフであるが、connect単位で独立させる必要があるとのこと。
つまり、データベースファイルには複数スレッドからアクセスしてもよいが、そのコネクションは共有してはならない、ということ。
また、コネクション・オブジェクトそのものがトランザクションを管理しているようである。
このあたりはJDBCも同様であるから、とくに驚きはない。

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# Python2.5以降のみ

import threading
import sqlite3

# データベースファイル名
# SQLiteのデータストアは単一のファイルである。
DBNAME = "threadingtest.db"

# データベースの準備
def init_db():
    conn = sqlite3.connect(DBNAME)
    try:
        # テーブルを作成する
        conn.executescript("""create table testtbl(
            idx integer primary key, tname varchar(128), val integer);""")
    except Exception, ex:
        # 作成に失敗したら、作成済みと想定して、既存データを削除する
        conn.execute("delete from testtbl")
        conn.commit()
    finally:
        conn.close()

# コネクションを操作するスレッド
class MyThread(threading.Thread):
    def run(self):
        # SQLiteは、コネクション単位でスレッドセーフである。
        # つまり、コネクションやカーソルをスレッドをまたいで使うことは出来ない。
        # ロックのタイムアウトは、60秒に設定。
        conn = sqlite3.connect(DBNAME, timeout=60000)
        # 1件づつ暗黙でコミットする。（Autocommit）
        conn.isolation_level = None
        try:
            cur = conn.cursor()
            try:
                for n in range(1, 10):
                    cur.execute(
                        "insert into testtbl(tname, val) values(?, ?)",
                        (self.getName(), n))
            finally:
                cur.close()
        except Exception, ex:
            # タイムアウト時間までにロックを獲得できなかった場合は例外が発生する。
            print "thread=%s /exception=%s" % (self.getName(), str(ex))
        finally:
            conn.close()

# メイン
def main():
    try:
        init_db()
        
        # スレッドを作成する
        threads = []
        for n in range(1, 10):
            t = MyThread()
            t.setName("MyThread:%d" % n)
            threads.append(t)

        # スレッドを開始する
        for t in threads:
            t.start()

        # すべてのスレッドの終了を待機する
        for t in threads:
            t.join()

        print "done."

    except Exception, ex:
        print ex

# 書き込み結果の表示
def verify_test():
    conn = sqlite3.connect(DBNAME)
    try:
        cur = conn.cursor()
        try:
            cur.execute("select count(*) from testtbl")
            cnt = cur.fetchone()[0]
            print "count=%d" % cnt
            cur.execute("""select tname, count(val) from testtbl
                group by tname order by tname""")
            for row in cur:
                print row
        finally:
            cur.close()
    except Exception, ex:
        print ex
    finally:
        conn.close()

# 実行
main()
verify_test()

実際に試した感触では、どうやら、トランザクションが開始されるとジャーナルファイルが作成され、コミットするとジャーナルファイルがデータベースファイルにマージされたあと削除され、このジャーナルが存在する期間は、別のスレッドはブロックされてしまうようなのだ。
SERIALIZEレベルの分離レベル、ということ？
いや、これはテーブルロックどころか、データベース全体をロックしているように見える。
まあ、これは見た感触であって、ただの推論だが。

結論

SQLiteは簡単に使えるうえに簡単に壊れたりすることはないようだが、
実感として、マルチスレッドでの書き込みが頻発する状況では、かなり遅いと考えてよさそうである。
しかし、主要な用途をクライアントサイドのスタンドアロンなアプリケーションのバッキングストアとして利用する分には、なんの問題もないだろう。
（BLOBのサイズ、データベースファイルの上限も、かなりでかい。まず、使い切ることはないだろう。）
そのうえで、共有しても壊れない、ファイルコピーで持ち運べる手軽さは魅力的である。
今後、Pythonでプログラムを書くときは、これを使う選択肢も覚えておくべきだろう。

C/C++バインドであるが、Windows版はDLL一個で動作するらしい。
さらに、msvcrt.dllという、VCのランタイムライブラリ以外に依存しない、というからまったく手軽なものである。
ぜひ、C/C++でSQLiteを使う方法も習得してみたいと思わされた。
これは、まちがいなく優れたデータベースの1つであって、覚えて損はないだろう。