How to use NOT IN clause in sqlalchemy ORM query
PythonMysqlSqlalchemyPython Problem Overview
how do i convert the following mysql query to 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:
query = session.query(table_a, table_b)
query = query.filter(table_a.id == table_b.id)
Python Solutions
Solution 1 - Python
The ORM internals describe the not_in()
operator (previously notin_()
), so you can say:
query = query.filter(table_a.id.not_in(subquery))
# ^^^^^^
From the docs:
>inherited from the ColumnOperators.not_in()
method of ColumnOperators
>
> implement the NOT IN operator.
>
>This is equivalent to using negation with ColumnOperators.in_(), i.e. ~x.in_(y).
Note that version 1.4 states:
> The not_in()
operator is renamed from notin_()
in previous releases. The previous name remains available for backwards compatibility.
So you may find notin_()
in some cases.
Solution 2 - Python
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.
Solution 3 - Python
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))