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 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/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/3092885d-246b-4e94-abfa-99842307870a%40www.fastmail.com. For more options, visit https://groups.google.com/d/optout.