Jonathan Marshall wrote:
>
> How is the sqlalchemy's outptut of parameters generated? It has the
> wrong value in it.
there is a loop on line 227 of sqlalchemy/engine/default.py.
> I find it interesting that 99,997 values are correct on a 100,000
> parameter query and 50,000 parameters works sometimes but not always.
> It implies that this is a bug somewhere, like a race condition,
there's no threads being spawned within SQLAlchemy, and if your app were
multithreaded, neither the connection or cursor object is shared outside
of the callstack of the statement execution. If you are sharing your
connection among threads yourself (which you shouldn't) and MySQLdb has an
issue with that, that is something different.
> Anyway I shall leave it here. It's taken me a lot longer to create a
> test case and investigate this issue than it took me to work around
> it!
your test case does not produce the results you illustrate on SQLite.
pysqlite does not accept that many bind parameters:
sqlalchemy.exc.OperationalError: (OperationalError) too many SQL variables
u'select value from value where value in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, < goes on and on with 100K
parameters >
here's a pure SQLite test illustrating the same thing:
import sqlite3
PARAMETERS = 100000
conn = sqlite3.connect(":memory:")
cursor = conn.cursor()
cursor.execute("CREATE TABLE value (id INTEGER PRIMARY KEY, value INTEGER)")
cursor.executemany("INSERT INTO value (value) values(?)", [[x] for x in
range(PARAMETERS)])
assert cursor.execute("SELECT count(1) FROM value").fetchone()[0] ==
PARAMETERS
sql = 'select value from value where value in (%s)' % ', '.join("?" for x
in range(PARAMETERS))
cursor.execute(sql, [x for x in range(PARAMETERS)])
Assuming you are having the "random values" failure only on MySQL and
assuming MySQL does in fact have no limit on the size of an IN clause then
the issue is very likely caused by the MySQLdb DBAPI since MySQLdb has
string manipulation logic that handles the rendering of bind parameters -
try creating a pure MySQLdb test case and posting on their bugtracker.
>
> Ta,
> Jon.
>
> On May 29, 4:34 pm, "Michael Bayer" <[email protected]> wrote:
>> 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.
>>
> >
>
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
