[SqlAlchemy] Snippets

リレーショナルマッピングの仕方

One to Many

class One(Base):
    __tablename__ = 'one_t'
    id = Column(Integer, primary_key=True)

    manys = relation('Many', uselist=True, back_populates='one')

class Many(Base):
    __tablename__ = 'many_t'
    id = Column(Integer, primary_key=True)
    fid = Column(Integer, ForeignKey('one_t.id'))

    one = relation('One', uselist=False, back_populates='manys')

Many to One

class Many(Base):
    __tablename__ = 'many_t'
    id = Column(Integer, primary_key=True)
    fid = Column(Integer, ForeignKey('one_t.id'))

    one = relation('One', uselist=False, back_populates='manys')

class One(Base):
    __tablename__ = 'one_t'
    id = Column(Integer, primary_key=True)

    manys = relation('Many', uselist=True, back_populates='one')

One to One

class One(Base):
    __tablename__ = 'one_t'
    id = Column(Integer, primary_key=True)

    another_one = relation('AnotherOne', uselist=False, back_populates='one')

class AnotherOne(Base):
    __tablename__ = 'another_one_t'
    id = Column(Integer, ForeignKey('one_t.id'), primary_key=True)

    one = relation('One', uselist=False, back_populates='another_one')

Many to Many

クラスを作成したくない場合

cross_table = Table('many_another_many', Base.metadata,
    Column('many_id', Integer, ForeignKey('many_t.id')),
    Column('another_many_id', Integer, ForeignKey('another_many_t.id'))
)

class Many(Base):
    __tablename__ = 'many_t'
    id = Column(Integer, primary_key=True)

    another_manys = relation('AnotherMany', uselist=True, back_populates='manys', secondary=cross_table)

class AnotherMany(Base):
    __tablename__ = 'another_many_t'
    id = Column(Integer, primary_key=True)

    manys = relation('Many', uselist=True, back_populates='another_manys', secondary=cross_table)

クラスを作成したい場合

class ManyToAnotherManyEntity(BASE):
    __tablename__ = "many_to_another_many_t"

    many_id = Column(Integer, ForeignKey("many_t.id"), primary_key=True)
    another_many_id = Column(Integer, ForeignKey("another_many_t.id"), primary_key=True)

class Many(Base):
    __tablename__ = 'many_t'
    id = Column(Integer, primary_key=True)

    another_manys = relation('AnotherMany', uselist=True, back_populates='manys', secondary=ManyToAnotherManyEntity.__tablename__)

class AnotherMany(Base):
    __tablename__ = 'another_many_t'
    id = Column(Integer, primary_key=True)

    manys = relation('Many', uselist=True, back_populates='another_manys', secondary=ManyToAnotherManyEntity.__tablename__)

結合条件を複数定義したいとき

複数のキーとjoinしたり、外部キーと結合する列が一意に定まらないときにprimaryjoinを使う。

class One(Base):
    __tablename__ = 'one_t'
    id = Column(Integer, ForeignKey('another_one_t.id'), primary_key=True)

    another_one = relation(
        'AnotherOne',
        uselist=False,
        primaryjoin="and_(AnotherOne.id==One.id,"
        "AnotherOne.type=='man')",)

class AnotherOne(Base):
    __tablename__ = 'another_one_t'
    id = Column(Integer, primary_key=True)
    type = Column(String, primary_key=True)

    one = relation('One', uselist=False, back_populates='another_one')

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

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