Thank you. This was very helpful.
One non-trivial thing that stumped me for a while is that if B is derived
from a B_base using joined-table inheritance, and the order variable is in
the base table B_base, then it seems one must include B_base explicitly --
as highlighted below.
from sqlalchemy import Column, Integer, ForeignKey, create_engine, select,
join
from sqlalchemy.orm import create_session, relationship, backref, mapper
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.associationproxy import association_proxy
sqlite = 'sqlite:///test_a_to_b.db'
e = create_engine(sqlite, echo=True)
Base = declarative_base(bind=e)
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
bs = association_proxy("a_to_bs", "b", creator=lambda x: A_to_B(b=x))
class B_base(Base):
__tablename__ = 'b_base'
id = Column(Integer, primary_key=True)
order = Column(Integer)
class B(B_base):
__tablename__ = 'b'
id = Column(Integer, ForeignKey(str(B_base.__table__) + ".id"),
primary_key=True)
class A_to_B(Base):
__tablename__ = 'a_to_b'
id = Column(Integer, primary_key=True)
a_id = Column(Integer, ForeignKey(str(A.__table__) + ".id"),
nullable=False, index=True)
a = relationship(A, foreign_keys=[a_id],
backref=backref("a_to_bs",
primaryjoin="and_(A_to_B.a_id == A.id, A_to_B.b_id == B.id*, B.id ==
B_base.id*)", order_by="B.order"))
b_id = Column(Integer, ForeignKey(str(B.__table__) + ".id"),
nullable=False)
b = relationship(B, foreign_keys=[b_id], lazy="subquery")
def __init__(self, a=None, b=None):
self.a = a
self.b = b
if __name__ == '__main__':
# recreate database
Base.metadata.drop_all()
Base.metadata.create_all()
# populate
session = create_session(bind=e, autocommit=False)
a = A(bs=[B(order=10), B(order=2)])
session.add(a)
session.commit()
session.close_all()
# read
session = create_session(bind=e, autocommit=False)
a = session.query(A).one()
for b in a.bs:
print b.order
for a_to_b in a.a_to_bs:
print a_to_b.b.order
session.close_all()
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
