[DB-SIG] how do I execute a query likte 'select * from mytable where id in (1, 2, 3)'?

[Carsten Haese]

On Fri, 28 Dec 2007 15:44:10 +0100, Joerg Beyer wrote
> Hello,
> 
> maybe this is obvious, but I don't know how to execute a query like:
> select * from mytable where id in (1,2,3)
> from python. The difficult part is the set in the where clause, the "in
> (a,b,c,d, ...)"

If the list is short enough that an IN query is feasible, you'll want to do
something like this:

sql = "select * from mytable where id in ("+",".join("?" for _ in mylist)+")"
cur.execute(sql, tuple(mylist))

(This assumes that your unspecified API module for your unspecified database
engine uses question marks as parameter markers. Adjust the parameter marker
to %s if necessary.)

If the list comes from another table, you should probably use a sub-query or
rewrite your query to use a join instead.

Hope this helps,

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