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 sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
