假设我有以下两个SQLAlchemy ORM类:
import sqlalchemy as sa
class Address(Base):
__tablename__ = 'DimAddress'
AddressKey = sa.Column(sa.Integer, primary_key=True)
# ... columns ...
class DealerOrganisation(Base):
__tablename__ = 'DimDealerOrganisation'
DealerOrganisationKey = sa.Column(sa.Integer, primary_key=True)
# ... columns ...
DealerOrganizationAddressKey = sa.Column(sa.Integer, sa.ForeignKey('DimAddress.AddressKey'), nullable=False)
# ... columns ...
address = relationship('Address')
我可以获得经销商组织及其地址(如果有),如下所示:
^{pr2}$这给我的SQL大致如下:
SELECT *
FROM DimDealerOrganisation
LEFT JOIN DimAddress ON AddressKey = DealerOrganizationAddressKey
但是,如果我只想对相关对象的子集执行ORM查询,该怎么办:
SELECT *
FROM DimDealerOrganisation
LEFT JOIN DimAddress ON AddressKey = DealerOrganizationAddressKey AND ZipCode = '90210'
也就是说,我要所有的经销商,但我只想要他们的地址,如果邮政编码是90210。据我所知,join()和outerjoin()允许您指定关系或显式条件,但不能同时指定两者。在这个人为的例子中,我可以使用显式条件并取回行而不是ORM对象,但在涉及多个表和一对多关系的实际查询中,这将是不方便的。我想向on子句添加额外的条件,但仍然要它填充返回的dealorganization对象的address
属性。这可能吗?在
您将要在联接中使用and_ operator from SQLAlchemy。我想它看起来像:
^{1}$相关问题 更多 >
编程相关推荐