SQLAlchemy secondary join

sqla_secondary.py

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()