通过 SQLAlchemy 中的链接对象键过滤查询 [英] Filter query by linked object key in SQLAlchemy
问题描述
从标题this来看是完全相同的问题,但是我看不出任何答案如何适用于我的用例:
Judging by the title this would be the exact same question, but I can't see how any of the answers are applicable to my use case:
我有两个类以及它们之间的关系:
I have two classes and a relationship between them:
treatment_association = Table('tr_association', Base.metadata,
Column('chronic_treatments_id', Integer, ForeignKey('chronic_treatments.code')),
Column('animals_id', Integer, ForeignKey('animals.id'))
)
class ChronicTreatment(Base):
__tablename__ = "chronic_treatments"
code = Column(String, primary_key=True)
class Animal(Base):
__tablename__ = "animals"
treatment = relationship("ChronicTreatment", secondary=treatment_association, backref="animals")
我希望能够仅选择经过代码为X"的治疗的动物.我尝试了很多方法.
I would like to be able to select only the animals which have undergon a treatment which has the code "X". I tried quite a few approaches.
这个失败了一个AttributeError
:
sql_query = session.query(Animal.treatment).filter(Animal.treatment.code == "chrFlu")
for item in sql_query:
pass
mystring = str(session.query(Animal))
这个很高兴返回一个未过滤的动物列表:
And this one happily returns a list of unfiltered animals:
sql_query = session.query(Animal.treatment).filter(ChronicTreatment.code == "chrFlu")
for item in sql_query:
pass
mystring = str(session.query(Animal))
与我可以放在一起的上述线程中的示例最接近的内容:
The closest thing to the example from the aforementioned thread I could put together:
subq = session.query(Animal.id).subquery()
sql_query = session.query(ChronicTreatment).join((subq, subq.c.treatment_id=="chrFlu"))
for item in sql_query:
pass
mystring = str(session.query(Animal))
mydf = pd.read_sql_query(mystring,engine)
也会因 AttributeError
而失败.
你能帮我整理这份清单吗?
Can you hel me sort this list?
推荐答案
首先,表定义有两个问题:
First, there are two issues with table definitions:
1) 在 treatment_association
你有 Integer
列指向 chronic_treatments.code
而 code
是 <代码>字符串代码>列.
1) In the treatment_association
you have Integer
column pointing to chronic_treatments.code
while the code
is String
column.
我认为在 chronic_treatments
中有一个整数 id
更好,这样你就不会在另一个表中复制字符串代码,也有机会添加稍后将有更多字段添加到chronic_treatments.更新:不完全正确,您仍然可以添加更多字段,但是如果您决定重命名它,更改代码"会更加复杂.
I think it's just better to have an integer id
in the chronic_treatments
, so you don't duplicate the string code in another table and also have a chance to add more fields to chronic_treatments later.
Update: not exactly correct, you still can add more fields, but it will be more complex to change your 'code' if you decide to rename it.
2) 在Animal
模型中,您有一个名为treatment
的关系.这很令人困惑,因为您有多对多关系,它应该是复数 - treatments
.
2) In the Animal
model you have a relation named treatment
. This is confusing because you have many-to-many relation, it should be plural - treatments
.
解决以上两个问题后,您的查询为什么不起作用应该更清楚了.这个(我用 treatments
替换了 treatment
:
After fixing the above two, it should be clearer why your queries did not work.
This one (I replaced treatment
with treatments
:
sql_query = session.query(Animal.treatments).filter(
Animal.treatments.code == "chrFlu")
Animal.treatments
表示多对多关系,它不是 SQL Alchemy 模式,因此您不能将其传递给 query
或使用在 filter
中.
The Animal.treatments
represents a many-to-many relation, it is not an SQL Alchemy mode, so you can't pass it to the query
nor use in a filter
.
由于同样的原因,下一个无法工作(您将 Animal.treatments
传递到 query
中.
Next one can't work for the same reason (you pass Animal.treatments
into the query
.
最后一个更接近,您实际上需要 join
才能获得结果.
The last one is closer, you actually need join
to get your results.
我认为将查询理解为 SQL 会更容易(而且您无论如何都需要了解 SQL 才能使用 sqlalchemy):
I think it is easier to understand the query as SQL (and you anyway need to know SQL to be able to use sqlalchemy):
animals = session.query(Animal).from_statement(text(
"""
select distinct animals.* from animals
left join tr_association assoc on assoc.animals_id = animals.id
left join chronic_treatments on chronic_treatments.id = assoc.chronic_treatments_id
where chronic_treatments.code = :code
""")
).params(code='chrFlu')
它将选择animals
并通过tr_association
加入chronic_treatments
,并通过代码过滤结果.
It will select animals
and join chronic_treatments
through the tr_association
and filter the result by code.
有了这个,就可以很容易地使用 SQL-less 语法重写它:
Having this it is easy to rewrite it using SQL-less syntax:
sql_query = session.query(Animal).join(Animal.treatments).filter(
ChronicTreatment.code == "chrFlu")
这将返回您想要的 - 使用给定代码进行相关慢性治疗的动物列表.
That will return what you want - a list of animals who have related chronic treatment with given code.
这篇关于通过 SQLAlchemy 中的链接对象键过滤查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!