Using secondary joins in the SQL Alchemy ORM

Question

I'm trying to set up a secondary many-to-many relationship from one table to two others, via a third in the middle that links to all three. I have two files - one for ORM objects (model.py) and one for schema objects (schema.py) They look like this:

model.py

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



class AbstractBase(object):
    def __repr__(self):
        macros = ["%s=%s" % (key, getattr(self, key, None)) for key in self.__table__.columns.keys()]
        rep = "<%s(%s)>" % (self.__class__.__name__, str.join(', ', macros))
        return rep

Base = declarative_base(cls=AbstractBase)


class A(Base):
    __table__ = schema.a_table
    dees = relationship("D", 
                       secondary=schema.b_table,
                       primaryjoin="A.a_id==b_table.c.a_id",
                       secondaryjoin="b_table.c.c_id==D.d_id")
    cees = relationship("C", 
                         secondary=schema.b_table,
                         primaryjoin="A.a_id==schema.b_table.c.a_id",
                         secondaryjoin="b_table.c.d_id==C.c_id",
                         backref="a_collection")

class C(Base):
    __table__ = schema.c_table

class D(Base):
    __table__ = schema.d_table

schema.py

from sqlalchemy import *
from sqlalchemy.dialects.mysql import *

metadata = MetaData()

a_table = Table(
    'a',
    metadata,
    Column("a_id", INTEGER(), primary_key=True, nullable=False),
    Column("date", DATETIME(timezone=True)),
)

b_table = Table(
    'shipment_runs',
    metadata,
    Column("a_id", ForeignKey("a.a_id"), primary_key=True,),
    Column("c_id", ForeignKey("c.c_id"), primary_key=True),
    Column("d_id", ForeignKey("d.d_id")),
)

c_table = Table(
    'c',
    metadata,
    Column('c_id', INTEGER(), primary_key=True, nullable=False),
    Column('name', VARCHAR(64), unique=True),
)

d_table = Table(
    'd',
    metadata,
    Column('d_id', INTEGER(), primary_key=True, nullable=False)
)

Unfortunately, instantiating this results in the following error:

sqlalchemy.exc.InvalidRequestError: When initializing mapper Mapper|A|a, expression 'A.a_id==b_table.c.a_id' failed to locate a name ("name 'b_table' is not defined"). If this is a class name, consider adding this relationship() to the class after both dependent classes have been defined.

Is there a way I can change my imports or make the mapper be aware of the objects in the schema module somehow?

Community · Accepted Answer · 2017-05-23 11:49:20Z

4

Was able to get it by doing the following:

class B(Base):
    __table__ = schema.b_table


class A(Base):
    __table__ = schema.a_table
    dees = relationship("D", 
                      secondary=b.__table__,
                      primaryjoin="A.a_id==B.a_id",
                      secondaryjoin="B.c_id==D.d_id")
    cees = relationship("C", 
                        secondary=B.__table__,
                        primaryjoin="A.a_id==B.a_id",
                        secondaryjoin="B.d_id==C.c_id",
                        backref="a_collection")

All credit goes to this question:

SQLAlchemy Relationship Error: object has no attribute 'c'

edited May 23, 2017 at 11:49

CommunityBot

11 silver badge

answered Aug 8, 2014 at 18:12

Dan Monego

10.1k6 gold badges42 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jason S · Accepted Answer · 2014-08-08 17:19:42Z

1

In general, you can use the table name in a string, or drop the string and use your actual references.

primaryjoin="A.a_id==shipment_runs.c.a_id",

primaryjoin=schema.a_table.c.a_id==schema.b_table.c.a_id,

That being said, given that you have the ForeignKeys set up in your tables, SQLAlchemy is smart enough that you don't even need the joins for a simple relationship, just secondary.

c_list = relationship("C", secondary=schema.b_table, backref="a_list")

(I think the "C" and "D" are swapped in your example?)

answered Aug 8, 2014 at 17:19

Jason S

13.9k2 gold badges42 silver badges43 bronze badges

1 Comment

Dan Monego Over a year ago

Thanks! The swap was a mistake I made while anonymizing the code - it's been fixed.

Collectives™ on Stack Overflow

Using secondary joins in the SQL Alchemy ORM

model.py

schema.py

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

model.py

schema.py

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related