Python + SQLite、面白い（引用だよ）

これは引用だよ
http://tawasi.infogami.com/development/pysqlite_1

Development taoy NG (Next Generation)

Python + SQLite、面白い

いやぁ、Python + SQLite の組み合わせって、なかなか面白そうだねぇ。 Debian では、sqlite3 ってパッケージと、python-pysqlite2 ってパッケージを入れてやれば、それで動作する。

from pysqlite2 import dbapi2 as sqlite

って書いて、Python モジュールをロードしておけば、後は、

con = sqlite.connect("mytest.db")

って感じで接続できちゃう。でもって、

cur = con.cursor()
cur.execute("select * from table1;")

とかで、SQLite3 に対して SQL 文を発行してデータを取得できちゃう。それも Python のオブジェクトとして取得。なので、後はフツーに Python のオブジェクトとして扱える。例えば、

print cur.fetchall()

で、全データ一覧表示。簡単。

んで、execute メソッドの引数がリテラルである必要は当然ないので、

SELECT = "select * from table1 order by id"

なんて定義しておけば、

con.execute(SELECT)

で実行できてしまう。良く使う SELECT 文とか、INSERT 文の部品とか、イチイチ書いてると間違いやすいもんはこうしてあらかじめ決まった名前で登録しちゃえば良いんだな。そうすりゃ、プログラム中のどこでもそれを使えちゃう。

便利だ。

もしかしてもしかすると、これぐらいのことはフツーのプログラミング言語だったら当り前の事なのかもしれないけどさ、私が今迄に自分自身で書いてきたもんだと、こういうのがちょっと面倒だったのだった。喰い物については他人の過去を多少は断罪出来るかもしれんけど、プログラミングについてはまるっきりの悪食だった、ってことかもしれませんね。スマンこってす(誰にだ?)

で。

SQLite ってのが、クライアントサーバ型の RDBMS で _ ない _ お蔭で、ちょっと変わった接続を実現できる。

con = sqlite.connect(":memory:")

これで、メモリ上に SQLite のデータベースを新規に展開しちゃう。私が作ろうとしてるような、利用者が極端に少ないような Web アプリケーションだったら、ユーザがログインしてきたときに、ディスク上のファイルにあるデータをメモリ上にデータベースとして展開しちゃえる、ってことだよね。でもって、python のカーソルもやっぱりメモリ上に展開されるから、そりゃぁ動作も速いだろうよ。

もちろん、大規模で権限の複雑な、要は整合性が可能なかぎり完全である必要のあるような、トランザクションベースの Webアプリケーション、それも同時接続ユーザが数千人以上、なぁんてもんだったら、これをスケーラブルに実現するなんらかの方法と云うか手段を考慮する必要はあるけれど、コト、私がやろうと思ってることに関して云えば、これはけっこうお誂え向きのプラットフォームでございますのではないかしらん。

うーむ。

もちょっと、いろいろと試して悩んでみよう。

last updated 2 years ago [06-10-11] by taoyd23 #

「seraphyの日記」からの引用だよ

「seraphyの日記」からの引用だよ
http://d.hatena.ne.jp/seraphy/20060927
 

そこで、さっそく試してみた。

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つであって、覚えて損はないだろう。