'selectin' loading with composite keys on DB2 for i

[Steven James]

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.

Thanks,

Steven James

[Steven James]

Sorry... made a typo there... the desired syntax is:

`SELECT * FROM table WHERE (table.a, table.b) IN (VALUES (?, ?), (?,?))`

[Mike Bayer]

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+...@googlegroups.com.

To post to this group, send email to sqlal...@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.

For more options, visit https://groups.google.com/d/optout.

[Steven James]

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!

To unsubscribe from this group and stop receiving emails from it, send an email to sqlal...@googlegroups.com.

[Steven James]

Wanted to note: this fix seems to be required to use composite keys with sqlite / selectin as well.