时间戳

首页 > 解决方案 > 尝试使用 SQLAlchemy 关系

python - 尝试使用 SQLAlchemy 关系

问题描述

例如，我有一个城市表和一个电影院表。一个城市有多个电影院，所以：

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)

现在我想获得一个城市的所有剧院：

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

此查询引发错误：

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

不知道我做错了什么？

标签： pythonsqlalchemy

解决方案

像这样的东西应该自动为你做连接：

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)

然后查询将是

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

说得通？

推荐阅读