如何在sqlalchemy ORM查询中使用NOT IN子句

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)

3 个解决方案

#1

Try this:

尝试这个：

subquery = session.query(table_c.id)
query = query.filter(~table_a.id.in_(subquery))

Note: table_a, table_b and table_c should be mapped classes, not Table instances.

注意：table_a，table_b和table_c应该是映射类，而不是Table实例。

#2

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 of ColumnOperators

继承自ColumnOperators的notin_（）方法

implement the NOT IN operator.

实现NOT IN运算符。

This is equivalent to using negation with ColumnOperators.in_(), i.e. ~x.in_(y).

这相当于使用ColumnOperators.in_（）的否定，即~x.in_（y）。

#3

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))

#1