Wanted to note: this fix seems to be required to use composite keys with
sqlite / selectin as well.
On Thursday, 27 June 2019 15:53:44 UTC-4, Steven James wrote:
>
> 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.
>> 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
>>
>> <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 view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/148fb0bd-1615-477d-9858-378da10eb4bb%40googlegroups.com.
