Re: [sqlalchemy] Unexpected TypeError when using `choices` as parameter name to TypeDecorator

[Steven James]

Thank you! That makes me feel quite a bit better.

On Thursday, 6 May 2021 at 11:59:14 UTC-4 Mike Bayer wrote:

> the caching routine for datatypes is matching up the names of the 
> parameters to the internal state of the object.   any name will reproduce 
> as long as you name the parameters and internal attributes the same.
>
> On Thu, May 6, 2021, at 11:52 AM, Steven James wrote:
>
> I originally came to that conclusion, and I agree that replacing it with a 
> tuple does fix it, but I still can't explain why using a different 
> parameter name also fixes it.
>
> On Thursday, 6 May 2021 at 11:46:53 UTC-4 Mike Bayer wrote:
>
>
> this is a regression in SQLAlchemy in that the TypeDecorator is being 
> applied to caching without giving the end user any clue or API to control 
> this process.
>
> the name "choices" is not significant, it's that the parameter as given is 
> trying to be part of a cache key, so turn this into a tuple for now to 
> resolve:
>
>
> class AltType(TypeDecorator):
> impl = Unicode(255)
>
> def __init__(self, choices):
> self.choices = tuple(choices)
> super(AltType, self).__init__()
>
>
> https://github.com/sqlalchemy/sqlalchemy/issues/6436
>
>
>
> On Thu, May 6, 2021, at 11:08 AM, Steven James wrote:
>
> This one is baffling me. The following example throws an exception about 
> an unhashable type, related (I think) to the new query caching in 1.4. 
> However, if I change the parameter name to AltType.__init__ to ANYTHING 
> other than `choices`, it works fine. I can't find any reference to the name 
> `choices` in the sqlalchemy codebase.
>
> So why can't I use `choices` as a parameter name here? The answer might be 
> related to the AnnotatedColumn in the generated expression but that's about 
> as far as I've gotten.
>
> ```
> # sqlalchemy 1.4.x
>
> from sqlalchemy import Column, Integer, create_engine, TypeDecorator, 
> Unicode
> from sqlalchemy.orm import sessionmaker, as_declarative, declared_attr
>
>
> class AltType(TypeDecorator):
> impl = Unicode(255)
>
> def __init__(self, choices):
> self.choices = choices
> super(AltType, self).__init__()
>
>
> @as_declarative()
> class Base(object):
> @declared_attr
> def __tablename__(cls):
> return cls.__name__.lower()
>
> pk = Column(Integer, primary_key=True)
>
>
> class MyClass(Base):
> d = Column(AltType(['a', 'list', 'here']))
>
>
> if __name__ == '__main__':
> e = create_engine('sqlite://', echo=True)
> conn = e.connect()
>
> Base.metadata.create_all(e)
>
> s = sessionmaker(e)()
> q = s.query(MyClass).filter(MyClass.d == 'search_str')
> 
> result = q.first()  # < error here
> print(result)
>
> ```
>
> result:
> ```
> Traceback (most recent call last):
>   File "...\scratch_78.py", line 36, in 
> results = q.first()
>   File "...\\lib\site-packages\sqlalchemy\orm\query.py", line 2750, in 
> first
> return self.limit(1)._iter().first()
>   File "...\\lib\site-packages\sqlalchemy\orm\query.py", line 2834, in 
> _iter
> result = self.session.execute(
>   File "...\\lib\site-packages\sqlalchemy\orm\session.py", line 1675, in 
> execute
> result = conn._execute_20(statement, params or {}, execution_options)
>   File "...\\lib\site-packages\sqlalchemy\engine\base.py", line 1521, in 
> _execute_20
> return meth(self, args_10style, kwargs_10style, execution_options)
>   File "...\\lib\site-packages\sqlalchemy\sql\elements.py", line 313, in 
> _execute_on_connection
> return connection._execute_clauseelement(
>   File "...\\lib\site-packages\sqlalchemy\engine\base.py", line 1382, in 
> _execute_clauseelement
> compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
>   File "...\\lib\site-packages\sqlalchemy\sql\elements.py", line 531, in 
> _compile_w_cache
> compiled_sql = compiled_cache.get(key)
>   File "...\\lib\site-packages\sqlalchemy\util\_collections.py", line 918, 
> in get
> item = dict.get(self, key, default)
> TypeError: unhashable type: 'list'
> ```
>
> -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.
> T

Re: [sqlalchemy] Unexpected TypeError when using `choices` as parameter name to TypeDecorator

[Steven James]

I originally came to that conclusion, and I agree that replacing it with a 
tuple does fix it, but I still can't explain why using a different 
parameter name also fixes it.

On Thursday, 6 May 2021 at 11:46:53 UTC-4 Mike Bayer wrote:

> this is a regression in SQLAlchemy in that the TypeDecorator is being 
> applied to caching without giving the end user any clue or API to control 
> this process.
>
> the name "choices" is not significant, it's that the parameter as given is 
> trying to be part of a cache key, so turn this into a tuple for now to 
> resolve:
>
>
> class AltType(TypeDecorator):
> impl = Unicode(255)
>
> def __init__(self, choices):
> self.choices = tuple(choices)
> super(AltType, self).__init__()
>
>
> https://github.com/sqlalchemy/sqlalchemy/issues/6436
>
>
>
> On Thu, May 6, 2021, at 11:08 AM, Steven James wrote:
>
> This one is baffling me. The following example throws an exception about 
> an unhashable type, related (I think) to the new query caching in 1.4. 
> However, if I change the parameter name to AltType.__init__ to ANYTHING 
> other than `choices`, it works fine. I can't find any reference to the name 
> `choices` in the sqlalchemy codebase.
>
> So why can't I use `choices` as a parameter name here? The answer might be 
> related to the AnnotatedColumn in the generated expression but that's about 
> as far as I've gotten.
>
> ```
> # sqlalchemy 1.4.x
>
> from sqlalchemy import Column, Integer, create_engine, TypeDecorator, 
> Unicode
> from sqlalchemy.orm import sessionmaker, as_declarative, declared_attr
>
>
> class AltType(TypeDecorator):
> impl = Unicode(255)
>
> def __init__(self, choices):
> self.choices = choices
> super(AltType, self).__init__()
>
>
> @as_declarative()
> class Base(object):
> @declared_attr
> def __tablename__(cls):
> return cls.__name__.lower()
>
> pk = Column(Integer, primary_key=True)
>
>
> class MyClass(Base):
> d = Column(AltType(['a', 'list', 'here']))
>
>
> if __name__ == '__main__':
> e = create_engine('sqlite://', echo=True)
> conn = e.connect()
>
> Base.metadata.create_all(e)
>
> s = sessionmaker(e)()
> q = s.query(MyClass).filter(MyClass.d == 'search_str')
> 
> result = q.first()  # < error here
> print(result)
>
> ```
>
> result:
> ```
> Traceback (most recent call last):
>   File "...\scratch_78.py", line 36, in 
> results = q.first()
>   File "...\\lib\site-packages\sqlalchemy\orm\query.py", line 2750, in 
> first
> return self.limit(1)._iter().first()
>   File "...\\lib\site-packages\sqlalchemy\orm\query.py", line 2834, in 
> _iter
> result = self.session.execute(
>   File "...\\lib\site-packages\sqlalchemy\orm\session.py", line 1675, in 
> execute
> result = conn._execute_20(statement, params or {}, execution_options)
>   File "...\\lib\site-packages\sqlalchemy\engine\base.py", line 1521, in 
> _execute_20
> return meth(self, args_10style, kwargs_10style, execution_options)
>   File "...\\lib\site-packages\sqlalchemy\sql\elements.py", line 313, in 
> _execute_on_connection
> return connection._execute_clauseelement(
>   File "...\\lib\site-packages\sqlalchemy\engine\base.py", line 1382, in 
> _execute_clauseelement
> compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
>   File "...\\lib\site-packages\sqlalchemy\sql\elements.py", line 531, in 
> _compile_w_cache
> compiled_sql = compiled_cache.get(key)
>   File "...\\lib\site-packages\sqlalchemy\util\_collections.py", line 918, 
> in get
> item = dict.get(self, key, default)
> TypeError: unhashable type: 'list'
> ```
>
> -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 view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/3e863503-4afa-44c3-b54a-4fb26ae5e8c8n%40googlegroups.com
>  
> <https://groups.google.com/d/msgid/sqlalchemy/3e863503-4afa-44c3-b54a-4fb26ae5e8c8n%40googlegroups.com?utm_medium=email_source=footer>
> .
>
>
&

[sqlalchemy] Unexpected TypeError when using `choices` as parameter name to TypeDecorator

[Steven James]

This one is baffling me. The following example throws an exception about an 
unhashable type, related (I think) to the new query caching in 1.4. 
However, if I change the parameter name to AltType.__init__ to ANYTHING 
other than `choices`, it works fine. I can't find any reference to the name 
`choices` in the sqlalchemy codebase.

So why can't I use `choices` as a parameter name here? The answer might be 
related to the AnnotatedColumn in the generated expression but that's about 
as far as I've gotten.

```
# sqlalchemy 1.4.x

from sqlalchemy import Column, Integer, create_engine, TypeDecorator, 
Unicode
from sqlalchemy.orm import sessionmaker, as_declarative, declared_attr


class AltType(TypeDecorator):
impl = Unicode(255)

def __init__(self, choices):
self.choices = choices
super(AltType, self).__init__()


@as_declarative()
class Base(object):
@declared_attr
def __tablename__(cls):
return cls.__name__.lower()

pk = Column(Integer, primary_key=True)


class MyClass(Base):
d = Column(AltType(['a', 'list', 'here']))


if __name__ == '__main__':
e = create_engine('sqlite://', echo=True)
conn = e.connect()

Base.metadata.create_all(e)

s = sessionmaker(e)()
q = s.query(MyClass).filter(MyClass.d == 'search_str')

result = q.first()  # < error here
print(result)

```

result:
```
Traceback (most recent call last):
  File "...\scratch_78.py", line 36, in 
results = q.first()
  File "...\\lib\site-packages\sqlalchemy\orm\query.py", line 2750, in first
return self.limit(1)._iter().first()
  File "...\\lib\site-packages\sqlalchemy\orm\query.py", line 2834, in _iter
result = self.session.execute(
  File "...\\lib\site-packages\sqlalchemy\orm\session.py", line 1675, in 
execute
result = conn._execute_20(statement, params or {}, execution_options)
  File "...\\lib\site-packages\sqlalchemy\engine\base.py", line 1521, in 
_execute_20
return meth(self, args_10style, kwargs_10style, execution_options)
  File "...\\lib\site-packages\sqlalchemy\sql\elements.py", line 313, in 
_execute_on_connection
return connection._execute_clauseelement(
  File "...\\lib\site-packages\sqlalchemy\engine\base.py", line 1382, in 
_execute_clauseelement
compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
  File "...\\lib\site-packages\sqlalchemy\sql\elements.py", line 531, in 
_compile_w_cache
compiled_sql = compiled_cache.get(key)
  File "...\\lib\site-packages\sqlalchemy\util\_collections.py", line 918, 
in get
item = dict.get(self, key, default)
TypeError: unhashable type: 'list'
```

-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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/3e863503-4afa-44c3-b54a-4fb26ae5e8c8n%40googlegroups.com.

Re: [sqlalchemy] Setting Foreign Key based on natural key

[Steven James]

Thank you for taking the time to look at this. I have shied away from using 
events in the past, but now that you mention it I can see how that could 
work for this case.

I had to add slightly to my previous solution. In some cases, the 
relationship is nullable and this solution was silently giving a NULL when 
I expected a Foreign Key constraint failure. 

@parent_key.setter
def parent_key(self, val):
non_existent_id = -999
self.parent_id = coalesce(

select([Parent.id_]).select_from(Parent.__table__).where(Parent.key == 
val).as_scalar(),
non_existent_id
)

(the .as_scalar() is needed to make coalesce() happy)

On Thursday, 19 November 2020 at 14:36:12 UTC-5 Mike Bayer wrote:

>
>
> On Thu, Nov 19, 2020, at 2:03 PM, Steven James wrote:
>
> In general I have set up foreign keys using the following pattern:
>
> p = session.query(Parent).filter(Parent.natural_key == key).one()
> new_child = Child(parent=p)
>
> This makes a SELECT and an INSERT and is fine, but it gets a little 
> cumbersome when I want to create a new child object with many relationships 
> where I am not given the primary key. It basically boils down to requiring 
> custom constructor functions for each kind of object.
>
> What I would like is the following pattern:
>
> new_child = Child(parent_key=key)
>
> where parent_key can be a kind of hybrid_property() on the Child and it 
> would generate an `INSERT INTO child_table (parent_id) VALUES ((SELECT id 
> FROM parent_table WHERE key=?))`  I've gotten it working that way actually, 
> but I wanted to check to make sure that this pattern does not already exist 
> in some easier construct.
>
>
> I think that's a fine pattern to use, it all comes down to when you want 
> to be able to access the object.since you are setting up Child() with a 
> key before the Child has any connection to any session or transaction, that 
> rules out being able to know the "parent_id" right at that moment.You 
> could, if you wanted, use events to do that lookup at different times, such 
> as using the after_attach() event to look up the "parent" where you could 
> load the full Parent object and associate it, that way child.parent would 
> be ready before you ever invoked the INSERT.  Or you could use events like 
> before_insert(), before_update() to set up that parent_id too.  but setting 
> it to the SELECT expression is more succinct and pretty clever too.
>
> there's basically  a lot of event hooks (too many at this point) that can 
> be used for this, but you've found probably the one way to do it without 
> using a hook, so should be good.
>
>
>
> Working example:
>
> from sqlalchemy import Column, Integer, String, ForeignKey, create_engine, 
> select
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.ext.hybrid import hybrid_property
> from sqlalchemy.orm import Session, relationship
>
> Base = declarative_base()
>
> class Parent(Base):
> __tablename__ = 'parent'
> id_ = Column(Integer, primary_key=True)
> key = Column(String(4))
> data = Column(String(64))
>
> def __repr__(self) -> str:
> return 'Parent(id={id_!r}, key={key!r})'.format(**self.__dict__)
>
>
> class Child(Base):
> __tablename__ = 'child'
> id_ = Column(Integer, primary_key=True)
> parent_id = Column(ForeignKey('parent.id_'))
> data = Column(String(64))
>
> parent = relationship(Parent, backref='children', lazy='joined')
>
> @hybrid_property
> def parent_key(self):
> return self.parent.key
>
> @parent_key.expression
> def parent_key(self):
> return Parent.key
>
> @parent_key.setter
> def parent_key(self, val):
> self.parent_id = 
> select([Parent.id_]).select_from(Parent.__table__).where(Parent.key == val)
>
>
> if __name__ == '__main__':
> e = create_engine('sqlite:///', echo=True)
> Base.metadata.create_all(e)
> s = Session(bind=e)
>
> p1 = Parent(key='p1', data='parent1')
> p2 = Parent(key='p2', data='parent2')
>
> s.add_all([p1, p2])
> s.commit()
>
> # initialize child/parent using the "natural key"
> ##  INSERT INTO child (parent_id, data) VALUES ((SELECT parent.id_ 
> FROM parent WHERE parent."key" = ?), ?)
> c = Child(data='i am the child', parent_key='p1')
> s.add(c)
> s.commit()
> print(c.parent)
>
> # update relationship using the "natural key"
> ## UPDATE child SET parent_id=(SELECT parent.id_ FROM parent WHERE 
> parent."key" = ?) WHERE child.id_ = ?
> c.parent_key = 'p2'
> s.com

[sqlalchemy] Setting Foreign Key based on natural key

[Steven James]

In general I have set up foreign keys using the following pattern:

p = session.query(Parent).filter(Parent.natural_key == key).one()
new_child = Child(parent=p)

This makes a SELECT and an INSERT and is fine, but it gets a little 
cumbersome when I want to create a new child object with many relationships 
where I am not given the primary key. It basically boils down to requiring 
custom constructor functions for each kind of object.

What I would like is the following pattern:

new_child = Child(parent_key=key)

where parent_key can be a kind of hybrid_property() on the Child and it 
would generate an `INSERT INTO child_table (parent_id) VALUES ((SELECT id 
FROM parent_table WHERE key=?))`  I've gotten it working that way actually, 
but I wanted to check to make sure that this pattern does not already exist 
in some easier construct.

Working example:

from sqlalchemy import Column, Integer, String, ForeignKey, create_engine, 
select
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.orm import Session, relationship

Base = declarative_base()

class Parent(Base):
__tablename__ = 'parent'
id_ = Column(Integer, primary_key=True)
key = Column(String(4))
data = Column(String(64))

def __repr__(self) -> str:
return 'Parent(id={id_!r}, key={key!r})'.format(**self.__dict__)


class Child(Base):
__tablename__ = 'child'
id_ = Column(Integer, primary_key=True)
parent_id = Column(ForeignKey('parent.id_'))
data = Column(String(64))

parent = relationship(Parent, backref='children', lazy='joined')

@hybrid_property
def parent_key(self):
return self.parent.key

@parent_key.expression
def parent_key(self):
return Parent.key

@parent_key.setter
def parent_key(self, val):
self.parent_id = 
select([Parent.id_]).select_from(Parent.__table__).where(Parent.key == val)


if __name__ == '__main__':
e = create_engine('sqlite:///', echo=True)
Base.metadata.create_all(e)
s = Session(bind=e)

p1 = Parent(key='p1', data='parent1')
p2 = Parent(key='p2', data='parent2')

s.add_all([p1, p2])
s.commit()

# initialize child/parent using the "natural key"
##  INSERT INTO child (parent_id, data) VALUES ((SELECT parent.id_ FROM 
parent WHERE parent."key" = ?), ?)
c = Child(data='i am the child', parent_key='p1')
s.add(c)
s.commit()
print(c.parent)

# update relationship using the "natural key"
## UPDATE child SET parent_id=(SELECT parent.id_ FROM parent WHERE 
parent."key" = ?) WHERE child.id_ = ?
c.parent_key = 'p2'
s.commit()
print(c.parent)

-- 
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/b1fe8102-289a-4b8a-a96f-8acba644f9c8n%40googlegroups.com.

[sqlalchemy] Re: calling stored procedure 1000 times in a row - system impact?

[Steven James]

Question 1:
I don't think there is a good fancy way of doing this built in to 
SQLAlchemy. With your constraint of using a stored proc for inserts (we 
have a similar constraint where I work), one way around the 
multiple-command overhead would be to do a bulk insert to a temporary 
"parameters" table, and then use another stored proc that will send those 
params to your original proc one set at a time, or will implement a more 
efficient bulk "insert from select." Error handling can become complex if 
there are instances where your parameters to the insert function could be 
rejected by the stored procedures.

Some engines will also let you send multiple commands in a "BEGIN ... END" 
sql block, but I have not tested this with Oracle and there is always a 
limit to how large a command like that can be.

Question 2: 
a / b : A single transaction will not span threads or connections in a 
pool. Your described approach would use a single thread with a single 
connection on the client side.
c: The only real overhead here is the 1000 separate db calls, especially if 
they go across a network. There is some overhead to each command sent to 
the db server. 1000 calls is a lot but might not be too many, so before you 
go through any optimization, do some performance testing to make sure you 
need it.

On Wednesday, 21 October 2020 at 13:11:06 UTC-4 thequie...@gmail.com wrote:

> In our application we are using SQLAlchemy to retrieve data from our 
> Oracle database. However, we have a policy that any INSERTS to the Oracle 
> database are done via stored procedures. We have approximately 1000 rows 
> (where each row  consists of 3-4 simple scalars such as numbers or strings) 
> that we need to insert.
>
> Question 1 - is there a sophisticated approach of creating an array of 
> these 1000 items and submitting this collection to Oracle in one shot? If 
> so, are there any references on this?
>
> Question 2 - If we adopt a naive approach of opening a transaction, making 
> 1000 calls to this stored proc that does simple inserts, and then commiting 
> to database at the end of the 1000 calls to the stored proc, does this 
> represent a bottleneck on database/computer resources of some sort, such as:
> a - will multiple threads will be created for this?
> b - presuming the default of 20 pooled connections to the database 
> , how many 
> connections from this pool will be used for the 1000 calls to the 
> inserting-stored-procedure before we commit?
> c - Would synchronous calls of this stored procedure pose a serious 
> performance/system-overhead issue? 
>
>
>
>
>

-- 
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/d40accbf-efb2-464d-85f7-f8c75e9134can%40googlegroups.com.

[sqlalchemy] Re: Assert ResultProxy

[Steven James]

In case you want more details about the differences, you could also use 
difflib...

from difflib import SequenceMatcher

seq1 = [tuple(row.values()) for row in resultproxy1]
seq2 = [tuple(row.values()) for row in resultproxy2]

sm = SequenceMatcher(a=seq1, b=seq2, autojunk=False)
print(sm.get_opcodes())
print(f'similarity: {sm.ratio()}')

assert sm.ratio() == 1  # example to ensure results are equivalent
assert sm.ratio() == 1, sm.get_opcodes()  # pytest syntax to show the 
opcodes if the assertion fails

Steven James

On Friday, 29 November 2019 09:13:23 UTC-5, sumau wrote:
>
> Hello
>
> I think my original question was too generic so rephrasing... Is there a 
> way in sqlalchemy to:
>
>1. Assert a ResultProxy against an expected ResultProxy (or list of 
>RowProxies against expected list of RowProxies) 
>2. Show any differences
>
> I wanted to check first before writing my own script :-)
>
> Regards
> S
>
> On Friday, 22 November 2019 10:50:54 UTC, sumau wrote:
>>
>> Hello
>>
>> I would like to assert the contents of tables in my PG schema i.e. make 
>> sure it contains the data I'm expecting
>>
>> I am aware of various options:
>>
>> 1) Compare the actual and expected tables using a sql query, orchestrated 
>> by sqlalchemy (i.e. create the actual and expected tables in DB, run the 
>> sql comparison script, return the output)
>> 2) Load the actual tables as tuples and compare them with expected tuples 
>> using something like assert_result
>>
>> https://github.com/sqlalchemy/sqlalchemy/blob/d933ddd503a1ca0a7c562c51c503139c541e707e/lib/sqlalchemy/testing/assertions.py#L465
>> 3) Load the actual tables as dataframes and compare them with expected 
>> dataframes using pandas assert_frame_equal
>>
>> https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.testing.assert_frame_equal.html
>>
>> Any recommendations / thoughts would be much appreciated, both as to the 
>> approach and the implementation :-)
>>
>

-- 
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/ca72e6a2-1c81-4775-af26-af5d465d037b%40googlegroups.com.

Re: [sqlalchemy] 'selectin' loading with composite keys on DB2 for i

[Steven James]

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"]),
>> )
>>

[sqlalchemy] Track a "column FOO does not exist" bug.

[Steven James]

Do you have a trigger or a constraint that references that column?

-- 
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/ae2db410-910e-4ae1-96ea-6255b2fb5c73%40googlegroups.com.

Re: [sqlalchemy] 'selectin' loading with composite keys on DB2 for i

[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!

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.co

[sqlalchemy] Re: 'selectin' loading with composite keys on DB2 for i

[Steven James]

Sorry... made a typo there... the desired syntax is:
`SELECT * FROM table WHERE (table.a, table.b) IN (VALUES (?, ?), (?,?))`


On Thursday, 27 June 2019 14:11:16 UTC-4, 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.
>
> 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/f318eb7e-82dc-4429-80e9-390406706eee%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] '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

-- 
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.