On Thu, Feb 7, 2019 at 3:59 PM Martin Stein <[email protected]> wrote: > > 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)
note the mentioned issue is closed. IN supports an inline parameter now: https://docs.sqlalchemy.org/en/rel_1_2/orm/extensions/baked.html#baked-in > > 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)? well normally for VALUES I'd have you make a custom clause element. However, you're looking to carry onto the EXPANDING thing as well, which has to happen after the SQL has been rendered but before it is sent to the database. So here's a way to use an event to make that rewrite happen: import re from sqlalchemy import bindparam from sqlalchemy import Column from sqlalchemy import create_engine from sqlalchemy import event from sqlalchemy import Integer from sqlalchemy import String from sqlalchemy.ext import baked from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import Session Base = declarative_base() class A(Base): __tablename__ = "a" id = Column(Integer, primary_key=True) data = Column(String) e = create_engine("postgresql://scott:tiger@localhost/test", echo=True) Base.metadata.create_all(e) s = Session(e) bakery = baked.bakery() query = bakery( lambda s: s.query(A).filter(A.id.in_(bindparam("ids", expanding=True))) ) q = query(s).params(ids=[1, 2, 3]) @event.listens_for(e, "before_cursor_execute", retval=True) def before_cursor_execute( conn, cursor, statement, parameters, context, executemany ): for bind in context.compiled.binds.values(): if bind.expanding: paramnames = sorted( p for p in parameters if p.startswith(bind.key) ) reg = r"IN \(%s\)" % ( ", ".join(r"%%\(%s\)s" % p for p in paramnames) ) statement = re.sub( reg, "IN (VALUES %s)" % ", ".join("(%%(%s)s)" % p for p in paramnames), statement, ) return statement, parameters print(q) q.all() query at the end is: SELECT a.id AS a_id, a.data AS a_data FROM a WHERE a.id IN (VALUES (%(ids_1)s), (%(ids_2)s), (%(ids_3)s)) > > .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. -- 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.
