We are using Postgres, the baked extension and have code along these lines:
query = bakery(
lambda s: s.query(Item)
.filter(Item.parent_id.op('IN')(bindparam('parent_ids')))
result = query(dbsession).params(parent_ids=tuple(parent_ids)).all()
The .op('IN') approach was used to get the bindparam working with an
IN-clause. (See https://github.com/sqlalchemy/sqlalchemy/issues/3574)
With the above approach, the relevant part of the SQL query looks something
like this:
WHERE items.parent_id IN %(parent_ids)s
and SQLAlchemy passes the list of parent_ids nicely as:
{
'parent_ids': ('abc', 'def', 'gjh')
}
For our case, Postgres executes the query significantly faster if we use
the PG-specific VALUES syntax (see here for an example:
https://stackoverflow.com/questions/40443409/postgresql-in-operator-performance-list-vs-subquery).
Therefore we are trying to achieve two things:
1) Somehow get an SQL-output like this:
WHERE items.parent_id IN (VALUES ('abc'), ('def'), ('gjh'))
2) Make this work with bindparam, so we can pass the parent_ids as
list/tuple into the baked query.
I have read
https://stackoverflow.com/questions/18858291/values-clause-in-sqlalchemy,
https://stackoverflow.com/questions/33767740/how-to-make-use-of-bindparam-in-a-custom-compiled-expression/33815597#33815597
and https://groups.google.com/d/topic/sqlalchemy/JW2oigBVpik/discussion ,
but I'm unable to get any of it to work.
How can I set this up so I can do the following (or something along those
lines)?
.filter(Item.parent_id.op('IN')(values(bindparam('parent_ids'))))
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
