seraphyの日記

日記というよりは過去を振り返るときのための単なる備忘録

Pyhon2.5でSQLObjectを使ってみる

Web2.0的システム構築―より進化したシステムの考え方
Web2.0的システム構築―より進化したシステムの考え方をちょっと流し読んでたら、PythonでのO/R MappingとしてSQLObject — SQLObject 3.5.0 documentationの、ものすごい簡単な紹介があったので興味をひかれたので試してみる。

本当にものすごく簡単に紹介していて、ついでに「SQLObjectを使ったようなコードはJavaのような重量級言語では実現できないに違いない、これがスクリプト言語の醍醐味だ」みたいなことが書かれていて、ちょうど、寝る前に試すには良い規模かなー、なんて思ってしまった。
実際には、予定よりも5時間もオーバーして、朝になってしまった。orz

とりあえず、基本的な使いかたは分かった気がするので、いろいろ不足する部分もあるが、ここで一区切りつけたい。

用意するもの

  • Python2.5
    • SQLite3が同梱されているから
  • SQLObject
    • Python2.5のSQLite3を使うにはsqlobject-0.8dev_r2072-py2.5が必要

SQLObjectはEasyInstall - The PEAK Developers' Centerから配布されているEasy Installを使ってセットアップする。

easy_install SQLObject

とやれば、SQLObjectを取得できるのだが、現時点ではsqlobject-0.7b1が取得された。
しかし、この0.7b1バージョンではPython2.5のSQLite3を認識しないため、Python2.5のSQLite3を使うためにはSVNリポジトリから0.8以降をローカルマシンに持ってきて、そのローカルフォルダに対してeasy_installを実行する必要がある。
しばらく待っていれば0.8になると思うので、あまり心配することでもないと思う。

SQLObject0.7b1がPython2.5で動作しない理由

Python2.5(SQLite3)でSQLObject(0.7b1)がどうして使えないのか。
使ってみれば、すぐに「そんなモジュールないよ」というエラーを吐いてくれるので理由も場所も簡単に気づくのであるが、要点は以下のコード sqliteconnection.py(0.8dev) にある。

    try:
        import sqlite3 as sqlite
        using_sqlite2 = True
    except ImportError:
        try:
            from pysqlite2 import dbapi2 as sqlite
            using_sqlite2 = True
        except ImportError:
            import sqlite
            using_sqlite2 = False

最新の0.8版では、sqlite3がインポートできなければ、pysqlite2をインポートして、これもダメならsqliteをインポートする、というコードになっているが、0.7b1版では、sqlite3のインポート部分がないのである。
ここにsqlite3をインポートする部分を書き加えれば動くことは予想ついたし実験もしてみたのだが、そんなやり方をして良いのだろうか、そもそもPython2.5でSQLObjectを使っている人間はいないのであろうか? などという疑問が生ずるので、またgoogleでさまようこと1時間ぐらいは潰してしまった。(日本語のページがぜんぜんないのよ。)
結局、SQLObjectのメーリングリストの中でもPython2.5に対応するためには、この部分を追加しなければならない、みたいなやりとりがあって、それでSVNリポジトリを見たら対応済であったので、やはり、正規の方法としても、ここに修正を加えることが正しい方法なのだろう、と理解。即行、0.7b1は捨てて0.8を入れることにしたのである。
私の試した限りでは、0.7b1でも、上記の部分だけ修正すれば動いたので、まだ正式リリースされていない0.8はイヤだと思うならば自分で書き換えてもよいかもしれない。

SQLObjectの変なクセ(?)

SQLite3でデータベースを作る場合、相対パスでも絶対パスでも問題ないし、Windowsのドライブレターを含む絶対パスでも問題ないはずである。ところが、SQLObjectでは、簡単にはゆかないのである。
まず、SQLObjectでは、使うデータベースの種類をスキーマとして、残りはデータベースへのアクセス方法としてURI形式で指定するのだが、

    scheme://[user[:password]@]host[:port]/database[?parameters]

SQLiteを使う場合には、

    sqlite:///....

のような形式になって、必ず「/」をつけなければならないのである。
すると、相対パスを指定することもできないし、Windows絶対パスを指定しようとすると、

    sqlite:///C:\\temp\\test.db

みたいなことになって、これはデータベースファイル名としてSQLiteには「/C:\\temp\\test.db」という名前で渡されるようなのである。で、もちろん不正なパスなのでデータベースにアクセスできないといってエラーで終わる。
(当然、インメモリデータベースである「sqlite:/:memory:」なんて指定したら、SQLiteには「/:memory:」とわたって、これもダメじゃないのか、と思ったら、SQLObjectのソースをみると「/:memory:」は「:memory:」に変換する、なんて処理がされていて回避可能になっている。なんだよ、それ? ますます不審に思うじゃないか。)
まず、ここで躓いたね。
いろいろ試してみたが、うまい解決方法がない。これは、どう考えてもWindowsを考慮していないバカな設計か、あるいは、ただのバグじゃないのか、と思い始めたころに、もう一度、マニュアルを読み返すと、こんな記述が見つかった。

Examples:

mysql://user:passwd@host/database
mysql://host/database?debug=1
postgres://user@host/database?debug=&cache=
postgres://host:5432/database
sqlite:///full/path/to/database
sqlite:/C|/full/path/to/database
sqlite:/:memory:

Note that there is a special syntax to specify full path on w32 systems (useable for sqlite and firebird); also sqlite can manipulate special in-memory databases.

なんだよ、それ。たしかに、よく見たら「C:」じゃなくて「C|」じゃないか。
「|」に変換すればいいのか? そうなのか!?
SQLite側で、うまいこと吸収できる仕組みがあるからSQLObjectは何もしていなかったというのか。
…どうも、そうらしい。
*1 (まあ、CVSとかでのリポジトリのURL形式での指定でも似たような困った事象になりがちだし、決して、珍しいってわけでもない。)
ということで、素直に従っておくことにする。
SQLObjectとSQLiteの組み合わせでWindows上で動作させる場合には、Windowsのドライブレターの後ろのコロン(:)をバーチカルスコア(|)に変換しておくこと。

SQLObjectでトランザクションを使う

SQLObjectも動くようになったし、SQLiteの指定もできるようになった。
あとは使うだけ、といいたいところだが、SQLiteの特性、それは「SQLiteトランザクションをかけないと無茶苦茶遅くなる」という性質、それがあることを忘れてはならない。
ところが、SQLObjectのマニュアルをよんでも、いまいちトランザクションのかけ方の要領を得ない。
結局、トランザクションのかけ方というよりもコネクションの持ち方の理解が必要っぽいのである。
とりあえず、以下、英文ドキュメントを多少読んでみた結果と、ソースコードを少し眺めてみた結果から得られた情報をメモしておく。(もしかすれば間違えもあるかもしれない。)

SQLObjectでのコネクションの使い方
  • connectionForURI()を使って、URIを指定してコネクションを取得する。
  • 取得したコネクションをsqlhub.processConnectionプロパティに代入する。(個々のオブジェクトに対して_connectionプロパティを使って直接コネクションを細かく制御することも可能であるが、sqlhub.processConnectionに設定するほうが、いろいろな恩恵を受けられるようである。)
  • SQLObjectはコネクションが必要な場合には、以下の順序で取得される。
    • インスタンスに関連づけられた_connectionプロパティがあれば、それを用いる。
    • sqlhubにスレッドに関連づけられたコネクションがある場合には、それを用いる。
    • 見つからない場合はsqlhubにプロセスに関連づけられたコネクションがあれば、それを用いる。
    • それもなければエラー
  • 明示的にトランザクションを開始する場合は、元となるコネクションに対してtransaction()を呼び出す。このメソッドが返したコネクションがトランザクション状態をもつコネクションとなる。
トランザクションの使い方

SQLObjectにおいては、コネクションからトランザクションを取得して、そのトランザクション状態をもったコネクションを使うことで1つのトランザクションに参加することになる。
つまり、明示的にトランザクション状態にするにはsqlhub.threadConnectionプロパティにトランザクション状態になっているコネクションを代入して、そのあとでSQLObjectのもろもろの処理を行うようにすればよい。
ただし、注意すべきことは、すでにスレッドにはコネクションが関連づけられているかもしれないし、例外が発生した場合にはロールバック、正常完了した場合はコミットして、また終了するときには正常・異常であろうとも以前のトランザクション状態に戻さなければならない。

そこで、これらを一発でやってくれる便利なメソッドがsqlhub.doInTransaction()メソッドがある。このメソッドは、ファンクションオブジェクトを引数として渡すと次のような処理を行う。

  1. スレッドまたはプロセスに関連づけられているコネクションを取得する。
  2. 取得したコネクションに対してtransaction()を呼び出しトランザクションを開始したコネクションを取得する。
  3. 現在のスレッドに関連づけられているコネクションを保存し、新規に作成したコネクションをセットする。
  4. ファンクションを実行する。
  5. ファンクションが例外で返された場合にはロールバックを行う。そうでなければコミットする。
  6. 現在のスレッドに関連づけられているコネクションを呼び出し前の状態に復元する。

このような処理が1つのメソッドで実現できるうえに、定義するファンクションの中にトランザクション処理をかかなくてよくなる、というメリットもある。

SQLObjectでトランザクションを使う場合の注意事項

トランザクションを使う場合の制限事項として「メモリ上にキャッシュを作らないこと」とある点にも注意が必要である。

If you want to use transactions you should also turn cacheValues off*2, like:

    class Person(SQLObject): 
        class sqlmeta:
            cacheValue = False

O/R Mappingは、たしかにデータベースとオブジェクトのミスマッチを緩和するための道具であるが、それ以上に、オブジェクトのキャッシュの威力が魅力的だといえる。むしろ、メモリ空間に入りきれないような膨大なオブジェクトのうち、活性化している少数のインスタンスだけをメモリにおいて、残りはディスクなどの不活性した状態で保存させておくことができる利点はパフォーマンス的にみても大きいだろう。
しかし、トランザクションがからむと話は難しくなる。同時実行的に複数のスレッド/トランザクションが同一アイデンティティをもつインスタンスを取得・操作する場合、どのように制御すべきなのか。あるいは複数サーバから単一データベースを利用する場合は?
SQLObjectの戦略がどうなのかは、よく分からないが、たしかにO/R Mappingで複数スレッド/トランザクション、あるいは複数プロセスが同一のデータベースを更新する場合、それぞれが独立した素朴なキャッシュをもっていると容易に不整合が発生するであろうことは簡単に想像できる。
O/R Mappingでのキャッシュの威力は魅力的ではあるが、これらの問題を解決するには実は簡単ではない。Oracle9i以降のRAC(リアルアプリケーションクラスタ)では、それぞれのサーバ間を太い帯域を使えるファイバチャネルでつないでキャッシュの調整をしているし、そこまで大がかりでなくても、それなりに複雑なメカニズムが必要となる。
しかし、キャッシュを捨ててしまえば単純にO/R Mappingの利点の1つは失われるが話は簡単になる。トランザクション制御は単純にデータベース側にお任せになるからである。

おそらく、現在のSQLObjectでは、キャッシュ戦略を複雑にするだけのメリットがないと考えたのであろう。Pythonのような軽量言語であれば妥当なものかもしれない。そのため、キャッシュの戦略は「あり・なし」の素朴なものになっているようである。(推論であるが。)

ようやく実験コード

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# Python2.5以降のみ
# SQLObjectはsqlobject-0.8dev_r2072-py2.5以降が必要。

from sqlobject import *
import os, random

# SQLite3のデータベースファイル名
DBNAME = "sqlobjecttest.db"
# デバッグモード指定
OPTION = "?debug=True"

# 絶対パスの取得
dbPath = os.path.abspath(DBNAME)

# すでにDBが作成済みか判定
existDb = os.path.exists(dbPath)

# Win32のフルパスのための構文に変換し、URIを構築する。
# Firebird/SQLiteではWin32上でドライブレターを含む絶対パスでは
# 「:」を「|」に変換することで絶対パスを利用することができる。
uri = "sqlite:///" + dbPath.replace(":", "|") + OPTION

# SQLObjectに対して接続するデータベースをURI形式で指定する。
# scheme://[user[:password]@]host[:port]/database[?parameters]
# スキーマ名でSQLObjectがサポートしているドライバタイプを指定する。
# sqlite, mysql, postgres, firebird, interbase, mssql, sybase...
conn = connectionForURI(uri)
print "connection-uri: %s" % conn.uri()

# プロセスにコネクションを関連づける。
# SQLObjectはSQL実行時にコネクションが必要となると、
# sqlhubのgetConnection()を呼び出す。
# これはスレッドに関連づけられたコネクションがあれば、それを用いて
# なければプロセスに関連づけられたコネクションを用いる。
# 注意すべきところは、コネクションからトランザクションを作成する、という点である。
# コネクションに対してtransaction()を呼び出したあと、
# それをスレッドに関連づければ、以降のオペレーションは同一トランザクションとなる。
sqlhub.processConnection = conn

#########################################

# テーブルの定義
# 暗黙でプライマリキーとしてidカラムがデータベースには作成される。
# SQLite3の場合はInteger Primary Key扱いとなる。
class Person(SQLObject):

    # メタ情報
    class sqlmeta:
        # トランザクションの取り消しがありえる場合、キャッシュしてはならない。
        cacheValues = False
    
    # 文字列カラムはStringCol()のように宣言する
    # notNoneをTrueに指定するとNOT NULL制約となる。
    name = StringCol(notNone = True)

    # 数値カラムはIntCol()のように宣言する
    # uniqueをTrueに指定するとUNIQUE制約となる。
    memberCode = IntCol(notNone = True, unique = True)

    # 関連オブジェクト
    phones = MultipleJoin("Phone")

class Phone(SQLObject):

    # メタ情報
    class sqlmeta:
        # トランザクションの取り消しがありえる場合、キャッシュしてはならない。
        cacheValues = False

    # 外部キー制約
    person = ForeignKey("Person")

    phoneNumber = StringCol(notNone = True)

# 初回データベース作成時にテーブルも作成する。
if not existDb:
    Person.createTable()
    Phone.createTable()

#########################################

# データの登録ブロック
# このファンクションはトランザクション単位となる。
# ファンクション中で例外が発生すると、ロールバックされる。
def func():
    #データの作成
    mx = int(random.random() * 100) + 10
    for n in range(1, mx + 1):
        mc = int(random.random() * 10000)
        # 既存のレコードの取得
        persons = list(Person.selectBy(memberCode = mc))
        if len(persons) == 0:
            # 既存レコードなし
            person = Person(name = "name:%d" % n, memberCode = mc)
            persons.append(person)
        # 関連レコードの作成
        for person in persons:
            mx2 = int(random.random() * 10)
            for n2 in range(1, mx2 + 1):
                phone = Phone(person = person,
                              phoneNumber = "%04d-%04d" % (n2, n))

# ファンクションを1つのトランザクションとして実行する。
# このメソッドは内部で現在のコネクションを保存して、コネクションからtransaction()
# で新しいトランザクションを開始し、それをスレッドに結びつけたのち、ファンクションを
# 実行する。成功した場合はコミット、失敗した場合はロールバックする。
# 最後に以前のコネクションをスレッドに復元してから復帰する。
sqlhub.doInTransaction(func)

# 登録されているレコードから、範囲を指定してレコードリストを取得する
mc_st = int(random.random() * 1000)
mc_en = mc_st + int(random.random() * 1000) + 10
persons = Person.select(Person.q.memberCode >= mc_st and
                        Person.q.memberCode < mc_en)
for person in list(persons):
    print "%04d %20s" % (person.memberCode, person.name)
    print "  phones %d" % len(person.phones)
    for phone in person.phones:
        print "  phone: %s" % phone.phoneNumber

間違いや無駄な部分も多数あるかもしれないが、とりあえず、動くことは確認した。
トランザクションは機能しているようだ。(トランザクションを外すと無茶苦茶遅くなることも確認。)

コネクションのURIにパラメータとして「Debug=True」と指定しているため、実行時にSQLObjectのトレース情報がコンソール画面に表示される。ここで、どのようなSQLが発行されているのか知ることができる。

結論

使い方としては比較的敷居は低いようなは感じられたが、いまいち分からない部分もある。
ドキュメントをきちんと読むこともそうだが、それよりもソースコードを直接見ていったほうが確実な感じでもある。
そのあたりでアジャイルというか軽量言語らしいさが悪い面で出ているような気もする。
メカニズムとしては、なかなか良いものではないか、と感じさせるには十分な出来だと思う。

しかし、うーん…、微妙。
やっぱり、この、まだ「アジャイル」な感じのライブラリは、まだバージョン0.8ということもあり、今後の発展とともにバッドノウハウを沢山覚えてゆくハメになりそうで、なんとなく及び腰になってしまう。
いいライブラリだと思うし、今後使われてゆく局面は、どんどん増えてゆくとは思うのだけど、現時点での使い方としては、「無難なところ」「使えるところ」を適当に使ってみるというのが良いのかもしれないなぁ。
私見だけど。

*1:12/3追記: よくよく調べてみると、SQLObjectがURIを解析するときに「|」を「:」に置き換えるらしい。したがって保持しているファイル名はWindows絶対パス形式となるし、SQLiteには普通の絶対パスとして渡される。だが、しかし、コネクションのメソッドuri()を呼び出すとコネクションを作成したドライバ名とデータベースロケーションを持つURIを返してくれるのだが、こいつは絶対パスのまま返すのだ。つまり、URIとして再利用はできない。この動きはどうなのか。

*2:原文は、ちょっと古いみたい。実行すると警告されるので、ここでは新しいスタイルで書いてみた。