Menu
Assuming a table foo with compound primary key (a,b), How I can generate following sql query with SQLAlchemy (postgresql dialect)?
SELECT * FROM foo WHERE (a,b) IN ((1,2), (2,3));
770
The grouping is done with the group_by() query method, which takes the column to use for the grouping as an argument, same as the GROUP BY counterpart in SQL. The statement ends by calling subquery() , which tells SQLAlchemy that our intention for this query is to use it inside a bigger query instead of on its own.
all() method. The Query object, when asked to return full entities, will deduplicate entries based on primary key, meaning if the same primary key value would appear in the results more than once, only one object of that primary key would be present. This does not apply to a query that is against individual columns.
all() will return all records which match our query as a list of objects.
It returns an instance based on the given primary key identifier providing direct access to the identity map of the owning Session. It creates a SQL JOIN against this Query object's criterion and apply generatively, returning the newly resulting Query. It returns exactly one result or raise an exception.
Here is the answer:
from sqlalchemy.sql.expression import Tuple
session.query(Foo).filter(Tuple(Foo.a, Foo.b).in_([(1,2), (3,4)])).all()
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With