On Fri, Feb 8, 2019 at 10:05 AM Martin Stein <[email protected]> wrote: > > Am Donnerstag, 7. Februar 2019 23:45:09 UTC+1 schrieb Mike Bayer: >> >> >> >> 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 >> > > We had started using the .op('IN') approach around v1.1 and I hadn't realized > that SQLAlchemy supports this now. Very good to know, thanks. > > I have integrated your suggested approach with the 'before_cursor_execute' > rewrite of the statement, and it works nicely, thanks again! > > Simply out of curiosity and because I'm trying to understand the internals a > bit better: > > 1) Theoretically, would another possible approach be to implement a sub-class > of BindParameter (let's say PGValuesBindParameter) and - in combination with > the .op('IN') approach - overwrite the bind-parameter behavior so it passes > the values as one long string like this: > .filter(Item.parent_id.op('IN')(PGValuesBindParameter('parent_ids'))) > > # Parameters that are passed into the statement: > { > 'parent_ids': "(VALUES ('abc'), ('def'), ('gjh'))" > }
so the event recipe I gave you , you probably *want* to do this, so that in the event, you not only look at bindparam.expanding, you also do isinstance(param, PGValuesBindParameter). > > 2) I've also considered using the following construct with the array-function > from the postgres-dialect. It achieves the same performance improvement as > the VALUES approach and doesn't require the tricky rewriting of the > VALUES-part with the inner parentheses: > > func.unnest(array(parent_ids)) > > but I couldn't get it to work with bindparams (tried also with > expanding=True). > > Which one would you suggest/do you think is the cleanest... the VALUES > statement-rewrite, or one of those other approaches? ideally the expanding IN system in SQLAlchemy would have a little bit of expansion built into it so you could just get in there to add this keyword. the big issue here is that you need to use the baked system so any writing of these parameters / arrays / whatever needs to happen *after* SQLAlchemy creates the string. the current IN trick you are doing is taking advantage of psycopg2's ability to pass a tuple as a parameter to libpq. psycopg2 probably can't achieve this with "UNNEST" because that's a SQL function, not something that libpq can accept as a parameter. So rewriting the statement after the fact is the best way to go. the workings of it are complicated because we have to also take your single parameter "foo" and add "foo_1", "foo_2", "foo_3", etc. to the parameter dictionary. If you are then passing in a list of tuples, then it has to break into a list of tuples. The logic is at https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/engine/default.py#L748, you can see it's pretty tedious, so it's probably best to keep leveraging that logic. Looking at the logic there, I can see we can leverage the existing "render a tuple" logic so that we can remove the regexp thing if you just pass the params as tuples. This can also be automated if you rewrite the parameter dictionary using the before_execute() event, but here's the more simple idea: from sqlalchemy import Column from sqlalchemy import create_engine from sqlalchemy import Integer from sqlalchemy import String from sqlalchemy.ext import baked from sqlalchemy.ext.compiler import compiles from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import Session from sqlalchemy.sql.expression import BindParameter class PGValuesParam(BindParameter): pass @compiles(PGValuesParam) def _compile_pgvalues(element, compiler, **kw): string = compiler.visit_bindparam(element, **kw) return "(VALUES %s" % string[1:] 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_(PGValuesParam("ids", expanding=True))) ) q = query(s).params(ids=[(1,), (2,), (3,)]) print(q) q.all() > > -- > 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.
