Carsten Haese wrote:
> On Wed, 2007-05-23 at 13:13 -0500, Carl Karsten wrote:
>> list = rows
>> cSql = ("select ktbl2_fk from tbl3 where ktbl1_fk IN ("
>> +",".join("%s" for _ in list)
>> +")" )
>> print cSql
>> cur.execute(cSql, list)
>
> Assuming that "rows" is the fetchall() result from your first query, try
>
> list = [x[0] for x in rows]
>
> instead of list=rows.
The goal was to reduce comprehensions. Ideally eliminate them.
Again, I know it can be done in the application layer. I did it, (OP has the
code.) I am just hoping a future db-api could deal with it.
Everything db-api does could be done custom. for some reason, db-api was
defined, code was written, and life is better.
>
>> _mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL
>> syntax; check the manual that corresponds to your MySQL server version for
>> the
>> right syntax to use near '),('2',),('3',))' at line 1")
>>
>> This is exactly the kind of stumbling I am trying to avoid.
>>
>> I would think that a list of items, or even a whole cursor should be able to
>> be
>> passed in just as elegantly as they are returned.
>>
>> It might even help the optimizers. this is a stretch: I am assuming these
>> are
>> not 'the same': "where x in (?,?)" and "...(?,?,?)" as where a single ?
>> that
>> represented a list of any size would use the same execution plan. (but I am
>> in
>> way over my head here, so feel free to just say no.)
>
> SQL has no notion of a single parameter representing a list of multiple
> values. Allowing this would lead to horrible coding practices. In
> general, <expr> IN (<expr>,<expr,...) queries should only be done for
> short lists that don't change much.
I agree with the 'in general' - but how times does the same exception need to
be
hand coded until it gets a lower level solution provided?
> If you have a long list, or one that
> changes a lot, store the list in a table and join it to your query.
That is outside the scope of this problem. or something.
Maybe this belongs in a similar category as ODBCs tables, columns and other
meta
data functions. it is code I would not expect to see in a normal app, but is
used enough to become one of the included batteries.
Carl K
_______________________________________________
DB-SIG maillist - DB-SIG@python.org
http://mail.python.org/mailman/listinfo/db-sig
