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
_______________________________________________
DB-SIG maillist - DB-SIG@python.org
http://mail.python.org/mailman/listinfo/db-sig
