There is nothing special about SQLAlchemy's handling of many parameters
versus a few, and it's likely a limiation of the IN clause as implemented
on those backends. IN does not support arbitrarily large numbers of
parameters. On Oracle for example the limit is 1000. If you need to do
a large number of IN parameters, you need to batch your queries such that
the size of the IN clause is limited.
Jonathan Marshall wrote:
>
> I have an automatically generated query that occasionally generates a
> huge number of parameters (50,000+). Sometimes Sqlalchemy does not
> correctly serialise the parameters. If I have 50,000 parameters often
> one is incorrect, for 100,000 parameters usually 3 are incorrect. More
> parameters seems to correlate with a higher chance of a problem
> occurring.
>
> Code to reproduce:
>
> from sqlalchemy import create_engine, Column, Integer
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import sessionmaker
> from sqlalchemy.sql import text
>
> PARAMETERS = 100000
>
> Base = declarative_base()
> class Value(Base):
> __tablename__ = 'value'
>
> id = Column(Integer, primary_key=True)
> value = Column(Integer, unique=True)
>
> db_engine = create_engine('sqlite:///?', echo=True)
> Base.metadata.create_all(db_engine)
>
> Session = sessionmaker(db_engine)
> session = Session()
>
> print 'Seeding database with %d values' % PARAMETERS
> for i in range(PARAMETERS):
> session.add(Value(value=i))
> session.commit()
>
> params = {}
> in_clause = []
> for i in range(PARAMETERS):
> params['in%d' % i] = i
> in_clause.append(':in%d' % i)
>
> sql = 'select value from value where value in (%s)' % ', '.join
> (in_clause)
> print 'Querying'
> print sql
> results = session.execute(text(sql), params=params).fetchall()
>
> expected = set(range(PARAMETERS))
> got = set((row[0] for row in results))
>
> missing = expected - got
> if missing:
> print 'Missing values %s' % missing
> r = dict(((v, k) for k, v in params.iteritems()))
> for i in missing:
> print '%s was parameter %s' % (i, r[i])
>
>
> When run the output contains:
> Missing values set([97490, 28181, 29654])
> 97490 was parameter in97490
> 28181 was parameter in28181
> 29654 was parameter in29654
>
> Looking through the output I see that 97490 was in my query. The
> output from for Sqlalchemy for the query parameters however contains
> this:
> ..., 97488, 97489, 93435, 97491, 97492, ...
>
> My test machine is a MacPro running SQLAlchemy 0.5.4p2 on python 2.5.
> I have observed this issue with both sqlite and mysql backends
>
> Cheers,
> Jon.
> >
>
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
