[DB-SIG] client side sub queries

[Carl Karsten]

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