SQLAlchemy's many-to-many relationship table

Question

I am trying to build a relationship with another many-to-many relationship, the code is as follows:

from sqlalchemy import Column, Integer, ForeignKey, Table, ForeignKeyConstraint, create_engine from sqlalchemy.orm import relationship, backref, scoped_session, sessionmaker from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() supervision_association_table = Table('supervision', Base.metadata, Column('supervisor_id', Integer, ForeignKey('supervisor.id'), primary_key=True), Column('client_id', Integer, ForeignKey('client.id'), primary_key=True) ) class User(Base): __tablename__ = 'user' id = Column(Integer, primary_key=True) class Supervisor(User): __tablename__ = 'supervisor' __mapper_args__ = {'polymorphic_identity': 'supervisor'} id = Column(Integer, ForeignKey('user.id'), primary_key = True) schedules = relationship("Schedule", backref='supervisor') class Client(User): __tablename__ = 'client' __mapper_args__ = {'polymorphic_identity': 'client'} id = Column(Integer, ForeignKey('user.id'), primary_key = True) supervisor = relationship("Supervisor", secondary=supervision_association_table, backref='clients') schedules = relationship("Schedule", backref="client") class Schedule(Base): __tablename__ = 'schedule' __table_args__ = ( ForeignKeyConstraint(['client_id', 'supervisor_id'], ['supervision.client_id', 'supervision.supervisor_id']), ) id = Column(Integer, primary_key=True) client_id = Column(Integer, nullable=False) supervisor_id = Column(Integer, nullable=False) engine = create_engine('sqlite:///temp.db') db_session = scoped_session(sessionmaker(bind=engine)) Base.metadata.create_all(bind=engine) 

What I want to do is associate the schedule with a specific Client-Supervisor relationship, although I did not know how to do this. After going through the SQLAlchemy documentation, I found some tips, as a result of which ExternalKeyConstraint was found in the schedule table.

How can I indicate the relationship for this association to work?

Answer 1

You need to map the superv_association_table file so that you can create relationships with / from it.

I can gloss over something here, but it seems that, since you have a lot to many, you really cannot have Client.schedules - if I say Client.schedules.append (some_schedule), what line does this indicate in “supervision”? Thus, the following example provides read-only access for those who join each SupervisorAssociation's Schedule collections. The association_proxy extension is used to hide, when convenient, the details of the SupervisionAssociation object.

 from sqlalchemy import Column, Integer, ForeignKey, Table, ForeignKeyConstraint, create_engine from sqlalchemy.orm import relationship, backref, scoped_session, sessionmaker from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.ext.associationproxy import association_proxy from itertools import chain Base = declarative_base() class SupervisionAssociation(Base): __tablename__ = 'supervision' supervisor_id = Column(Integer, ForeignKey('supervisor.id'), primary_key=True) client_id = Column(Integer, ForeignKey('client.id'), primary_key=True) supervisor = relationship("Supervisor", backref="client_associations") client = relationship("Client", backref="supervisor_associations") schedules = relationship("Schedule") class User(Base): __tablename__ = 'user' id = Column(Integer, primary_key=True) class Supervisor(User): __tablename__ = 'supervisor' __mapper_args__ = {'polymorphic_identity': 'supervisor'} id = Column(Integer, ForeignKey('user.id'), primary_key = True) clients = association_proxy("client_associations", "client", creator=lambda c: SupervisionAssociation(client=c)) @property def schedules(self): return list(chain(*[c.schedules for c in self.client_associations])) class Client(User): __tablename__ = 'client' __mapper_args__ = {'polymorphic_identity': 'client'} id = Column(Integer, ForeignKey('user.id'), primary_key = True) supervisors = association_proxy("supervisor_associations", "supervisor", creator=lambda s: SupervisionAssociation(supervisor=s)) @property def schedules(self): return list(chain(*[s.schedules for s in self.supervisor_associations])) class Schedule(Base): __tablename__ = 'schedule' __table_args__ = ( ForeignKeyConstraint(['client_id', 'supervisor_id'], ['supervision.client_id', 'supervision.supervisor_id']), ) id = Column(Integer, primary_key=True) client_id = Column(Integer, nullable=False) supervisor_id = Column(Integer, nullable=False) client = association_proxy("supervisor_association", "client") engine = create_engine('sqlite:///temp.db', echo=True) db_session = scoped_session(sessionmaker(bind=engine)) Base.metadata.create_all(bind=engine) c1, c2 = Client(), Client() sp1, sp2 = Supervisor(), Supervisor() sch1, sch2, sch3 = Schedule(), Schedule(), Schedule() sp1.clients = [c1] c2.supervisors = [sp2] c2.supervisor_associations[0].schedules = [sch1, sch2] c1.supervisor_associations[0].schedules = [sch3] db_session.add_all([c1, c2, sp1, sp2, ]) db_session.commit() print c1.schedules print sp2.schedules