[DB-SIG] client side sub queries

Carsten Haese carsten at uniqsys.com
Thu May 24 22:25:53 CEST 2007


On Thu, 2007-05-24 at 15:10 -0500, Carl Karsten wrote:
> > There is also Option 3: Use actual parameter passing to build a WHERE ... IN
> > (...) clause:
> > 
> > cSql = ("select ktbl2_fk from tbl3 where OtherKey IN ("
> >         +",".join("%s" for _ in cList)
> >         +")" )
> > cur.execute(cSql, cList)
> 
> Don't suppose you know off the top of your head how to code it for
> .paramstyle=named
> 
>  >>> print cx_Oracle.paramstyle
> named

This doesn't necessarily mean that 'named' is the only style it will
accept. Informix is the only commercial RDBMS I use--I'm odd that
way--so your mileage may vary, but I'd say there's a more than 50%
chance that cx_Oracle will silently accept qmark style:

cSql = ("select ktbl2_fk from tbl3 where OtherKey IN ("
        +",".join("?" for _ in cList)
        +")" )
cur.execute(cSql, cList)

If that doesn't work and you must use named style, you'll have to build
a dictionary with your values, and the list of placeholders will look
more involved. Something along these lines:

paramnames = [ ":p%d"%i for (i,_) in enumerate(cList) ]
paramdict = dict(zip(paramnames, cList))
cSql = ("select ktbl2_fk from tbl3 where OtherKey IN ("
        +",".join(paramnames)
        +")" )
cur.execute(cSql, paramdict)

HTH,

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




More information about the DB-SIG mailing list