Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQLAlchemy relationships across multiple tables

Tags:

I'm trying to create a relationship that spans across 3 tables but I can't quite figure out the syntax.

I have 3 tables TableA, TableB and TableC and the relationship I'm trying to model is:

TableA.my_relationship = relationship(
    'TableC',
    primaryjoin='and_(TableA.fk == TableB.pk, TableB.fk == TableC.pk)',
    viewonly=True
)

so that on an instance of TableA I can do instance_of_a.my_relationship to get the TableC record that's indirectly associated with instance_of_a

like image 241
mjallday Avatar asked Jul 10 '13 20:07

mjallday


People also ask

How do you create a many to many relationship in SQLAlchemy?

Python Flask and SQLAlchemy ORM Many to Many relationship between two tables is achieved by adding an association table such that it has two foreign keys - one from each table's primary key.

How would you describe the relationship between tables using ORM?

Usually, the Table uses the MetaData object associated with the declarative base class, so that the ForeignKey directives can locate the remote tables with which to link. The relationship. back_populates parameter for each relationship() establishes a bidirectional relationship.


1 Answers

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class A(Base):
    __tablename__ = 'a'

    id = Column(Integer, primary_key=True)
    b_id = Column(Integer, ForeignKey('b.id'))

    # method one - put everything into primaryjoin.
    # will work for simple lazy loads but for eager loads the ORM
    # will fail to build up the FROM to correctly include B
    cs = relationship("C",
                # C.id is "foreign" because there can be many C.ids for one A.id
                # B.id is "remote", it sort of means "this is where the stuff
                # starts that's not directly part of the A side"
                primaryjoin="and_(A.b_id == remote(B.id), foreign(C.id) == B.c_id)",
                viewonly=True)

    # method two - split out the middle table into "secondary".
    # note 'b' is the table name in metadata.
    # this method will work better, as the ORM can also handle 
    # eager loading with this one.
    c_via_secondary = relationship("C", secondary="b",
                        primaryjoin="A.b_id == B.id", secondaryjoin="C.id == B.c_id",
                        viewonly=True)
class B(Base):
    __tablename__ = 'b'

    id = Column(Integer, primary_key=True)
    c_id = Column(Integer, ForeignKey('c.id'))

class C(Base):
    __tablename__ = 'c'
    id = Column(Integer, primary_key=True)

e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

sess = Session(e)

sess.add(C(id=1))
sess.flush()
sess.add(B(id=1, c_id=1))
sess.flush()
sess.add(A(b_id=1))
sess.flush()

a1 = sess.query(A).first()
print(a1.cs)

print(a1.c_via_secondary)
like image 172
zzzeek Avatar answered Sep 20 '22 06:09

zzzeek