[DB-SIG] client side sub queries

[Carsten Haese]

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.

> _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. If you have a long list, or one that
changes a lot, store the list in a table and join it to your query.

HTH,

-- 
Carsten Haese
http://informixdb.sourceforge.net