SQLAlchemy secondary join
from sqlalchemy import ( | |
Column, | |
ForeignKey, | |
Integer, | |
Text, | |
create_engine) | |
from sqlalchemy.ext.declarative import declarative_base | |
from sqlalchemy.orm import ( | |
relationship, | |
sessionmaker) | |
# ORM Base class | |
Base = declarative_base() | |
class Parent(Base): | |
__tablename__ = 'parent' | |
id = Column(Integer, primary_key=True) | |
name = Column(Text) | |
children = relationship('Child', back_populates='parent') | |
child_virtues = relationship( | |
'Virtue', | |
secondary='join(Child, ChildVirtue, ChildVirtue.child_id == Child.id)', | |
primaryjoin='Parent.id == Child.parent_id', | |
secondaryjoin='Virtue.id == ChildVirtue.virtue_id', | |
viewonly=True) | |
class Child(Base): | |
__tablename__ = 'child' | |
id = Column(Integer, primary_key=True) | |
name = Column(Text) | |
parent_id = Column(ForeignKey('parent.id')) | |
parent = relationship('Parent', back_populates='children') | |
virtues = relationship('Virtue', secondary='child_virtue') | |
class ChildVirtue(Base): | |
__tablename__ = 'child_virtue' | |
id = Column(Integer, primary_key=True) | |
child_id = Column(ForeignKey('child.id')) | |
child = relationship('Child') | |
virtue_id = Column(ForeignKey('virtue.id')) | |
virtue = relationship('Virtue') | |
class Virtue(Base): | |
__tablename__ = 'virtue' | |
id = Column(Integer, primary_key=True) | |
name = Column(Text) | |
def make_session(echo=False): | |
engine = create_engine('sqlite://', echo=echo) | |
Base.metadata.create_all(bind=engine) | |
return sessionmaker(bind=engine)() | |
def main(): | |
session = make_session(echo=True) | |
alice = Parent(name='Alice') | |
bob = Parent(name='Bob') | |
charlie = Child(name='Charlie', parent=alice) | |
charlie.virtues.append(Virtue(name='Captivating')) | |
diana = Child(name='Diana', parent=bob) | |
diana.virtues.append(Virtue(name='Dazzling')) | |
edward = Child(name='Edward', parent=alice) | |
edward.virtues.append(Virtue(name='Eccentric')) | |
farah = Child(name='Farah', parent=bob) | |
farah.virtues.append(Virtue(name='Faithful')) | |
session.add_all([alice, bob]) | |
session.commit() | |
for parent in session.query(Parent): | |
print(f'Virtues of children of {parent.name}:') | |
for virtue in parent.child_virtues: | |
print(f' {virtue.name}') | |
if __name__ == '__main__': | |
main() |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment