SQLAlchemy で Firebird その1

その0 準備
その1 テーブル定義関連（いまここ）
その2 接続関連
その3 クエリー関連（ORM 版）

テーブル定義関連。

まずは Object Relational Tutorial 。そのあとに読むべきは Mapper Configuration 、 Describing Databases with MetaData 、 Column and Data Types って多いよ。うれしいけれど文章多すぎるよ。こんなところにも Python らしさがにじみ出ているというかなんというか。

しかも英語なのでオレの貧弱な英語力で読み直すには速度に難あり。なのでメモのこしたりリンク張ったりしておく。

基本

本来 SQLAlchemy ではテーブル定義(SQLAlchemy.Table のインスタンス)と対応する Python クラス（自作）を別々につくり、両者をマッピングして使用することとなっている*1。

from sqlalchemy import MetaData
from sqlalchemy import Table, Column, Integer, Unicode
from sqlalchemy.orm import mapper

metadata = MetaData()

person_table = Table('person', metadata,
        Column('id', Integer, primary_key=True),
        Column('name', Unicode(256)),
        )

class Person(object):
    pass

mapper(Person, person_table)

しかし、そこまできっちり分ける必要はないというユーザーが多数ということで、 declarative_base が用意されている。これで Base クラスを作って継承し、すこし書き方を覚えれば OK 。これで前述のコードとほぼ同じになる。詳しくは declarative を。

from sqlalchemy import Column
from sqlalchemy import Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Person(Base):
    __tablename__ = 'person'

    id = Column('id', Integer, primary_key=True)
    name = Column('name', Unicode(256))

これで次のようなテーブル person の定義となる。

CREATE TABLE person (
        id INTEGER NOT NULL,
        name VARCHAR(20),
        PRIMARY KEY (id)
)

テーブル定義クラスは自作クラスの __table__ 属性に隠れている。たとえば、 __table__ を用いてテーブル定義を確認したり、 CREATE TABLE, DROP TABLE を行うことができる。

>>> for column in Person.__table__.columns:
...   print column
...
person.id
person.name
>>>
>>> from sqlalchemy import create_engine
>>> engine = create_engine('firebird+kinterbasdb://user:password@host/db_alias')
>>> engine.create(Person.__table__)
>>> engine.drop(Person.__table__)

同様に、テーブルらのメタデータは Base.metadata に隠れている。 metadata.create_all でテーブル一括作成などのようなメタデータが必要なときは Base.metadata.create_all で OK。

>>> Base.meta.create_all()

declarative_base をつかってコードをはしょっていても、やれることが減るわけではないのがすてき。

そして、 Person クラスが Python 側でこのテーブルのデータを扱うための雛形になる。

>>> p = Person()
>>> p.id = 1
>>> p.name = u'シュン'

メソッドの追加

クラスには Python 側で扱いやすくするためのメソッド（クラスメソッド、スタティックメソッド含む）を自由に追加できる。テーブル定義には影響ないのでガンガン追加するべし。

from sqlalchemy import Column
from sqlalchemy import Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Person(Base):
    __tablename__ = 'person'

    id = Column('id', Integer, primary_key=True)
    name = Column('name', String(20))

    @classmethod
    def create(cls, id_, name):
        obj = cls()
        obj.id = id_
        obj.name = name
        return obj

    def __repr__(self):
        return 'Person(id_=%r, name=%r)' % (self.id, self.name)

>>> p = Person.create(id_=1, name=u'シュン')
>>> print p
Person(id_=1, name=u'\u30b7\u30e5\u30f3')

それが __init__ だとしても。 SQLAlchemy がオブジェクトを生成するときに支障がありそうだが、 SQLAlchemy は __init__ を直接呼び出さないので問題ないとのこと。

class Person(Base):
    __tablename__ = 'person'

    id = Column('id', Integer, primary_key=True)
    name = Column('name', String(20))

    def __init__(self, id_, name):
        self.id = id_
        self.name = name

>>> p = Person(1, u'シュン')
>>> print p.id, p.name
1 シュン

カラムの型

Column and Data Types の Generic Types だけをつかっていれば、 Firebird 以外のデータベースを使いたくなったとき、 SQLAlchemy がうまく型を選んでくれるので楽。命名則は 1 文字目だけ大文字。

Firebird で実際にテーブルを作って調べてみたところ、対応はこんな感じ。

SQLAlchemy.type	Firebird 用 SQL
BigInteger	BIGINT
Boolean	SMALLINT, CHECK(column_name IN (0, 1))
Date	DATE
DateTime	TIMESTAMP
Enum	CHECK 制約つき VARCHAR *2
Float	FLOAT
Integer	INTEGER
Interval	TIMESTAMP
LargeBinary	BLOB SUB_TYPE 0
Numeric	NUMERIC
PickleType	BLOB SUB_TYPE 0 *3
SmallInteger	SMALLINT
String	VARCHAR *4
Text	BLOB SUB_TYPE 1
Time	TIME
Unicode	VARCHAR5 6
UnicodeText	BLOB SUB_TYPE 1*7

SQL Standard Types は SQL 標準の型と 1 対 1 対応しているもの。生成される SQL がどうなるかもすぐにわかる。命名則はすべて大文字。

Vendor-Specific Types は扱うデータベースが完全に決まっているとき用。でも Firebird に関する文章には Firebird 用の型の話が一切出てこない罠。 sqlalchemy.dialects.firebird モジュールには用意はされているようだがドキュメント化されていないのか、 Generic Types だけですむからドキュメント化する必要がなかったのか…。

制約各種、インデックス、デフォルト値

より詳しくは Defining Constraints and Indexes 。CHECK 制約や複数カラムにまたがる制約についても載っている。

カラム定義である Column のコンストラクタにはさまざまなオプションがある。たとえば primary_key=True で NOT NULL と PRIMARY KEY がつく。

各オプションの Firebird での解釈は次のとおり。

Column option	Firebird 用 SQL
autoincrement=True	無視される
default='x'	無視される
index=True	CREATE INDEX ix_tablename_columnname ON tablename (columnname)
nullable=False	NOT NULL
primary_key=True	NOT NULL, PRIMARY KEY (columnname)
server_default='x'	DEFAULT 'x'
unique=True	UNIQUE (columnname)

default オプションは SQLAlchemy 側でデフォルト値を用意するものであるためデータベース側には現れない。 DEFAULT 句に対応するのは server_default オプション。

Foreign Key も制約ではあるが、これはリレーションのほうに書く。

オートインクリメント代わりにシーケンスを使う

Firebird には AUTOINCREMENT がないので別の手段が必要*8。とはいえ、シーケンスを使えば 1 ずつ増える値を用意することができるのでやってやれないことはない。

SQLAlchemy では Culumn の定義に Sequence('sequence_name') を加えるだけで、テーブル作成時にシーケンスが一緒に生成され、インサートする際にはこのシーケンスの値をつかってくれる。

from sqlalchemy import Sequence
from sqlalchemy import Column
from sqlalchemy import Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Person(Base):
    __tablename__ = 'person'

    id = Column('id',
            Integer, Sequence('person_id'),
            autoincrement=True, primary_key=True)
    name = Column('name', String(20))

シーケンス名はユニークである必要があるため、ユーザーが名前を決めて管理しなくてはならないのが autoincrment よりもほんの少しだけ手間。

リレーション

http://docs.sqlalchemy.org/en/rel_0_6/orm/extensions/declarative.html#configuring-relationships や http://docs.sqlalchemy.org/en/rel_0_6/orm/relationships.html に詳しい情報あり。

ForeignKey 制約をかけるだけなら ForignKey を Column に設定すればよい。 Python オブジェクトから関連テーブルのデータにアクセスするための属性をつけるならば relationship を使うこととなる。逆参照用の属性をつけるならば relationship に backref オプションをつける。

ただし、これらの属性がついているテーブルに対し session.query(Table).all() のようなクエリを投げてオブジェクトを取得しようとしたとしても、参照先テーブルに対して JOIN するようなクエリが発行されるということはない（というか、毎回勝手に参照されると困る）。こういった属性を参照しようとした際に始めて参照先テーブルを調べるクエリが発行される。

一対多

ForignKey を適切に張った上で、参照先へ relationnship を向ければよい。逆参照のために backref もつけておくとよい。

住所と人の例。 address.people で住んでいる人々が取れる。 backref をつけているため person.address で住所を取ることもできる。

class Address(Base):
    __tablename__ = 'address'

    id = Column('id', Integer, primary_key=True)
    address = Column('address', String(128))
    people = relationship('Person', backref='address')

class Person(Base):
    __tablename__ = 'person'

    id = Column('id', Integer, primary_key=True)
    name = Column('name', String(128))
    address_id = Column('address_id', Integer, ForeignKey('address.id'))

多対一

一対多と比べて relationship を向ける方向が逆になっただけ。逆参照がいる場合は backref もつけておく。

人と住所の例。 person.address で住所がとれる。 backref をつけているため address.people で住んでいる人々をとることもできる。

class Person(Base):
    __tablename__ = 'person'

    id = Column('id', Integer, primary_key=True)
    name = Column('name', String(128))
    address_id = Column('address_id', Integer, ForeignKey('address.id'))
    address = relationship('Address', backref='people')

class Address(Base):
    __tablename__ = 'address'

    id = Column('id', Integer, primary_key=True)
    address = Column('address', String(128))

多対多

両者の関連を保持するテーブルを用意して relationship の secondary 引数に指定すればよい。関連保持用のテーブルは Table 定義だけがあれば十分なので次のようにする。

人と好きな食べ物の例。

class Person(Base):
    __tablename__ = 'person'

    id = Column('id', Integer, primary_key=True)
    name = Column('name', String(128))
    favorite_dishes = relationship(
            'FavoriteDish', secondary='person_favorite', backref='persons')

class FavoriteDish(Base):
    __tablename__ = 'favorite_dish'

    id = Column('id', Integer, primary_key=True)
    name = Column('name', String(128))
    #persons = relationship('Person', secondary='person_favorite', backref='parson')

person_favorite = Table(
    'person_favorite', Base.metadata,
    Column('person_id', Integer, ForeignKey('person.id'), primary_key=True),
    Column('favorite_dish_id', Integer, ForeignKey('favorite_dish.id'), primary_key=True),
    )

しかし関連保持用のテーブルにマッピングされたオブジェクトを作れるようにすることもできる。この場合は secondary は使用しない。 Association Object 参照。

ON UPDATE と ON DELETE

Firebird は ON UPDATE, ON DELETE 句にも対応しているデータベースのひとつ。これを用いるには ForeignKey のコンストラクタにて onupdate, ondelete 引数を使う。詳しくは ON UPDATE and ON DELETE 参照。

class Person(Base):
    __tablename__ = 'person'

    id = Column('id', Integer, primary_key=True)
    name = Column('name', String(128))
    address_id = Column(
            'address_id', Integer,
            ForeignKey(
                'address.id', onupdate="CASCADE", ondelete="SET NULL"))

参照先の取得方法の指定

SQLAlchemy ではテーブル定義時、 relationship の lazy 引数を指定することで参照しているテーブルのデータをいつ、どのように取得するかを制御することができる。

class Address(Base):
    __tablename__ = 'address'

    id = Column('id', Integer, primary_key=True)
    address = Column('address', String(128))
    persons = relationship('Person', backref='address', lazy='joined')

テーブル定義にて指定した動作を各クエリでさらに上書きすることも可能。

from sqlalchemy.orm import joinedload

for p in session.query(Person).options(joinedload('address')).all():
    print p.name, p.address.address

デフォルトではオブジェクトを生成したときには参照テーブルのデータはとってこず、必要になったときにあらためて SELECT クエリを投げるようになっている。これを JOIN や SUBQUERY をつかって最初からとってくるよう指定することができる。

詳しくは Relationship Loading Techniques 。用途に応じてどれを用いればよいかの指針もここに書かれている。

*1:O/R マッパーとして使わない場合はテーブル定義だけでもよい。 SQL Expression Language Tutorial 参照。

*2:Enum('a', 'b') だと VERCHER(1), CHECK(column_name IN ('a', 'b') ) になる

*3:Python オブジェクトを pickle でバイナリ化したものが入る

*4:String(256) だと VARCHAR(256)

*5:Unicode(256) だと VARCHAR(256)

*6:SQLAlchemy による Unicode への変換が行われる

*7:SQLAlchemy による Unicode への変換が行われる

*8:sqlite3 にも AUTOINCREMEN はないが __table_args__ = {'sqlite_autoincrement': True} というクラス変数を定義（Table のコンストラクタに sqlite_autoincrement=True オプションを指定）するという手があるのが、ちょっとうらやましい。

銀月の符号

Python 使い見習いの日記・雑記