Trying to use SQLAlchemy relationships

Question

As an example, I have a cities table and a movie theaters table. A city has multiple movie theaters so:

class City(Base):
    __tablename__ = 'cities'
    id = Column(Integer, primary_key=True)
    name = Column(VARCHAR(255))
    theaters = relationship("Theater", backref=backref("theaters"))

class Theater(Base):
    __tablename__ = 'theaters'
    id = Column(Integer, primary_key=True)
    city_id = Column(Integer, ForeignKey('cities.id'))
    name = Column(VARCHAR(255), nullable=False)

now i want to get all theaters for a city:

theaters = db_session.query(City).filter_by(city = "New York").join(Theater.city_id).all()

This query throws error:

sqlalchemy.exc.ArgumentError: Join target Theater.city_id does not refer to a mapped entity

not sure what I'm doing wrong?

Feline · Accepted Answer · 2021-05-06 23:26:43Z

3

Something like this should automatically do the join for you:

class City(Base):
    __tablename__ = 'cities'
    id = Column(Integer, primary_key=True)
    name = Column(VARCHAR(255))
    theaters = relationship(
        "Theater", 
        lazy='joined', 
        backref=backref("city")  # This is how you refer to the city
                                 # from theater. In other words, this adds
                                 # a field "city" to the Theater class.
    )

class Theater(Base):
    __tablename__ = 'theaters'
    id = Column(Integer, primary_key=True)
    city_id = Column(Integer, ForeignKey('cities.id'))
    name = Column(VARCHAR(255), nullable=False)

And then the query would be

ny_theaters = session.query(Theater).filter(Theater.city.has(City.name == 'New York')).all()

Make sense?

answered May 6, 2021 at 23:26

Feline

7844 silver badges14 bronze badges

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

Comments

van · Accepted Answer · 2021-05-07 05:54:36Z

1

theaters = db_session.query(Theater).join(City).filter(City.name = "New York").all()

answered May 7, 2021 at 5:54

van

77.5k13 gold badges174 silver badges179 bronze badges

Collectives™ on Stack Overflow

Trying to use SQLAlchemy relationships

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related