问题跨多个表的SQLAlchemy关系

我正在尝试创建跨越3个表的关系，但我无法弄清楚语法。

我有3张桌子 TableA， TableB 和 TableC 我想建模的关系是：

TableA.my_relationship = relationship(
    'TableC',
    primaryjoin='and_(TableA.fk == TableB.pk, TableB.fk == TableC.pk)',
    viewonly=True
)

所以在一个实例上 TableA 我可以 instance_of_a.my_relationship 获得 TableC 记录与...间接相关的记录 instance_of_a

1974

2017-07-10 20:52

起源

答案:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class A(Base):
    __tablename__ = 'a'

    id = Column(Integer, primary_key=True)
    b_id = Column(Integer, ForeignKey('b.id'))

    # method one - put everything into primaryjoin.
    # will work for simple lazy loads but for eager loads the ORM
    # will fail to build up the FROM to correctly include B
    cs = relationship("C",
                # C.id is "foreign" because there can be many C.ids for one A.id
                # B.id is "remote", it sort of means "this is where the stuff
                # starts that's not directly part of the A side"
                primaryjoin="and_(A.b_id == remote(B.id), foreign(C.id) == B.c_id)",
                viewonly=True)

    # method two - split out the middle table into "secondary".
    # note 'b' is the table name in metadata.
    # this method will work better, as the ORM can also handle 
    # eager loading with this one.
    c_via_secondary = relationship("C", secondary="b",
                        primaryjoin="A.b_id == B.id", secondaryjoin="C.id == B.c_id",
                        viewonly=True)
class B(Base):
    __tablename__ = 'b'

    id = Column(Integer, primary_key=True)
    c_id = Column(Integer, ForeignKey('c.id'))

class C(Base):
    __tablename__ = 'c'
    id = Column(Integer, primary_key=True)

e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

sess = Session(e)

sess.add(C(id=1))
sess.flush()
sess.add(B(id=1, c_id=1))
sess.flush()
sess.add(A(b_id=1))
sess.flush()

a1 = sess.query(A).first()
print(a1.cs)

print(a1.c_via_secondary)

2017-07-11 01:03

一种关系定义方法是否因任何原因而优先于另一种？显式引用 secondary 在方法二中，直观地看起来比复杂的更清晰 primaryjoin 方法一中的str ......但方法一似乎可能更强大。 - Russ

这是一种奇怪的，所以就次要而言，并不是原来打算如何使用“次要”，例如：它希望FK能够处于中间位置。我不确定这种“次要”关系是否真的能够正确地保持，例如它可能还需要viewonly = True。在构建诸如预先加载之类的东西时，ORM也会做出不同的选择，在这方面，“辅助”版本可以做出更好的决定，因为它知道FROM子句中有一个额外的表。 - zzzeek

确认，加入急切加载只适用于第二个，第一个无法正确建立FROM - zzzeek

如果'b'表没有映射到类并且只是一个辅助表怎么办？ “辅助”方法如何加入会改变？ - Artem Zaytsev

答案:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class A(Base):
    __tablename__ = 'a'

    id = Column(Integer, primary_key=True)
    b_id = Column(Integer, ForeignKey('b.id'))

    # method one - put everything into primaryjoin.
    # will work for simple lazy loads but for eager loads the ORM
    # will fail to build up the FROM to correctly include B
    cs = relationship("C",
                # C.id is "foreign" because there can be many C.ids for one A.id
                # B.id is "remote", it sort of means "this is where the stuff
                # starts that's not directly part of the A side"
                primaryjoin="and_(A.b_id == remote(B.id), foreign(C.id) == B.c_id)",
                viewonly=True)

    # method two - split out the middle table into "secondary".
    # note 'b' is the table name in metadata.
    # this method will work better, as the ORM can also handle 
    # eager loading with this one.
    c_via_secondary = relationship("C", secondary="b",
                        primaryjoin="A.b_id == B.id", secondaryjoin="C.id == B.c_id",
                        viewonly=True)
class B(Base):
    __tablename__ = 'b'

    id = Column(Integer, primary_key=True)
    c_id = Column(Integer, ForeignKey('c.id'))

class C(Base):
    __tablename__ = 'c'
    id = Column(Integer, primary_key=True)

e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

sess = Session(e)

sess.add(C(id=1))
sess.flush()
sess.add(B(id=1, c_id=1))
sess.flush()
sess.add(A(b_id=1))
sess.flush()

a1 = sess.query(A).first()
print(a1.cs)

print(a1.c_via_secondary)

2017-07-11 01:03

确认，加入急切加载只适用于第二个，第一个无法正确建立FROM - zzzeek

如果'b'表没有映射到类并且只是一个辅助表怎么办？ “辅助”方法如何加入会改变？ - Artem Zaytsev

问题 跨多个表的SQLAlchemy关系

答案:

答案:

热门问题

问题跨多个表的SQLAlchemy关系