Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
You can’t perform that action at this time.