Re: [sqlalchemy] 'selectin' loading with composite keys on DB2 for i
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
>>
Re: [sqlalchemy] 'selectin' loading with composite keys on DB2 for i
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
>
>
Re: [sqlalchemy] 'selectin' loading with composite keys on DB2 for i
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
>
> .
> 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
[sqlalchemy] 'selectin' loading with composite keys on DB2 for i
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 -- 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. For more options, visit https://groups.google.com/d/optout.
