On Mon, Feb 11, 2019 at 3:37 PM Martin Stein <[email protected]> wrote: > > So I have replaced the regex-based rewrite with the PGValuesParam approach > that we talked about. One issue with the regex-based rewrite was that in its > current form it breaks for parameter-lists > 10 elements, because the inner > sorted(..) call does string-based sorting: > > 'IN \\(%\\(parent_ids_1\\)s, %\\(parent_ids_10\\)s, %\\(parent_ids_11\\)s, > ...' > > whereas the actual sequence in the statement is: > > ... IN (%(parent_ids_1)s, %(parent_ids_2)s, %(parent_ids_3)s ...) > > I'm aware that we could modify the sorting to account for that, but it starts > to feel somewhat tricky. I like the PGValuesParam-idea a lot, because it is > an explicit opt-in approach, that avoids the tricky regex. So I went with the > following: > > class PGValuesParam(BindParameter): > > > @staticmethod > def prepare(parameters): > return [(element,) for element in parameters] > > > Usage in the query then: > > .filter(Item.parent_id.in_(PGValuesParam('parent_ids', expanding=True))) > > > and passing of parameters as: > > query(session).params(parent_ids=PGValuesParam.prepare(parent_ids)) > > The transformation of each list element to a tuple is done in the > prepare-staticmethod. That way, the rest of the team doesn't have to remember > the details besides "Use the .prepare(...)-method when passing > parameter-lists to PGValuesParam." > > The cherry on top would be to do an automatic transformation of the list to > the list-of-tuples for the PGValuesParam, but when I stepped into the > before_execute-event and inspected the data, I couldn't find the > PGValuesParam instance anywhere. I see the list of values as part of > multiparams, but not PGValuesParam itself (which would be needed as the > 'signal' for doing the transformation). > > Anyway, while I would be interested in how to achieve this (it would make the > whole approach very clean), I don't want to take up too much of your time. > So, thank you very much for your help already, Mike. And I've been meaning to > mention this: While I can feel myself getting somewhat frustrated with > Python's lack of good static typing and gravitating towards .NET Core because > of this, SQLAlchemy is the one library that singlehandedly keeps me in the > Python world, because it's so amazing.
it's a tight spot to get the params but inside of before_execute() before the string is generated you'd need to traverse the "clauseelement" to search for PGValuesParam instances. The traversal would be with this function: https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/sql/visitors.py#L276 that is def visit_bindparam(element): if isinstance(element, PGValuesParam): params[element.key] = < make tuples out of the values that are in params > traverse(clauseelement, {}, {"bindparam": visit_bindparam}) haven't tried it, I think "bindparam" should hit your PGValuesParam objects correctly though > > > Am Freitag, 8. Februar 2019 18:03:20 UTC+1 schrieb Mike Bayer: >> >> 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. -- 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.
