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

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, ...)"

Any suggestions?


    thanks in advance
    Joerg
_______________________________________________
DB-SIG maillist  -  DB-SIGpython.org
http:/
/mail.python.org/mailman/listinfo/db-sig

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.s
ourceforge.net

_______________________________________________
DB-SIG maillist  -  DB-SIGpython.org
http:/
/mail.python.org/mailman/listinfo/db-sig

Joerg,

> 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, ...)"


WHICH database are you talking about?

With Oracle, you are on your own, that is, you have to do
stringmagic
to create the SQL.

With PostgreSQL and PSYCOPG2 you can use lists:

cs.execute("select * from s2e where element = any
(%(what)s)",
dict(what=[1,2,3]))

using PostgreSQL arrays and the "any" function.

Best wishes,

Harald


-- 
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
fx 01212-5-13695179
-
EuroPython 2008 will take place in Vilnius, Lithuania - Stay
tuned!
_______________________________________________
DB-SIG maillist  -  DB-SIGpython.org
http:/
/mail.python.org/mailman/listinfo/db-sig

Il giorno sab, 29/12/2007 alle 11.37 +0100, Harald Armin
Massa ha
scritto:
> Joerg,
> 
> > 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, ...)"
> 
> 
> WHICH database are you talking about?
> 
> With Oracle, you are on your own, that is, you have to
do stringmagic
> to create the SQL.
> 
> With PostgreSQL and PSYCOPG2 you can use lists:
> 
> cs.execute("select * from s2e where element = any
(%(what)s)",
> dict(what=[1,2,3]))
> 
> using PostgreSQL arrays and the "any"
function.

psycopg2 supports direct conversion of tuples to IN clauses,
doing
quoting on the arguments. Just do:

cs.execute("select * from s2e where element IN
%(what)s", 
	{'what': (1,2,3)})

federico

-- 
Federico Di Gregorio                         http://people.initd.org/f
og
Debian GNU/Linux Developer                               
fogdebian.org
INIT.D Developer                                          
foginitd.org
 If a process is potentially good, but 90%+ of the time
smart and
  well-intentioned people screw it up, then it's a bad
process.
                                                          --
Steve Yegge

_______________________________________________
DB-SIG maillist  -  DB-SIGpython.org
http:/
/mail.python.org/mailman/listinfo/db-sig

Harald Armin Massa schrieb:
> Joerg,
> 
>> 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, ...)"
> 
> 
> WHICH database are you talking about?

I am using MySQL.

> 
> With Oracle, you are on your own, that is, you have to
do stringmagic
> to create the SQL.
> 
> With PostgreSQL and PSYCOPG2 you can use lists:
> 
> cs.execute("select * from s2e where element = any
(%(what)s)",
> dict(what=[1,2,3]))
> 
> using PostgreSQL arrays and the "any"
function.

the MySQL function "in()" seems to be what
"any()" is for PostgreSQL, see
http://dev.mysql.com/doc/refman/5.0/en/func-op-su
mmary-ref.html
http://www.postgresql.org/docs/8.2/static/func
tions-aggregate.html

The missing trick was to use a python list as a an argument
to the
query. It is obvious now.

	Thanks for the hint
	Joerg
> Best wishes,
> 
> Harald
> 
> 


-- 
skype: j.beyer, jabber: j.beyerweb.de
gpg: 2CFB B5F2 988E 8897 06DB  2D50 F128 D2D6 F1B2 2099
_______________________________________________
DB-SIG maillist  -  DB-SIGpython.org
http:/
/mail.python.org/mailman/listinfo/db-sig