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)
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 ColumnOperatorsimplement 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 fromnotin_()in previous releases. The previous name remains available for backwards compatibility.
So you may find notin_() in some cases.
notin_([42, 43, 44, 45])
not_in() in 1.4. docs.sqlalchemy.org/en/14/core/…
notin_() works!!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.
query is query object that you showed above.query = query.filter(table_a.id.notin_(subquery))
None ?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))
in_(1,2,3).. I got: 'ColumnOperators.in_() takes 2 positional arguments but 4 were given', so I changed tobetween(1,3)instead and it worked.. In my scenario I had twoin_clauses within anand_clause too - not sure if that combo causes odd message in SQLAlchemy 1.4.41.in_requires an iterable, e.g.in_([1,2,3])