This Worked! @compiles(BinaryExpression, 'ibm_db_sa') def _comp_binary(element, compiler, **kwargs): text = compiler.visit_binary(element, **kwargs) if element.operator is operators.in_op: text = re.sub(r"\) IN \(", ") IN (VALUES ", text) return text
Had to make a couple of changes.... 1) the dialect name is 'ibm_db_sa' and 2) (strangely!) DB2 gave me an error that it explicitly disallows parameter markers in VALUES (but only in the single-column-key case). My other tests seem to indicate that this was a misleading error message but I'm not going to touch it again now that it is working. Thanks! On Thursday, 27 June 2019 15:03:01 UTC-4, Mike Bayer wrote: > > > > On Thu, Jun 27, 2019, at 2:11 PM, Steven James wrote: > > Currently, `selectin` loading with composite keys works for me on MySQL > and SQLite. The documentation states that it also works with Postgres. I'm > currently trying to get it working on DB2 (connecting to a DB2 for i, 7.2 > system. > > (the following assumes a table with the primary key consisting of two > columns: a and b) > > selectin loading currently emits: > `SELECT * FROM table WHERE (table.a, table.b) IN ((?, ?), (?, ?))` > > For this type of loading to work in DB2 (DB2 for i), the syntax needs to > be: > `SELECT * FROM table WHERE (table.a, table.b) IN VALUES (?, ?), (?,?)` > > Is there any way to implement this without a core change? I'm wondering if > I can override the normal operation of in_() using a custom dialect or > custom default comparator. > > > funny thing is that I'm a Red Hat employee, so assuming RH's merger with > IBM goes through I may eventually be an IBM employee, and maybe they'd like > to give me DB2 things to work on :) However, that is not the case right > now and I've done only very limited work with the DB2 driver as I'm sure > you're aware the database itself is a beast. > > So this is something DB2's SQLAlchemy driver will have to add support for > at some point, the selectinload thing is going to become more popular and > also the internal mechanism for "IN" is going to be moving entirely to a > newer architecture called "expanding". That's probably not important here > though. > > For now, in order to get that "VALUES" in there, you don't need to > "change" Core or work with custom datatypes, there's a variety of event > hooks that can give you access to that part of the SQL more at the string > level. I'm able to make this work also on Postgresql by intercepting > BinaryExpression in the compiler, see the example below. > > import re > > from sqlalchemy import Column > from sqlalchemy import create_engine > from sqlalchemy import ForeignKeyConstraint > from sqlalchemy import Integer > from sqlalchemy import String > from sqlalchemy.ext.compiler import compiles > from sqlalchemy.ext.declarative import declarative_base > from sqlalchemy.orm import relationship > from sqlalchemy.orm import selectinload > from sqlalchemy.orm import Session > from sqlalchemy.sql import operators > from sqlalchemy.sql.expression import BinaryExpression > > > @compiles(BinaryExpression, "postgresql") # because I'm testing it here > @compiles(BinaryExpression, "db2") > def _comp_binary(element, compiler, **kw): > text = compiler.visit_binary(element, **kw) > if element.operator is operators.in_op: > text = re.sub(r" IN \(", " IN (VALUES ", text) > return text > > > Base = declarative_base() > > > class A(Base): > __tablename__ = "a" > > id = Column(Integer, primary_key=True) > id2 = Column(Integer, primary_key=True) > data = Column(String) > > bs = relationship("B") > > > class B(Base): > __tablename__ = "b" > id = Column(Integer, primary_key=True) > > a_id = Column(Integer) > a_id2 = Column(Integer) > > __table_args__ = ( > ForeignKeyConstraint(["a_id", "a_id2"], ["a.id", "a.id2"]), > ) > > > e = create_engine("postgresql://scott:tiger@localhost/test", echo=True) > Base.metadata.drop_all(e) > Base.metadata.create_all(e) > > s = Session(e) > > s.add(A(id=1, id2=1, bs=[B(), B()])) > s.commit() > > s.query(A).options(selectinload(A.bs)).all() > > > > > > > Thanks, > Steven James > > > -- > 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 sqlal...@googlegroups.com <javascript:>. > To post to this group, send email to sqlal...@googlegroups.com > <javascript:>. > Visit this group at https://groups.google.com/group/sqlalchemy. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/e7a3b368-d40e-4aba-84d5-10020fb05fe4%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/e7a3b368-d40e-4aba-84d5-10020fb05fe4%40googlegroups.com?utm_medium=email&utm_source=footer> > . > 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/7f6bab32-5717-4fac-bb10-d5b2ee1610dc%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.