SQLAlchemyのリレーションにおけるメソッドやパラメータについてのメモ

SQLAlchemyはPythonのORMの中でも比較的よく検索にヒットするように思いますが，和訳済みドキュメント*1のバージョンが古く，リレーションの張り方についてどうも自分の中でごちゃごちゃしているなと思い少し調べてみました．

注意：
これはただの生物学徒が自分の興味本位で適当に本家ドキュメント*2とかを流し読みして書いた内容です．間違っていたらコメント等でお知らせください．

環境
relation()とrelationship()
backrefとback_populates
lazy
cascade
- save-update
- delete
- delete-orphan
- merge
- refresh-expire
- expunge
基本的なリレーションパターン
まとめ

環境

Python==3.5.2
SQLAlchemy==1.1

基本的には公式ドキュメントのサンプルコードをお借りします．
またSessionクラスを作成しwith文で扱えるようにしておきます．

from sqlalchemy import Integer, ForeignKey, String, Column
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

Base = declarative_base()

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String)

    addresses = relationship("Address", backref="user")

class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    email = Column(String)
    user_id = Column(Integer, ForeignKey('user.id'))

engine = create_engine('sqlite:///:memory:', pool_recycle=3600, echo=True)
Base.metadata.create_all(engine)
Sess = sessionmaker(bind=engine, expire_on_commit=False, autocommit=False)


class Session(object):

    def __init__(self):
        self.session = Sess()

    def __enter__(self):
        return self.session

    def __exit__(self, *exception):
        if exception[0] is not None:
            self.session.rollback()
        self.session.close()

`relation()`と`relationship()`

公式のドキュメントを読む限り，違いは無いようです．relationship()についての説明がされた後，relation()については

A synonym for relationship().

とだけ書かれています．個人的にはどちらかに統一して欲しいところです（むしろなぜ同じ機能なのに異なるメソッド名で参照しているのだろう）．

なお和訳済みドキュメントのAPIリファレンスにはこのrelationship()がどこにも載っていないようです．文中では出てくるのですが，このメソッド自体のリファレンスが存在しないようです（探し方が甘いだけでしたらすいません）．なおrelation()については上記の引用と同じ事が書かれています*3．ふしぎ．

`backref`と`back_populates`

これについてはQiitaに記事がありましたので最初にご紹介します．

qiita.com

また本家ドキュメントでも詳しく扱っています．完全な情報についてはこちら*4をご覧ください．

これらは両方ともsqlalchemy.orm.relationship()（またはsqlalchemy.orm.relation()）のパラメータです．和訳済みドキュメントが書かれたバージョン0.6.5の時点ではback_populatesについての言及がされていません．

これらの使い分けとしては，一つのテーブルを定義したとき，複数のリレーションを張るならback_populates，一つしか存在しないならbackrefを使うと良いようです．なおここでは簡単のためsqlalchemy.orm.backref()については一旦置いておきます．

backrefの場合

最初に記述したコードを使います．

# ...

class User(Base):
    # ...
    addresses = relationship("Address", backref="user")

class Address(Base):
    # ...
    user_id = Column(Integer, ForeignKey('user.id'))

ここではUserクラスのみにrelationship()を定義し，backref="user"というパラメータを与えています． Userクラスのインスタンスの.addressにAddressクラスのオブジェクトを追加したとき暗黙的に双方向のリレーションが張られるため，Addressクラスにはrelationship()が定義されていませんが.userで参照することができるようになります．
ただしこれはMany to One，もしくはOne to Many，もしくは One to Oneの場合しか使用できません．この場合はUser（One）とAddress（Many）の関係になっており，Userは複数のアドレスを持てますがAddressは一つのUserしか保持できず，Address.user.property.uselistはFalseになっています．

from models import User, Address

user_data = {
    "name": "test_user"
}

address_data = {
    "owner": "test_user",
    "address": "test@example.com"
}


def test_backref():
    user = User()
    user.name = user_data["name"]
    address = Address()
    address.email = address_data["address"]

    print(user)
    print(address)
    print(user.addresses)
    print(address.user)

    user.addresses = [address]

    print(user)
    print(address)
    print(user.addresses)
    print(address.user)

if __name__ == "__main__":
    test_backref()

実行結果は以下（models.pyに上記モデルを記述しています．）

<models.User object at 0x1054bcb70>
<models.Address object at 0x1054bcc18>
[]
None
<models.User object at 0x1054bcb70>
<models.Address object at 0x1054bcc18>
[<models.Address object at 0x1054bcc18>]
<models.User object at 0x1054bcb70>

Process finished with exit code 0

back_populatesの場合

# ...

class User(Base):
     # ...
     addresses = relationship("Address", back_populates="user")

class Address(Base):
     # ...    
     user = relationship("User", back_populates="addresses")

ここでは両方にrelationship()とパラメータとしてback_populatesを定義することで明示的に双方向のリレーションを張っています．

例えばUserクラスのrelationship()を定義しなかったとすると，

sqlalchemy.exc.InvalidRequestError: Mapper 'Mapper|User|user' has no property 'addresses'

という例外を発生します．

また，Userクラスのback_populatesを指定しなかった場合，backrefのときに使用したテストと同じコードを走らせると

<models.User object at 0x1066e3fd0>
<models.Address object at 0x1066f40b8>
[]
None
<models.User object at 0x1066e3fd0>
<models.Address object at 0x1066f40b8>
[<models.Address object at 0x1066f40b8>]
None

となり，Addressのインスタンスからは.userがNoneとなりますがUser側からは.addressesが見えるという状態になります．

上記例におけるForeignKey

Addressの持つForeignKeyが何のために必要なのか最初分からないまま使っていたのですが，これはaddressテーブルのオブジェクトを取得したときに，同時にリレーションを張ったuserテーブル上のオブジェクトを参照するためにあるようで，記述しなかった場合以下の様な例外が発生し正しく実行できません．

sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship User.addresses - there are no foreign keys linking these tables.  Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.

また，特定の1つのテーブルに対して複数のrelationshipを持つ場合，複数のForeignKeyを設定することになりますが，この場合以下の様な例外が発生します．

# ...

class Customer(Base):
    __tablename__ = 'customer'
    id = Column(Integer, primary_key=True)
    name = Column(String)

    billing_address_id = Column(Integer, ForeignKey("address.id"))
    shipping_address_id = Column(Integer, ForeignKey("address.id"))

    billing_address = relationship("Address")
    shipping_address = relationship("Address")

class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    street = Column(String)
    city = Column(String)
    state = Column(String)
    zip = Column(String)

sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Customer.billing_address - there are multiple foreign key paths linking the tables.  Specify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference to the parent table.

これはforeign_keysというパラメータで指定してやることで解決できます．

billing_address = relationship("Address", foreign_keys=[billing_address_id])
shipping_address = relationship("Address", foreign_keys=[shipping_address_id])

また，ForeignKeyを設定せず，自分でJOINの設定をする場合にはprimaryjoinやsecondaryjoinというパラメータを使うことになるようです．詳しくは公式ドキュメント*5をご覧ください．

sqlalchemy.orm.backref()

このメソッドは，リレーションに対するさらに高度な設定を提供します．たとえばSQLAlchemyの特徴の一つである遅延読み出し（lazy），またカスケードの設定，self relationalなテーブルを作るときに用いるremote_sideなど，多くのパラメータが存在します．
「えっそれ，relationship()の引数でいいんじゃないの？？？？」と思っていましたが，もしもrelationship()でlazyなどを定義したとしても適用されるのはそのrelationshipを定義した側だけです．
backrefによって自動的に作成されたリレーションでは，このメソッドを用いて，「受け取る側」にもこの引数を伝える必要があります．

`lazy`

SQLAlchemyにおいて，リレーションを張った先のオブジェクトはデフォルトでは遅延して読み込まれます．

# ...

class User(Base):
    # ...
    addresses = relationship("Address", backref="user")

class Address(Base):
    # ...

class Session(object):
    # ...

if __name__ == "__main__":
    with Session() as s:
        user = User()
        addr = Address()
        user.addresses = [addr]
        user = session.query(User).filter_by(id=1).first() # Userはここで読み込まれる
        print(user.addresses) # Userに紐付いたAddressはここで読み込まれる

select, True

これがデフォルトのパラメータです．プロパティにアクセスされたときにSELECTを発行してデータを引っ張ってきます．selectの代わりにTrueを与えても同義です．
なお発行されるSQLについてはcreate_engineにおいてecho=Trueを与えたときに出力されるものをそのまま貼っています．

# ...
class User(Base):
    # ...
    addresses = relationship("Address", backref=backref("user", lazy="select"), lazy="select")

# ...

user = session.query(User).filter_by(id=1).first() # Userはここで読み込まれる
print(user.addresses) # Userに紐付いたAddressはここで読み込まれる

発行されるSQL

SELECT user.id AS user_id, user.name AS user_name 
FROM user 
WHERE user.id = ? LIMIT ? OFFSET ?

SELECT address.id AS address_id, address.email AS address_email, address.user_id AS address_user_id 
FROM address 
WHERE ? = address.user_id

immediate

これはselectと発行するSQLは全く同じで，読み出しのタイミングのみが異なります．具体的には，上記例で言うとUserが読み出されたタイミングでそれに紐付いたAddressが別のSELECTが発行されて読み出されます．

user = session.query(User).filter_by(id=1).first() # UserもAddressもここで読み込まれる
print(user.addresses)

発行されるSQL

SELECT user.id AS user_id, user.name AS user_name 
FROM user 
WHERE user.id = ? 
LIMIT ? OFFSET ?

SELECT address.id AS address_id, address.email AS address_email, address.user_id AS address_user_id 
FROM address 
WHERE ? = address.user_id

joined, False

JOINまたはLEFT OUTER JOINを用いて，親となる要素と同時にリレーション先も読み込まれます．Falseを与えても同義です．

user = session.query(User).filter_by(id=1).first() # UserもAddressもここで読み込まれる
print(user.addresses)

発行されるSQL

SELECT anon_1.user_id AS anon_1_user_id, anon_1.user_name AS anon_1_user_name, address_1.id AS address_1_id, address_1.email AS address_1_email, address_1.user_id AS address_1_user_id 
FROM (SELECT user.id AS user_id, user.name AS user_name 
FROM user 
WHERE user.id = ?
LIMIT ? OFFSET ?) AS anon_1 LEFT OUTER JOIN address AS address_1 ON anon_1.user_id = address_1.user_id

subquery

JOINを用いて親となる要素と同時にリレーション先も読み込まれます．

user = session.query(User).filter_by(id=1).first() # UserもAddressもここで読み込まれる
print(user.addresses)

発行されるSQL

SELECT address.id AS address_id, address.email AS address_email, address.user_id AS address_user_id, anon_1.user_id AS anon_1_user_id 
FROM (SELECT user.id AS user_id 
FROM user 
WHERE user.id = ?
LIMIT ? OFFSET ?) AS anon_1 JOIN address ON anon_1.user_id = address.user_id ORDER BY anon_1.user_id

dynamic

オブジェクトではなくクエリのみが構築され返されます．
また，One to OneやMany to Oneなリレーションではエラーが出ます．例えば以下の様に設定すると，

class User(Base):
    # ...
    addresses = relationship("Address", backref=backref("user", lazy="dynamic"), lazy="dynamic")

このようなエラーが出ます．

sqlalchemy.exc.InvalidRequestError: On relationship Address.user, 'dynamic' loaders cannot be used with many-to-one/one-to-one relationships and/or uselist=False.

正しくは以下の様に設定しなければいけません．

class User(Base):
    # ...
    addresses = relationship("Address", backref="user", lazy="dynamic")

要するに複数のリレーションを持ちうるrelationship()でしかdynamicは使用できません．

user = session.query(User).filter_by(id=1).first() # Userはここで読み込まれる
print(user.addresses) # Addressは読み込まれずクエリが返される
print(user.addresses.all()) # ここでクエリが発行され，Addressが読み込まれる

発行されるSQL

SELECT user.id AS user_id, user.name AS user_name 
FROM user 
WHERE user.id = ?
 LIMIT ? OFFSET ?

print(user.addresses)したときの出力結果

SELECT address.id AS address_id, address.email AS address_email, address.user_id AS address_user_id 
FROM address 
WHERE :param_1 = address.user_id

noload, None

データを追加した時点では（そのSession内では）読み取りが可能ですが，それをデータベースにcommitして再び別Sessionで取り出すと，そのリレーションを張った先のデータを取ることができなくなります．Noneを与えても同義です．

# データを追加
with Session() as s:
    user = User()
    addr = Address()
    user.addresses = [addr]
    session.add(user)
    session.commit()

# 新規でセッションを作り直して取り出す
with Session() as s:
    user = s.query(User).filter_by(id=1).first() # Userはここで読み込まれる
    print(user.addresses) # 出力結果：[]

発行されるSQL

SELECT user.id AS user_id, user.name AS user_name 
FROM user 
WHERE user.id = ?
 LIMIT ? OFFSET ?

raise, raise_on_sql

raiseまたはraise_on_sqlは1.1から追加されたパラメータで，そのリレーションに対して遅延ロードを許しません．ただし，raise_on_sqlはその遅延ロードのためにSQLを生成する必要がある場合にのみ例外を発生します（raise_on_sqlについてはその制約をくぐり抜けて値をロードする状況がわからないので誰か分かる方コメントを…）．

# データを追加
with Session() as s:
    user = User()
    addr = Address()
    user.addresses = [addr]
    session.add(user)
    session.commit()

# 新規でセッションを作り直して取り出す
with Session() as s:
    user = s.query(User).filter_by(id=1).first() # Userはここで読み込まれる
    print(user.addresses) # .addressesにアクセスすると例外を送出

このとき例外を送出します．

sqlalchemy.exc.InvalidRequestError: 'User.addresses' is not available due to lazy='raise'

`cascade`

例えば親と子の関係があるオブジェクトで親に含まれる子のオブジェクトを削除したとき，子のテーブルからもその要素を消したいときなどがあります．デフォルトではcascade="save-update, merge"になっており，以下の様な挙動をします．

# テーブルの定義
from sqlalchemy import Integer, ForeignKey, String, Column
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

Base = declarative_base()

class User(Base):
    # ...

class Address(Base):
    # ...

class Session(object):
    # ...

if __name__ == "__main__":
    with Session() as s:
        user = User()
        addr = Address()
        user.addresses = [addr]
        s.add(user)
        s.commit()
    with Session() as s:
        u = s.query(User).get(1)
        s.delete(u)
        users = s.query(User).all()
        addrs = s.query(Address).all()
        print(len(users))
        print(len(addrs))
        print([addr.user_id for addr in addrs])

実行結果

0 # Userは削除したため0
1 # 紐付いていたAddressは消えない
[None] # 紐付いていたAddressのuser_idはNoneになる

save-update

これはSession.add()したとき，リレーションを貼った先も同時にaddされることを示しています．これにより，一度のaddで関連するオブジェクトを全て登録することができます．

with Session() as session:
    user = User()
    addr1, addr2 = Address(), Address()
    user.addresses = [addr1, addr2]
    session.add(user)
    print(addr1 in session) # True
    print(addr2 in session) # True

この動作を無効化するにはrelationship()にcascade_backref=Falseを設定します．

delete

このパラメータが設定されたとき，いわゆる「親」が削除されるときそれに紐付いた「子」も同時に削除されます．

class User(Base):
    # ...
    addresses = relationship("Address", backref="user", cascade="save-update, merge, delete")

# ...

if __name__ == "__main__":
    with Session() as s:
        user = User()
        addr = Address()
        user.addresses = [addr]
        s.add(user)
        s.commit()
    with Session() as s:
        u = s.query(User).get(1)
        s.delete(u)
        users = s.query(User).all()
        addrs = s.query(Address).all()
        print(len(users))
        print(len(addrs))

実行結果

0 # Userは削除したため0
0 # 紐付いたAddressも消える

またMany to Manyなリレーションにおいては，リレーションを張った先のオブジェクトだけでなく，セカンダリーテーブル上のものもまた削除します．

delete-orphan

親からのリレーションを解除された際にその子オブジェクトを削除します．

class User(Base):
    # ...
    addresses = relationship("Address", backref="user", cascade="save-update, merge, delete-orphan")

# ...

if __name__ == "__main__":
    with Session() as s:
        user = User()
        addr = Address()
        user.addresses = [addr]
        s.add(user)
        s.commit()
    with Session() as s:
        u = s.query(User).get(1)
        u.addresses = [] # Userのアドレスを空に
        users = s.query(User).all()
        addrs = s.query(Address).all()
        print(len(users))
        print(len(addrs))

1 # Userはそのまま
0 # リレーションが解除されたAddressは削除される

基本的にこのパラメータは子オブジェクトに一つの親を持つことしか許さないため，Many to Oneや Many to Manyなリレーションにおいては使用するべきでないとしています．もし使用する場合にはsingle_parent引数を使用するべきであるとしています*6 *7．

merge

これはSession.merge()が親から子へと伝播されるべきであることを示します．merge()メソッドはSessionがオブジェクトをロードする際，元のインスタンスが持つ主キーを元にセッション内のデータと照合し，ない場合データベースへの問い合わせを行ってターゲットとなるオブジェクトを生成した後，元のインスタンスの状態をコピーする，と言ったことがリファレンスに書いてあります．和訳は適当なので参照を貼っておきます（正直に言うとよくわかっていません）*8．

refresh-expire

Session.expire()によって親が期限切れにされた場合，参照されたオブジェクトにこれを伝播します．また，Session.refresh()によって期限切れに設定された後更新された場合，参照されたオブジェクトはrefreshされずexpireされるのみです．

expunge

Session.expunge()の操作によって親がセッションから削除されたとき，参照されたオブジェクトにこれを伝播します．

class User(Base):
    # ...
    addresses = relationship("Address", backref="user", cascade="save-update, merge, expunge")

# ...

if __name__ == "__main__":
    with Session() as s:
        user = User()
        addr = Address()
        user.addresses = [addr]
        s.add(user)
        print(user in s)
        print(addr in s)
        s.expunge(user) # Sessionから取り除く
        print(user in s)
        print(addr in s)

True # addしたのでTrue
True # save-updateによってTrue
False # expunge()によって除かれた
False # cascadeに従って除かれた

基本的なリレーションパターン

だいぶ長くなってしまったので，ここはもう他のサイト様にお任せします．この方のブログ記事にはかなりお世話になっています（ありがとうございます）．

Python の O/Rマッパー SQLAlchemy を使ったリレーショナルマッピング基本 4... | CUBE SUGAR STORAGE

まとめ

高機能すぎて初心者にはなかなか理解できない部分もあり，かなり難しく感じますが，簡単なテーブルであれば非常に手軽に定義できるので便利に使わせてもらっています．もうちょっと使いこなせるようになりたいなぁ…

*1:オブジェクトリレーショナルマッパチュートリアル — SQLAlchemy 0.6.5 ドキュメント (和訳)

*2:SQLAlchemy Documentation — SQLAlchemy 1.1 Documentation

*3:クラスのマッピング — SQLAlchemy 0.6.5 ドキュメント (和訳)

*4:Linking Relationships with Backref — SQLAlchemy 1.1 Documentation

*5:Configuring how Relationship Joins — SQLAlchemy 1.1 Documentation

*6:Cascades — SQLAlchemy 1.1 Documentation

*7:Relationships API — SQLAlchemy 1.1 Documentation

*8:Session API — SQLAlchemy 1.1 Documentation

ぽよメモ

レガシーシステム考古学専攻

SQLAlchemyのリレーションにおけるメソッドやパラメータについてのメモ

環境

`relation()`と`relationship()`

`backref`と`back_populates`

backrefの場合

back_populatesの場合

上記例におけるForeignKey

sqlalchemy.orm.backref()

`lazy`

select, True

immediate

joined, False

subquery

dynamic

noload, None

raise, raise_on_sql

`cascade`

save-update

delete

delete-orphan

merge

refresh-expire

expunge

基本的なリレーションパターン

まとめ

環境

relation()とrelationship()

backrefとback_populates

backrefの場合

back_populatesの場合

上記例におけるForeignKey

sqlalchemy.orm.backref()

lazy

select, True

immediate

joined, False

subquery

dynamic

noload, None

raise, raise_on_sql

cascade

save-update

delete

delete-orphan

merge

refresh-expire

expunge

基本的なリレーションパターン

まとめ

`relation()`と`relationship()`

`backref`と`back_populates`

`lazy`

`cascade`