I doubt theres any performance difference if the DBAPI does the right
thing with a prepared statement. also, IN() wont work too well if
the rows are targeted by more than just one column; plus it generally
limits to 1000 elements and presents a larger and non-consistent
string to the DB which has to re-parse it each time.
On Mar 28, 2007, at 4:44 PM, Rick Morrison wrote:
> Working now...
>
> Anyway, wouldn't this operation be a lot more efficient using IN()
> instead of executemany()? Is detecting that too hard?
>
> Rick
>
>
> On 3/28/07, Michael Bayer <[EMAIL PROTECTED]> 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:[EMAIL PROTECTED]/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 ()
>
> >
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
