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