Python 如何在 sqlalchemy ORM 查询中使用 NOT IN 子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26182027/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use NOT IN clause in sqlalchemy ORM query
提问by nuttynibbles
how do i convert the following mysql query to sqlalchemy?
我如何将以下 mysql 查询转换为 sqlalchemy？
SELECT * FROM `table_a` ta, `table_b` tb where 1
AND ta.id = tb.id
AND ta.id not in (select id from `table_c`)
so far i have this for sqlalchemy:
到目前为止，我对 sqlalchemy 有这个：
query = session.query(table_a, table_b)
query = query.filter(table_a.id == table_b.id)
回答by Slava Bacherikov
Try this:
尝试这个：
subquery = session.query(table_c.id)
query = query.filter(~table_a.id.in_(subquery))
Note: table_a, table_band table_cshould be mapped classes, not Tableinstances.
注：table_a，table_b并且table_c应该被映射类，而不是Table实例。
回答by nuttynibbles
here is the full code:
这是完整的代码：
#join table_a and table_b
query = session.query(table_a, table_b)
query = query.filter(table_a.id == table_b.id)
# create subquery
subquery = session.query(table_c.id)
# select all from table_a not in subquery
query = query.filter(~table_a.id.in_(subquery))
回答by fedorqui 'SO stop harming'
The ORM internals describe the notin_()operator, so you can say:
ORM 内部描述了notin_()操作符，所以你可以说：
query = query.filter(table_a.id.notin_(subquery))
# ^^^^^^
From the docs:
从文档：
inherited from the
notin_()method ofColumnOperatorsimplement the
NOT INoperator.This is equivalent to using negation with
ColumnOperators.in_(), i.e.~x.in_(y).
继承自的
notin_()方法ColumnOperators实施
NOT IN运算符。这等效于对
ColumnOperators.in_()，即使用否定~x.in_(y)。
