[sqlalchemy] Re: UOW relation delete bug - Rick Morrison - com.googlegroups.sqlalchemy

9 messages in com.googlegroups.sqlalchemy[sqlalchemy] Re: UOW relation delete bug

From	Sent On	Attachments
Rick Morrison	27 Mar 2007 22:36	.py
Michael Bayer	28 Mar 2007 00:31
Rick Morrison	28 Mar 2007 10:15
Michael Bayer	28 Mar 2007 10:21
Rick Morrison	28 Mar 2007 12:16
Rick Morrison	28 Mar 2007 13:43
Michael Bayer	28 Mar 2007 13:45
Michael Bayer	28 Mar 2007 14:02
Rick Morrison	28 Mar 2007 15:34

Subject:	[sqlalchemy] Re: UOW relation delete bug
From:	Rick Morrison (rick...@gmail.com)
Date:	03/28/2007 10:15:00 AM
List:	com.googlegroups.sqlalchemy

Hey Mike, this looks to be related to the parameters-as-ClauseParameters instead of Python dict() on a different thread.

I'm going to need some help or advice beating the MSSQL module into shape with the new convention. Where does the positional / non-positional specification go? I don't see it in any of the DB modules.

Looks like pymssql wants positional -style parameters, anyone know about adbodbapi / pyodbc?.

Rick

On 3/28/07, Michael Bayer <mike...@zzzcomputing.com> wrote:

it works for me, the bind params are in sqlite:

[[1], [3], [5]]

in postgres:

[{'id': 1}, {'id': 3}, {'id': 5}]

in both cases, thats a list of three sets of bind params, one positional and the other named, which correspond to executemany(). if this condition doesnt work with MS-SQL, MS-SQL should get added to test/engine/execute.py, which test the various scenarios of *multiparams and **params that you can send to execute(). just pick the paramstyle that works with MS-SQL and add it to the "supported" list for that test.

On Mar 28, 2007, at 1:36 AM, Rick Morrison wrote:

Surprised no one has hit this one yet.

When issuing a series of deletes in a UOW, SA issues the bogus delete statement

DELETE child where id = [1,2,3]

instead of using IN()

Test case attached. Seems to work in Sqlite even while issuing the bogus SQL (which is probably why a unit test didn't pick it up), but MS-SQL doesn't like it; didn't check PG or others.

from sqlalchemy import * import logging

logging.basicConfig() logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)

#metadata = BoundMetaData('mssql://d:d@test-db/drvtest') metadata = BoundMetaData('sqlite:///:memory:')

entity = Table('entity', metadata, Column('id', INT, primary_key=True, nullable=False), Column('typ', VARCHAR(12)), Column('lname', VARCHAR(128)) )

entityattr = Table('entityattr', metadata, Column('id', INT, primary_key=True, nullable=False), Column('ident', INT, ForeignKey('entity.id'), nullable=False), Column('typ', VARCHAR(12), nullable=False), Column('val', VARCHAR(128)) )

metadata.create_all()

class O(object): def __init__(self,**kw): for k,v in kw.items(): setattr(self,k,v)

class Ent(O): pass class Entattr(O): pass

mapper(Ent, entity, properties = {'props':relation(Entattr, cascade="all, delete-orphan")}) mapper(Entattr, entityattr)

S = create_session()

S.save(Ent(typ='A',lname='A', props = [Entattr(typ='A1', val='1'), Entattr(typ='A2', val='2'), Entattr(typ='A3', val='3'), Entattr(typ='A4', val='4'), Entattr(typ='A5', val='5'), Entattr(typ='A6', val='6') ])) S.flush() S.clear()

e = S.query(Ent).options(eagerload('props')).select()[0]

# remove some attributes e.props = [p for p in e.props if 0 == int(p.val) % 2]

# put some back e.props.append(Entattr(typ='A6', val='6')) e.props.append(Entattr(typ='A7', val='7')) e.props.append(Entattr(typ='A8', val='8'))

S.flush() # <-- Delete issued here S.clear()

e = S.query(Ent).options(eagerload('props')).select()[0] assert 6 == len(e.props)

metadata.drop_all()