[DB-SIG] comparing with lists via cx_Oracle

Harald Armin Massa haraldarminmassa at gmail.com
Tue Jan 9 21:03:19 CET 2007


Dieter,

> >cs.execute("select whatever from sometable where id in (%(pyeles)s)",
> >dict(pyeles=[1,2,3,4,5]))

> Does "cx_Oracle" support Python parameter style?

It does not, my fault. That's not the problem I am focussed on.

cx_Oracle supports a "Oracle-Style Parameter fitted to Python dicts", that is:

> >cs.execute("select whatever from sometable where id in (:pyeles)",
> >dict(pyeles=[1,2,3,4,5]))

> >cs.execute("select whatever from sometable where id in (%(pyeles)s)",
> >dict(pyeles=[1,2,3,4,5]))

"would" work.

> If the problem is not the parameter style, it may be the
> sequence --> string conversion. In this case, use can use
>          ",".join([1,2,3,4,5])
> instead of "[1,2,3,4,5]".

There MUST NOT be a conversion to a string.

It has to be a "sequence" or whatever that would be within Oracles SQL.

Just imagine not having numeric IDs, but Strings:

["isK9","kloN","kJni"]

as ID.

I went down that ", ".join(whatever) way; even to the ", ".join("'%s'"
% (a,) for a in sequence)

way (those many "' are single quotes quoted with double quotes), and it works.

BUT ... it forces me to construct the SQL query via Python String
functions, and to do all the necessary escaping of parameters in
Python. That is considered BAD, because then I have to deal with all
the escaping challenges - that's the entrence door of most PHP
vulnerabilities.

Parameters should be escaped via the database adapter. .... So I am
looking for the way to pass sequence parameters into Oracles throat :)

Best wishes,

Harald



-- 
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
Python: the only language with more web frameworks than keywords.


More information about the DB-SIG mailing list