SQLAlchemy 0.6.5 ドキュメント (和訳)

和訳: 株式会社アクセンス・テクノロジー 増田 泰
Version: 0.6.5 Last Updated: 06/15/2011 21:52:24
API Reference | Index

オブジェクトリレーショナルマッパ チュートリアル

このチュートリアルでは、SQLAlchemy でオブジェクトリレーショナルマッピングを 行い、データベースに Python オブジェクトを保存したり、データベースからオブ ジェクトを取り出したりする方法を説明します。チュートリアル中の例文は doctest の形式に準拠していて、 >>> で始まる行は、 Python のコマンドプロ ンプトから何か命令を入力していることを表し、その後に続く行が出力されるべき 結果を表しています。

バージョンチェック

まずは、 バージョン 0.6 の SQLAlchemy が使われているかどうかをチェック しましょう:

>>> import sqlalchemy
>>> sqlalchemy.__version__ 
0.6.0

データベースへの接続

このチュートリアルでは、メモリベースの SQLite データベースを使います。デー タベースへの接続には、 create_engine() を使います:

>>> from sqlalchemy import create_engine
>>> engine = create_engine('sqlite:///:memory:', echo=True)

echo フラグは SQLAlchemy のログ機能を有効にするためのショートカットです。 ログ機能には Python 標準の logging モジュールが使われています。 echo でログ機能を有効にすると、 SQLAlchemy は生成した SQL を全て表示し ます。チュートリアルの例題を実行する時に、出力内容を少なめにしておきたいの なら、 echoFalse にしてください。このチュートリアルでは、SQL の 出力をポップアップウィンドウに表示させ、邪魔にならないようにしています。SQL を見たければ、 “SQL” リンクをクリックしてください。

テーブルを定義して生成する

では、 SQLAlchemy にどんなテーブルを作るか指示していきましょう。まず、 users というテーブルを作るところから始めます。 users は、これから作 るアプリケーション (ここでは何かのウェブサイトということにしておきます) の エンドユーザを表すレコードを保存するためのテーブルです。テーブルを定義する には、 Table コンストラクタを使ってテーブルオブ ジェクトを生成して、メタデータ (MetaData) と呼 ばれるカタログに登録します。 Table コンストラク タの書法は、 SQL のCREATE TABLE 文に似ています:

>>> from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
>>> metadata = MetaData()
>>> users_table = Table('users', metadata,
...     Column('id', Integer, primary_key=True),
...     Column('name', String),
...     Column('fullname', String),
...     Column('password', String)
... )

Table の詳しい定義方法や、既存のデータベースか ら Table を取り出す方法 (いわゆる リフレクション (reflection) は、:ref:metadata_toplevel に書かれていま す。

次に、テーブルのメタデータに基づいて、 CREATE TABLE 文を実行します。 create_all()engine インスタンスを 渡して呼び出します。 engine はこのチュートリアルで使うデータベースを参 照しています。テーブルの生成が行われる前には、既存のテーブルがあるかどうか を必ずチェックするので、何度呼び出してもかまいません:

sql>>> metadata.create_all(engine) 

ノート

CREATE TABLE の構文に詳しいユーザなら、 VARCHAR カラムが長さの指 定なしで生成されていることに気づいたでしょう。 SQLite や Postgresql では、 長さ指定のない VARCHAR を有効なカラムデータ型として指定できますが、他の データベースではそうではありません。このチュートリアルを他のデータベー スを使って実行し、かつ CREATE TABLE を実行させたいなら、以下のようにし て String の引数に “length” を指定する必要が あります:

Column('name', String(50))

String の length フィールドは、 SQLAlchemy がテーブルを生成するとき以外には使われません。これは、 Integer の精度 (precision) フィールドや Numeric のスケール (scale) フィールドでも同 様です。

また、 Firebird や Oracle では、プライマリキーとして使われる ID を生成 するためにシーケンス値のカラムが必要ですが、 SQLAlchemy はこうしたカラ ムを自動的に生成しないので、 Sequence で明 に定義してやる必要があります:

from sqlalchemy import Sequence
Column('id', Integer, Sequence('user_id_seq'), primary_key=True)

というわけで、失敗しない Table 定義を考える と、例えば以下のようになります:

users_table = Table('users', metadata,
   Column('id', Integer, Sequence('user_id_seq'), primary_key=True),
   Column('name', String(50)),
   Column('fullname', String(50)),
   Column('password', String(12))
)

マッピング先のPythonクラスを定義する

Table オブジェクトでは、データベースの情報を定 義しています。その一方で、アプリケーション内で使うビジネス・オブジェクトの ふるまいについては何も記述していません。 SQLAlchemy ではデータベースに関す る定義と、ビジネスオブジェクトのふるまいを別のものとして扱っているのです。 users テーブルに対応するオブジェクトとして、 User クラスのきわめて 単純なバージョン作成してみましょう。クラスを作成するときには、Python組み込 みの object クラスをサブクラス化する (つまり、新スタイルクラスを使う) という点だけ、注意してください:

>>> class User(object):
...     def __init__(self, name, fullname, password):
...         self.name = name
...         self.fullname = fullname
...         self.password = password
...
...     def __repr__(self):
...        return "<User('%s','%s', '%s')>" % (self.name, self.fullname, self.password)

このクラスでは、便宜上、 __init__()__repr__() メソッドを定義し ています。二つのメソッドは、実際にはどちらも完全に省略可能です。 SQLAlchemy が __init__() を直接呼び出すことはありません。

マッピングを行う

users_tableUser クラスができたので、この二つを対応づけましょう。 いよいよ SQLAlchemy の ORM パッケージが登場します。 users_tableUser との マッピング には、 mapper 関数を使います:

>>> from sqlalchemy.orm import mapper
>>> mapper(User, users_table) 
<Mapper at 0x...; User>

mapper() 関数は User に結びついた Mapper オブジェクトを新たに生成して、後で使 うために保存しておきます。それでは、 User オブジェクトを生成して、挙動 を調べてみましょう:

>>> ed_user = User('ed', 'Ed Jones', 'edspassword')
>>> ed_user.name
'ed'
>>> ed_user.password
'edspassword'
>>> str(ed_user.id)
'None'

インスタンスには __init__() メソッドでは定義していなかった id 属性 が付加されています。この id があるのは、 users_table オブジェクトで 定義されていたからです。デフォルトの設定では、 mapperTable クラス内の全てのカラムに対応したクラスア トリビュートを生成します。各々のクラスアトリビュートは Python のデスクリプ タで、マップされたクラスのいわば 操作具(instrumentation) です。 この「操作具」はきわめて機能に富んでいて、データベース上の変更を追跡して、 必要なときに自動的に新しいデータを読み出す機能を備えています。

上の例では、まだ SQLAlchemy に Ed Jones をデータベースに保存するよう指 示していないので、 id の値は None です。今後、オブジェクトをデータ ベースに保存すると、アトリビュートは新たにデータベース側で生成された値に なります。

一つの宣言でテーブル生成とクラスマッピングを一度に行う

これまで解説してきたアプローチでは、 ORM の設定に Table クラスとユーザの定義したマップ対象クラス、 mapper() の呼び出しが必要でした。これは従来の SQLAlchemy の使い方に準じ たもので、ORM の構成要素を可能な限り分離した形で実現しているという利点があ ります。一方で、多くのアプリケーションが必ずしも分離型のアプローチを必要と しているわけではありません。そのため、 SQLAlchemy では declarative という「手っ取り早い」設定スタイルを提供 しています。大半のアプリケーションでは、このスタイルだけを使えばよいはずで す。これまで解説してきたマッピングの例を、 declarative を使って書き直すと、以下のように書けます:

>>> from sqlalchemy.ext.declarative import declarative_base

>>> Base = declarative_base()
>>> class User(Base):
...     __tablename__ = 'users'
...
...     id = Column(Integer, primary_key=True)
...     name = Column(String)
...     fullname = Column(String)
...     password = Column(String)
...
...     def __init__(self, name, fullname, password):
...         self.name = name
...         self.fullname = fullname
...         self.password = password
...
...     def __repr__(self):
...        return "<User('%s','%s', '%s')>" % (self.name, self.fullname, self.password)

上の例では、まず declarative_base() 関数 を使って Base というクラスを生成しています。このクラスから全ての ORM 対 応クラスを導出します。クラス定義の中で、 Columnname 引数を使わずに呼び出していることに注意してください。 Column の名前はアトリビュートから決まるので、指 定しなくてもよいのです。

declarative_base() 版の User クラスに 対応する Table オブジェクトは、 User クラス の __table__ アトリビュートを使ってアクセスできます:

>>> users_table = User.__table__

MetaData オブジェクトにもアクセスできます:

>>> metadata = Base.metadata

declarative の詳しいドキュメントは 「 API リファレンス 」にある 「 declarative 」を参照してください。

「宣言的に」ORM を設定するもう一つの方法として、 Elixir というサードパーティ製のライブラリがあ ります。 Elixir は高機能かつ詳細に作り込まれていて、高水準なマッピング設定 をいくつも組み込みで持っています。 declarative と同様、クラスとマッピン グを一旦定義すれば、従来の SQLAlchemy で設定したのと同じ方法で ORM を操作で きます。

セッションの作成

さて、マッピングを定義したので、データベースと対話できるようになりました。 Session は、ORM がデータベースを操作すると きのいわば「ハンドル」にあたります。アプリケーションの立ち上げ時で create_engine() と同じレベルに記述する場合には、以下のよ うにして Session クラスを作成します。この クラスは、後で Session オブジェクトのファ クトリクラスの役割を担います:

>>> from sqlalchemy.orm import sessionmaker
>>> Session = sessionmaker(bind=engine)

モジュールレベルでセッションをセットアップし、アプリケーション内でまだ Engine を作成していない場合には、以下のよ うにセッションをセットアップします:

>>> Session = sessionmaker()

セットアップ後に create_engine() でエンジンを作成して Session に結びつけるには、 configure() を使います:

>>> Session.configure(bind=engine)  # エンジンを初期化済み

上のように Session クラスを作成すると、 データベースに結びついた Session オブジェ クトが生成されるようになります。 後のセクションで解説しますが、 sessionmaker() を呼び 出す際には、他にもトランザクションに関する特性を指定できます。さて、データ ベースと対話するときには、常に Session を 生成します:

>>> session = Session()

上の Session オブジェクトは、以前に触れた SQLite の engine に結びついています。ただし、コネクションはまだ開いてい ません。コネクションは、セッションを始めて使用したときに engine の管理 するコネクションプールから取り出され、データーベースへの全ての変更を commit するか、セッションオブジェクトを閉じるまでセッションによって保持されます。

オブジェクトの新規追加

User オブジェクトをデータベースに保存するには、まず Sessionadd() します:

>>> ed_user = User('ed', 'Ed Jones', 'edspassword')
>>> session.add(ed_user)

この時点では、 User インスタンスは 処理待ち(pending) で、SQL はまだ 実行されていません。 Session は、 Ed Jones を保存する必要ができた時点で、 フラッシュ(flush) と呼ばれ る手続きで SQL を実行します。 Ed Jones に対するデータベースクエ リが必要になれば、それまで処理待ちだった全ての情報を一旦フラッシュしてから、 クエリが実行されます。

例えば、以下の例では、 User のインスタンスを読み出すような Query オブジェクトを新たに作成しています。こ のクエリでは、「 name アトリビュートが ed 」という条件でフィルタし ており、かつ得られた結果の最初のレコードだけを取り出そうとしています。

結果的に、クエリによって得られる User インスタンスは、先ほど追加した User と同じです:

sql>>> our_user = session.query(User).filter_by(name='ed').first() 
>>> our_user
<User('ed','Ed Jones', 'edspassword')>

実際には、 Session が、クエリの結果得られ た行レコードが、 Session 内のオブジェクト マップ上のあるオブジェクトと 同じ だと判断するので、先ほど追加したのと 同じインスタンスを返すのです:

>>> ed_user is our_user
True

ここには、 アイデンティティマップ(identity map) と呼ばれる、 ORM の重要 なコンセプトが働いています。アイデンティティマッピングは、ある Session の中で特定のレコード行を操作したと きに、同一のデータが操作されるよう保証しています。 あるプライマリキーを持ったオブジェクトは、ひとたび Session の中に組み込まれると、 Session は、常にプライマリキーの値に対して 一意で同一の Python オブジェクトを返します。あるプライマリキーを持ったオブ ジェクトがすでにセッション上にあるのに、データベースに保存されているオブジェ クトを同じプライマリキーでセッションに追加しようとするとエラーを引き起こし ます。

複数の User オブジェクトを一括してセッションに追加したければ、 add_all() を使います:

>>> session.add_all([
...     User('wendy', 'Wendy Williams', 'foobar'),
...     User('mary', 'Mary Contrary', 'xxg527'),
...     User('fred', 'Fred Flinstone', 'blah')])

ここで、仮に Ed が自分のパスワードを(もう安全でなくなったと思って)変更し たとしましょう:

>>> ed_user.password = 'f8s7ccs'

Session はこの変更を見逃しません。以下の通 り、 Ed Jones のデータが変更されたことを知っています:

>>> session.dirty
IdentitySet([<User('ed','Ed Jones', 'f8s7ccs')>])

ついでに、先ほど追加した3つの User オブジェクトが 処理待ち であるこ とも知っています:

>>> session.new  
IdentitySet([<User('wendy','Wendy Williams', 'foobar')>,
<User('mary','Mary Contrary', 'xxg527')>,
<User('fred','Fred Flinstone', 'blah')>])

さて、現在処理待ちの変更をデータベースに反映して、トランザクションをコミッ トしたいことを Session に伝えるには、 commit() を使います:

sql>>> session.commit()

commit() は、現在処理されずに残っているデータの変更を全てデータベースに 反映して、トランザクションをコミットします。セッションが捕まえているコネク ションのリソースは、この時点でコネクションプールに返却されます。この後、セッ ションに対して操作を行うと、 新たな トランザクションが作成され、必要に 応じて再度コネクションリソースの取得が行われます。

Ed の id アトリビュートは、以前は None でしたが、コミットしたので何 らかの値を持っているはずです:

sql>>> ed_user.id 
1

Session がデータベースに何らかのレコード行 を新たに INSERT すると、オブジェクトの識別子や、データベースが生成したデフォ ルト値をアトリビュートを介して扱えるようになります。これらの値は、即座にオ ブジェクトに反映される場合もありますし、保存後に最初にアクセスしたときに生 成される場合もあります。上の例では、 commit() を実行した後に開始した新 たなトランザクションの中でデータにアクセスしているので、行データ全体が取り 出されています。SQLAlchemy のデフォルトの動作では、以前のトランザクションか ら存在しているデータに、新たなトランザクションの中でアクセスすると、データ の最新の状態を扱えるようにするために、最初のアクセスの時点でリフレッシュを 行います。レコードのリロードをどの程度厳密に行うかは制御でき、セッションに 関する章で解説しています。

ロールバック

Session はトランザクションの中で動作するの で、データの変更に対するロールバックも可能です。二つほど、データを変更して から、切り戻してみましょう。まず ed_user のユーザ名を Edwardo にし てみます:

>>> ed_user.name = 'Edwardo'

そして、 fake_user というユーザを追加してみます:

>>> fake_user = User('fakeuser', 'Invalid', '12345')
>>> session.add(fake_user)

セッションに対してクエリを行うと、現在のトランザクションの中でフラッシュが 行われます:

sql>>> session.query(User).filter(User.name.in_(['Edwardo', 'fakeuser'])).all() 
[<User('Edwardo','Ed Jones', 'f8s7ccs')>, <User('fakeuser','Invalid', '12345')>]

ここでロールバックすると ed_user の名前が ed に戻り、 fake_user がセッションから蹴り出されます。

sql>>> session.rollback()

sql>>> ed_user.name 
u'ed'
>>> fake_user in session
False

SELECT してみれば、データベースがロールバックされたとわかります:

sql>>> session.query(User).filter(User.name.in_(['ed', 'fakeuser'])).all() 
[<User('ed','Ed Jones', 'f8s7ccs')>]

クエリを発行する

クエリ (Query) は、 Session オブジェクトの query() メソッドを使って生成します。 このメソッドには可変個の引数を指定できます。引数として渡せるのはマップクラ スやマップクラス上のデスクリプタの組み合わせです。以下の例では、 User インスタンスを読み出す Query を示し ています。クエリ結果をイテレータとして扱うと、 User オブジェクトのリス トを返します:

sql>>> for instance in session.query(User).order_by(User.id): 
...     print instance.name, instance.fullname
ed Ed Jones
wendy Wendy Williams
mary Mary Contrary
fred Fred Flinstone

Query には、マップクラス上のデスクリプタを引 数に渡せます。 query() の引数にク ラスやカラムに対応するエンティティを複数渡すと、結果はタプルで表現されます:

sql>>> for name, fullname in session.query(User.name, User.fullname): 
...     print name, fullname
ed Ed Jones
wendy Wendy Williams
mary Mary Contrary
fred Fred Flinstone

Query の返すタプルは 名前つき(named) タプ ルという特殊なタプルですが、通常の Python オブジェクトと同じように扱えます。 タプルの各要素は名前で呼び出せます。クエリに指定したのがアトリビュートなら アトリビュート名、クラスならクラス名が対応しています:

sql>>> for row in session.query(User, User.name).all(): 
...    print row.User, row.name
<User('ed','Ed Jones', 'f8s7ccs')> ed
<User('wendy','Wendy Williams', 'foobar')> wendy
<User('mary','Mary Contrary', 'xxg527')> mary
<User('fred','Fred Flinstone', 'blah')> fred

タプルの名前は、スカラ値のアトリビュートなら label() を、クラスなら aliased() を使って変えられます:

>>> from sqlalchemy.orm import aliased
>>> user_alias = aliased(User, name='user_alias')
sql>>> for row in session.query(user_alias, user_alias.name.label('name_label')).all(): 
...    print row.user_alias, row.name_label
<User('ed','Ed Jones', 'f8s7ccs')> ed
<User('wendy','Wendy Williams', 'foobar')> wendy
<User('mary','Mary Contrary', 'xxg527')> mary
<User('fred','Fred Flinstone', 'blah')> fred

Query に対して、 LIMIT や OFFSET のような基 本的な操作を Python のアレイやスライスとして実行できます。 ORDER BY 操作と も組み合わせられます:

sql>>> for u in session.query(User).order_by(User.id)[1:3]: 
...    print u
<User('wendy','Wendy Williams', 'foobar')>
<User('mary','Mary Contrary', 'xxg527')>

フィルタをかけるには、 filter_by() にキー ワード引数を渡します:

sql>>> for name, in session.query(User.name).filter_by(fullname='Ed Jones'): 
...    print name
ed

filter() を使う方法もあります。 filter() はより柔軟性の高い SQL 式を使う 方法です。 SQL 式を使えば、 Python の標準的な演算子とマップクラスのクラスア トリビュートを組み合わせた式を指定できます:

sql>>> for name, in session.query(User.name).filter(User.fullname=='Ed Jones'): 
...    print name
ed

Query オブジェクトはほぼ完璧な 生成機能(generative) を備えていて、ほとんどのメソッド呼び出しが、検索条件 の追加された新たな:class:~sqlalchemy.orm.query.Query オブジェクトを返しま す。例えば、ユーザ名が “ed” で、フルネームが “Ed Jones” のユーザを検索した いときに、 filter() を 2 回呼び出して、 AND 検索を実施できます:

sql>>> for user in session.query(User).filter(User.name=='ed').filter(User.fullname=='Ed Jones'): 
...    print user
<User('ed','Ed Jones', 'f8s7ccs')>

よく使うフィルタ演算子

filter() でよく使う演算子をいくつか解説し ましょう:

  • 等しい:

    query.filter(User.name == 'ed')
  • 等しくない:

    query.filter(User.name != 'ed')
  • LIKE:

    query.filter(User.name.like('%ed%'))
  • IN:

    query.filter(User.name.in_(['ed', 'wendy', 'jack']))
    
    # works with query objects too:
    
    query.filter(User.name.in_(session.query(User.name).filter(User.name.like('%ed%'))))
  • NOT IN:

    query.filter(~User.name.in_(['ed', 'wendy', 'jack']))
  • IS NULL:

    filter(User.name == None)
  • IS NOT NULL:

    filter(User.name != None)
  • AND:

    from sqlalchemy import and_
    filter(and_(User.name == 'ed', User.fullname == 'Ed Jones'))
    
    # or call filter()/filter_by() multiple times
    filter(User.name == 'ed').filter(User.fullname == 'Ed Jones')
  • OR:

    from sqlalchemy import or_
    filter(or_(User.name == 'ed', User.name == 'wendy'))
  • マッチ:

    query.filter(User.name.match('wendy'))
match のパラメタに指定できる内容は、データベースバックエンドごとに違います。

リストとスカラの戻り値

Query クラスの三つのメソッド、 all(), one(), first() は、 SQL を即座に実行して、イテ レータでない値を返します。一方、 all() はリストを返します:

>>> query = session.query(User).filter(User.name.like('%ed')).order_by(User.id)
sql>>> query.all() 
[<User('ed','Ed Jones', 'f8s7ccs')>, <User('fred','Fred Flinstone', 'blah')>]

first() は、SELECT に 1 レコードの制約 をかけ、得られた結果をスカラで返します:

sql>>> query.first() 
<User('ed','Ed Jones', 'f8s7ccs')>

one() は全ての行レコードをフェッチして、 結果が一意なオブジェクトに対応しないか、複数のレコードが混在している場合に はエラーを送出します:

sql>>> from sqlalchemy.orm.exc import MultipleResultsFound
>>> try: 
...     user = query.one()
... except MultipleResultsFound, e:
...     print e
Multiple rows were found for one()
sql>>> from sqlalchemy.orm.exc import NoResultFound
>>> try: 
...     user = query.filter(User.id == 99).one()
... except NoResultFound, e:
...     print e
No row was found for one()

SQL リテラルを使う

Query はリテラル文字列を柔軟に扱えます。 ほとんどのメソッドが、 SQLAlchemy の SQL 式の他に SQL の文字列にも対応して います。 filter()order_by() の例を示しましょう:

sql>>> for user in session.query(User).filter("id<224").order_by("id").all(): 
...     print user.name
ed
wendy
mary
fred

コロン (:) を使って、SQL 文字列にパラメタバインディングできます。パラメタに 値を結びつけるには params() メソッドを 使います:

sql>>> session.query(User).filter("id<:value and name=:name").\
...     params(value=224, name='fred').order_by(User.id).one() 

完全に文字列の SQL 文だけを使いたいのなら、 from_statement() を使います。 このとき、マッパが使うカラム名が全て含まれるように注意してください (下の例 ではアスタリスクを指定して解決しています):

sql>>> session.query(User).from_statement("SELECT * FROM users where name=:name").params(name='ed').all()
[<User('ed','Ed Jones', 'f8s7ccs')>]

必要なカラム名を文字列で指定さえすれば、完全に「生の」SQL 文を from_statement() で使えます:

sql>>> session.query("id", "name", "thenumber12").from_statement("SELECT id, name, 12 as thenumber12 FROM users where name=:name").params(name='ed').all()
[(1, u'ed', 12)]

カウント

Query には、レコード数をカウントするための count() メソッドがあります:

sql>>> session.query(User).filter(User.name.like('%ed')).count() 
2

count() メソッドは、 SQL 文が何個のレコー ド行を返そうとしているか調べるのに使われ、一つのタイプのエンティティの数、 ここでは User を単純に数えた結果を返すのが主な役割です。 カラムやエンティティの集合が複雑で、「何を数えるべきか」を細かく指定しなけ ればならないような場合には、 count() を 使う必要はないでしょう。下の例では、個別のカラムに対するクエリをカウントし て、期待通りの結果を得ています:

sql>>> session.query(User.id, User.name).filter(User.name.like('%ed')).count() 
2

しかし、生成された SQL を見ると、SQLAlchemy は個々のカラム式を記述したいの だと解釈して、かつそのカラム式をサブクエリにラップすることを決め、「サブク エリの行数」が返されるようにしています。こうした防御策的な振る舞いは、ここ では実際には必要なく、場合によっては不要ですらあります。例えば、以下のよう に、 User を名前ごとにグループ化してカウントしたい場合を考えましょう:

sql>>> session.query(User.name).group_by(User.name).count()  
4

ここで欲しいのは 4 が 1 行ではなく、複数行の結果のはずです。というわけ で、何かに限定してカウントを行いたい場合には、カラム式の中に func.count() を使ってください:

>>> from sqlalchemy import func
sql>>> session.query(func.count(User.name), User.name).group_by(User.name).all()  
()
[(1, u'ed'), (1, u'fred'), (1, u'mary'), (1, u'wendy')]

リレーションを定義する

今度は、テーブルをもう一つ作って組み合わせてみましょう。いま作成しているシ ステムの仕様では、ユーザは自分のユーザ名に電子メールアドレスを好きな数だけ 登録できることになっています。つまり、 users_table と電子メールアドレス を保存するテーブル addresses の間には一対多の関係があります。 declarative を使って、テーブルとマップクラス Address を作成します:

>>> from sqlalchemy import ForeignKey
>>> from sqlalchemy.orm import relation, backref
>>> class Address(Base):
...     __tablename__ = 'addresses'
...     id = Column(Integer, primary_key=True)
...     email_address = Column(String, nullable=False)
...     user_id = Column(Integer, ForeignKey('users.id'))
...
...     user = relation(User, backref=backref('addresses', order_by=id))
...
...     def __init__(self, email_address):
...         self.email_address = email_address
...
...     def __repr__(self):
...         return "<Address('%s')>" % self.email_address

このクラスには、 users テーブルを参照する 外部キー(foreign key) の 制約が登場しています。外部キー制約を指定すると、 SQLAlchemy は単にデータベー スのレベルだけで、二つのテーブルの間にリレーションを定義します。 UserAddress クラスとの間のリレーションは、 relation() 関数を使って別個に定義します。 上の例では、 user アトリビュートを Address クラスに持たせ、逆に User クラス側には addresses というコレクションのアトリビュート が付加されます。これはいわゆる 双方向(bidrectional) リレーションです。 外部キーを定義したことで、 Address から User へは多対一の、 User から Address へは一対多の関係が生まれます。 SQLAlchemy は、外 部キーの定義に応じて、多対一または一対多の関係を自動的に認識します。

relation() はきわめて柔軟性のある関数で、 User クラス側からでもリレーションを定義できます:

class User(Base):
    # ....
    addresses = relation(Address, order_by=Address.id, backref="user")

backref は指定しなくてもよいし、 relation() を片側のクラスだけで定義して、もう一方で定義しないようにもできます。 一つのクラスに順方向と逆方向の二つの relation() を 定義することも可能で、一対多や多対一のリレーションのときには問題なく使えま す。ただし、多対多のリレーションではうまく動作しません。

declarativerelation() を使う場合に限り、リ レーション先のクラスは文字列で指定できます。これは対象のクラスがまだ定義さ れていなくても使えるようにするためで、 declarative を使うとき だけ 利用できます:

class User(Base):
    ....
    addresses = relation("Address", order_by="Address.id", backref="user")

declarative を使っていない場合には、まず mapper() でマッピングを定義し、 Table オブジェクトを作成済みなので、文字列を使 う必要はないはずです。

さて、 addresses テーブルをデータベースに作る必要があるので、 metadata を使って CREATE 文を実行します。このとき、すでに存在するテーブ ルの作成は、自動的に省略されます:

sql>>> metadata.create_all(engine) 

リレーションを操作する

リレーションを定義したので、 User を作成したときに、 addresses とい う空のコレクションが追加されているはずです。アトリビュートのコレクション型 は、 set や辞書型のような色々な型にできます(詳しくは コレクションを別のデータ型に切り替える を参照してください)が、デフォルト の型は Python のリストです。

>>> jack = User('jack', 'Jack Bean', 'gjffdd')
>>> jack.addresses
[]

addresses を使えば、 User オブジェクトに Address オブジェクトを 追加できます。リストを代入してみましょう:

>>> jack.addresses = [Address(email_address='jack@google.com'), Address(email_address='j25@yahoo.com')]

双方向のリレーションを定義している場合、リレーションの片側で要素の追加を行 うと、自動的に反対側のリレーションも見えるようになります。これは backref キーワード引数を指定したときの基本的な挙動で、 SQL を使わず、 完全にメモリ上だけでリレーションの維持を行っています:

>>> jack.addresses[1]
<Address('j25@yahoo.com')>

>>> jack.addresses[1].user
<User('jack','Jack Bean', 'gjffdd')>

Jack Bean をデータベースに追加してコミットしてみます。 カスケーディング(cascading) によって、 jackjackaddresses コレクション内の二つの Address が同時にセッションに追加さ れます:

>>> session.add(jack)
sql>>> session.commit()

再びクエリで Jack を取り出してみます。このとき、 Jack の addresses を調 べるための SQL はまだ実行されません:

sql>>> jack = session.query(User).filter_by(name='jack').one() 
>>> jack
<User('jack','Jack Bean', 'gjffdd')>

addresses コレクションを参照すると、 SQL が実行されたことがわかります:

sql>>> jack.addresses 
[<Address('jack@google.com')>, <Address('j25@yahoo.com')>]

addresses コレクションにアクセスした時点で、突然 SQL が実行されました。 これが 遅延読み出しリレーション(lazy loading relation) です。 addresses のコレクションは、一度読み出されたら、通常のリストと同じよう にふるまいます。

クエリの数を(たいていは劇的に)減らしたい場合もあることでしょう。そんなと きには、クエリ操作のときに 即時読み出し(eager load) を適用できます。 同じクエリで、 クエリオプション を指定して、 addresses が「即座に」 読み出されるようにします。そうすると、 SQLAlchemy は usersaddresses テーブルの間に外部結合(outer join)を作成して、二つのテーブル のデータを一度に読み出し、各 User オブジェクトの addresses コレクショ ンを(まだなければ)作成します:

>>> from sqlalchemy.orm import eagerload

sql>>> jack = session.query(User).options(eagerload('addresses')).filter_by(name='jack').one() 
>>> jack
<User('jack','Jack Bean', 'gjffdd')>

>>> jack.addresses
[<Address('jack@google.com')>, <Address('j25@yahoo.com')>]

SQLAlchemy には、一つの SQL クエリの中で、テーブルの結合を特定のアトリビュー トや特定のレベルの深さにわたるよう制御する機能があります。この機能の詳細は リレーションのマッピング で解説しています。

JOIN を使ってクエリを実行する

addresses のコレクションを一括ロードしたい時に即時読み出しを行うとテー ブルの結合が実行されますが、明示的に結合を行う方法もいくつかあります。例え ば、単純な内部結合を UserAddress の間で行いたい場合には、 関連するカラムを使って filter() を実行 します。下の例では、 UserAddress のエンティティを一度に取り出し ています:

sql>>> for u, a in session.query(User, Address).filter(User.id==Address.user_id).\
...         filter(Address.email_address=='jack@google.com').all():   
...     print u, a
<User('jack','Jack Bean', 'gjffdd')> <Address('jack@google.com')>

SQLAlchemy に JOIN 文を実際に作成させることもできます。一つは ORM の join() を使う方法で、 “select from” に相当する部分に join() を指定した Query を実行します:

>>> from sqlalchemy.orm import join
sql>>> session.query(User).select_from(join(User, Address)).\
...         filter(Address.email_address=='jack@google.com').all() 
[<User('jack','Jack Bean', 'gjffdd')>]

UserAddress の間には外部キーが一つしか存在しないので、 join() はテーブルの結合方法を自動的に判断します。 外部キーがなかったり、複数あったりする場合には、 join() には ON 節の内容に相当する第三の引数が必要で す。この引数には、下の三種類の指定方法があります:

join(User, Address, User.id==Address.user_id)  # 明示的な条件指定
join(User, Address, User.addresses)            # 左から右へのリレーション指定
join(User, Address, 'addresses')               # 文字列を使う方法

Queryjoin() メソッドを使えば、 join() と同じ機能を実現できます。このメソッドの最も 簡単な使い方は、下のように「ON」節の部分だけを指定するというものです:

sql>>> session.query(User).join(User.addresses).\
...     filter(Address.email_address=='jack@google.com').all() 
[<User('jack','Jack Bean', 'gjffdd')>]

テーブル結合のターゲットを明示的に指定するには、通常の JOIN の引数リストの ようなタプルを作って渡します。この方法は、カラムのエイリアスなどを扱う場合 に重要なテクニックです:

session.query(User).join((Address, User.addresses))

引数をリストで渡すと、複数の JOIN を作成できます:

session.query(Foo).join(Foo.bars, Bar.bats, (Bat, 'widgets'))

上の式は、 foo JOIN bars ON <onclause> JOIN bats ON <onclause> JOIN widgets ON <onclause> のような SQL を生成します。

エイリアスの使用

複数テーブルにまたがるクエリを作っていて、一つのテーブル名を何度も参照する 必要が生じる場合、SQL の制約として、テーブル名に別名(エイリアス)を付けな ければならないことがあります。というのも、SQL 中のある場所に登場するテーブ ル名が表すレコード集合は、別の場所に登場するテーブルとは違うレコード集合を 指していることがよくあるからです。 Query はエイリアスをサポートしていて、 aliased を使って明示的に指定します。 下の例では、ある特定の二つのメールアドレスを持っているユーザを検索するため に、 Address エンティティを二回 JOIN しています:

>>> from sqlalchemy.orm import aliased
>>> adalias1 = aliased(Address)
>>> adalias2 = aliased(Address)
sql>>> for username, email1, email2 in \
...     session.query(User.name, adalias1.email_address, adalias2.email_address).\
...     join((adalias1, User.addresses), (adalias2, User.addresses)).\
...     filter(adalias1.email_address=='jack@google.com').\
...     filter(adalias2.email_address=='j25@yahoo.com'):
...     print username, email1, email2      
jack jack@google.com j25@yahoo.com

サブクエリの使用

Query の生成する SQL 文は、サブクエリにも使 えます。例として、 User オブジェクトを、各ユーザに結びついている Address レコード数に応じて読み出したい場合を考えてみましょう。このよう な状況でのベストの SQL 生成方法は、まずユーザIDで Address レコードをグ ループ化しておき、 Address の親にあたる User と JOIN するといったと ころでしょう。 Address が一つも結びついていない User も検索されるよ うに、 LEFT OUTER JOIN を使います:

SELECT users.*, adr_count.address_count FROM users LEFT OUTER JOIN
    (SELECT user_id, count(*) AS address_count FROM addresses GROUP BY user_id) AS adr_count
    ON users.id=adr_count.user_id

Query を使って、上のような SQL を作りましょ う。 Query オブジェクトの statement アク セサを使えば、クエリの実行に使われる SQL 文を返してくれます。 statement アクセサは実際には select() コンストラクトのインスタンス で、 SQL表現言語チュートリアル で詳しく解説しています:

>>> from sqlalchemy.sql import func
>>> stmt = session.query(Address.user_id, func.count('*').label('address_count')).group_by(Address.user_id).subquery()

func キーワードは、 SQL 関数の生成に使われています。 Querysubquery() メソッドは、サブセレ クションをエイリアスに埋め込んだ SELECT 文を表す SQL 式を生成します。 (実際には、 query.statement.alias() のショートカットに過ぎません)

このようにして作成した SQL 式は、このチュートリアルで作成した users のような Table であるかのようにふるまいます。 カラムデータには、 c というアトリビュートを介してアクセスできます:

sql>>> for u, count in session.query(User, stmt.c.address_count).\
...     outerjoin((stmt, User.id==stmt.c.user_id)).order_by(User.id): 
...     print u, count
<User('ed','Ed Jones', 'f8s7ccs')> None
<User('wendy','Wendy Williams', 'foobar')> None
<User('mary','Mary Contrary', 'xxg527')> None
<User('fred','Fred Flinstone', 'blah')> None
<User('jack','Jack Bean', 'gjffdd')> 2

サブクエリからの SELECT

上の例では、単にサブクエリから内部のカラムデータに応じて SELECT を行っただ けでした。では、サブクエリを何らかのエンティティクラスに対応づけたい場合に はどうすればよいのでしょうか? それには、 aliased() を使って、マップクラ スの「別名」をサブクエリに結びつけます:

sql>>> stmt = session.query(Address).filter(Address.email_address != 'j25@yahoo.com').subquery()
>>> adalias = aliased(Address, stmt)
>>> for user, address in session.query(User, adalias).join((adalias, User.addresses)): 
...     print user, address
<User('jack','Jack Bean', 'gjffdd')> <Address('jack@google.com')>

EXISTS の使用

SQL で EXISTS キーワードといえば、ある SQL 式が何らかのレコード行を含む場合 に True を返すブール型の演算子です。 EXISTS は様々な状況で JOIN の代わりに 使われるほか、あるテーブルのレコード行のうち、別の関連するテーブルに対応す るレコードがないようなものを探したい場合にも便利です。

EXISTS を明示的に使うには、以下のようにします:

>>> from sqlalchemy.sql import exists
>>> stmt = exists().where(Address.user_id==User.id)
sql>>> for name, in session.query(User.name).filter(stmt):   
...     print name
jack

Query には、 EXISTS 節を自動的に組み込むよう な演算子がいくつかあります。上の例だと、 User.addresses へのリレーショ ンは any() を使って書けます:

sql>>> for name, in session.query(User.name).filter(User.addresses.any()):   
...     print name
jack

any() には条件式を指定でき、一致させるレコード行を制限できます:

sql>>> for name, in session.query(User.name).\
...     filter(User.addresses.any(Address.email_address.like('%google%'))):   
...     print name
jack

has()any() と同じ演算子で、多対一のリレーションに使います ( ~ 演算子もここで使われていますが、意味は「NOT」に相当します)。

sql>>> session.query(Address).filter(~Address.user.has(User.name=='jack')).all() 
[]

リレーションに関する主な演算子

リレーションの演算に使われる演算子は以下の通りです:

  • equals (多対一で使えます):

    query.filter(Address.user == someuser)
  • not equals (多対一で使えます):

    query.filter(Address.user != someuser)
  • IS NULL (多対一で使えます):

    query.filter(Address.user == None)
  • contains (一対多、多対多のコレクションで使えます):

    query.filter(User.addresses.contains(someaddress))
  • any (一対多、多対多のコレクションで使えます):

    query.filter(User.addresses.any(Address.email_address == 'bar'))
    
    # also takes keyword arguments:
    query.filter(User.addresses.any(email_address='bar'))
  • has (多対一で使えます):

    query.filter(Address.user.has(name='ed'))
  • with_parent (全てのリレーションで使えます):

    session.query(Address).with_parent(someuser, 'addresses')

レコードの削除

jack を削除して、何が起きるか見てみましょう。セッションの中で削除のマー クを付けて、 count クエリを実行すると、レコードが何も残っていないことが わかります:

>>> session.delete(jack)
sql>>> session.query(User).filter_by(name='jack').count() 
0

ここまでは問題なし。それでは、 Jack の Address オブジェクトはどうなって いるでしょうか?

sql>>> session.query(Address).filter(
...     Address.email_address.in_(['jack@google.com', 'j25@yahoo.com'])
...  ).count() 
2

あらら、まだ残っていますね! flush 時の SQL を解析すれば、各アドレスの user_id カラムの値が NULL になっているだけで、行レコードは削除されてい ないことがわかります。 SQLAlchemy はレコードの削除がカスケードするものとい う前提をもっていないので、カスケードさせたければ教えてやらねばなりません。

delete/delete-orphan カスケードの設定

前節で説明した動作を変更するために、 User.addresses リレーションに cascade オプションを設定しましょう。 SQLAlchemy はたいていの場合、マッ ピングクラスにいつでもアトリビュートを追加できるのですが、この場合だけは 一旦既存のリレーションを削除せねばならないので、まずは一度全てのマッピング を解消して、最初から構築しなおすことにします。下では再構築の操作を行ってい ますが、これは単に説明のためのやり方であって、一般的な操作ではないので注意 してください。

ORM の状態を全て削除するには、以下のようにします:

>>> session.close()  # roll back and close the transaction
>>> from sqlalchemy.orm import clear_mappers
>>> clear_mappers() # clear mappers

次に、 mapper() を使って UserAddress の ORM マッピングを再 構築します。どちらのクラスも、今はマッピングされていないだけで、すでに存在 しています。今度は User.addresses のリレーションに delete, delete-orphan というカスケードの指定を入れて、 DELETE 操作のと きに Address オブジェクトにカスケードが及ぶようにし、かつ親オブジェクト から除去された Address オブジェクト自体を削除対象にします:

>>> mapper(User, users_table, properties={    
...     'addresses':relation(Address, backref='user', cascade="all, delete, delete-orphan")
... })
<Mapper at 0x...; User>

>>> addresses_table = Address.__table__
>>> mapper(Address, addresses_table) 
<Mapper at 0x...; Address>

これで、 Jack を(下の例では get() で主キーを使って)ロードして、 addresses コレクションからアドレスを除去すると、除去された Address オブジェクトのレコードも削除されます:

# Jack を主キーを使ってロードする
sql>>> jack = session.query(User).get(5)    

# Address を削除する(addresses のアクセスで遅延読み込みが実行されます)
sql>>> del jack.addresses[1] 

# 残っているアドレスは一つだけ
sql>>> session.query(Address).filter(
...     Address.email_address.in_(['jack@google.com', 'j25@yahoo.com'])
... ).count() 
1

Jack を削除すると、 Jack と Jack に結びついている残りの Address が削除 されます:

>>> session.delete(jack)

sql>>> session.query(User).filter_by(name='jack').count() 
0

sql>>> session.query(Address).filter(
...    Address.email_address.in_(['jack@google.com', 'j25@yahoo.com'])
... ).count() 
0

多対多のリレーションを構築する

ややおまけ気味になってしまいますが、多対多のリレーションについても解説しま しょう。この節では他の機能も少し試してみます。あるブログアプリを開発すると します。このブログでは、ユーザが記事を表す BlogPost を作成でき、各記事 にキーワードを表す Keyword を結びつけられます。

declarative を使って準備すると、以下のようになるでしょう:

>>> from sqlalchemy import Text

>>> # 連結テーブル (association table)
>>> post_keywords = Table('post_keywords', metadata,
...     Column('post_id', Integer, ForeignKey('posts.id')),
...     Column('keyword_id', Integer, ForeignKey('keywords.id'))
... )

>>> class BlogPost(Base):
...     __tablename__ = 'posts'
...
...     id = Column(Integer, primary_key=True)
...     user_id = Column(Integer, ForeignKey('users.id'))
...     headline = Column(String(255), nullable=False)
...     body = Column(Text)
...
...     # BlogPost<->Keyword 間の多対多リレーション
...     keywords = relation('Keyword', secondary=post_keywords, backref='posts')
...
...     def __init__(self, headline, body, author):
...         self.author = author
...         self.headline = headline
...         self.body = body
...
...     def __repr__(self):
...         return "BlogPost(%r, %r, %r)" % (self.headline, self.body, self.author)

>>> class Keyword(Base):
...     __tablename__ = 'keywords'
...
...     id = Column(Integer, primary_key=True)
...     keyword = Column(String(50), nullable=False, unique=True)
...
...     def __init__(self, keyword):
...         self.keyword = keyword

この例では、 BlogPost.keywords が多対多リレーションです。 多対多リレーションの定義を行っているのは、 secondary キーワード引数に 連結テーブルを指定しているところです。連結テーブルには、リレーションに関わ る双方のオブジェクトへの参照を入れるためのカラムだけが入っています。連結テー ブルに主キーや他のテーブルへの外部キーのようなカラムを追加したい場合には、 「連結オブジェクト(association object)」と呼ばれる別のプログラミングパター ンを使う必要があります。これについては 結合オブジェクト (Association Object) で解説し ています。

多対多のリレーションでも、 backref キーワードを使って双方向性を持たせら れます。これから作ろうとしているブログのデータモデルは、 backref を使う のが一般的なケースです。というのも、 Keyword エンティティにもう一つ別の posts のようなリレーションを定義してしまうと、二つのリレーションを使っ て互いに別々に post_keywords テーブルにレコードを追加したり削除したりで きるので、コンフリクトを起こしてしまうからです。

今度は BlogPost クラスに author フィールドを追加したいとします。双 方向のリレーションとして定義できますが、ただ一つ、一人のユーザが沢山のブロ グポストを持ててしまうという問題があります。 User.posts にアクセスした とき、ポストのコレクション全体にアクセスしないよう、クエリ結果をフィルタで きたほうがよいでしょう。そのため、 relation()lazy='dynamic' という設定を入れて、アトリビュートの 読み出し戦略 を変更します。「逆方向の」 relation() に対して読み出 し戦略を変えたければ、 backref() を使います:

>>> from sqlalchemy.orm import backref
>>> # "dynamic" loading relation to User
>>> BlogPost.author = relation(User, backref=backref('posts', lazy='dynamic'))

新しいテーブルを作成してみましょう:

sql>>> metadata.create_all(engine) 

これまでとあまり操作方法は変わりませんね。それでは、 Wendy にいくつかブログ 記事をポストさせましょう:

sql>>> wendy = session.query(User).filter_by(name='wendy').one() 
>>> post = BlogPost("Wendy's Blog Post", "This is a test", wendy)
>>> session.add(post)

キーワードはデータベース内で一意にしていますが、この時点ではまだキーワード を一つも登録していないので、何も考えず単に作成しておきます:

>>> post.keywords.append(Keyword('wendy'))
>>> post.keywords.append(Keyword('firstpost'))

これで、「firstpost」というキーワードを持つブログポストを検索できるようにな りました。 any 演算子を使えば、「”firstpost”というキーワードの付いたた ブログ記事」を調べられます:

sql>>> session.query(BlogPost).filter(BlogPost.keywords.any(keyword='firstpost')).all() 
[BlogPost("Wendy's Blog Post", 'This is a test', <User('wendy','Wendy Williams', 'foobar')>)]

Wendy のポストした記事だけを検索したければ、 User の親オブジェクトの視 点から、クエリを絞り込めます:

sql>>> session.query(BlogPost).filter(BlogPost.author==wendy).\
... filter(BlogPost.keywords.any(keyword='firstpost')).all() 
[BlogPost("Wendy's Blog Post", 'This is a test', <User('wendy','Wendy Williams', 'foobar')>)]

Wendy 自身の posts リレーションを使っても絞り込めます。 posts は 「動的な」リレーションなので、素直にクエリを書けます:

sql>>> wendy.posts.filter(BlogPost.keywords.any(keyword='firstpost')).all() 
[BlogPost("Wendy's Blog Post", 'This is a test', <User('wendy','Wendy Williams', 'foobar')>)]

参考文献

Query API のリファレンスは「 クエリ関連 」にあります。

ORM のセットアップに関する詳しい情報は「 mapper の設定 」を参 照してください。

セッションに関する詳しい情報は「 セッションを使う 」を参照してくださ い。

Previous: 概要とインストール方法 Next: SQL表現言語チュートリアル