[DB-SIG] comparing with lists via cx_Oracle

Harald Armin Massa haraldarminmassa at gmail.com
Wed Jan 10 07:07:09 CET 2007


Marc-Andre,

> The standard SQL notation for value lists in SQL is
>
>         x in (value1, value2, ...)
>
> so this should work pretty much with every SQL database.

yes. If I know the number of values beforhand, even parameters work:

x in (:value1, :value2, :value3)

(for being ":" the parametersubstitution of choice for the database

> If you have numbers as sequence elements, the Python tuple
> representation works just fine.

I am going to test this, if something like

"x in (:mypytu)", dict(mypytu=(1,2,3))"

works ... thanks for the hint! I stopped testing when I failed with
set() and list() :)

>For strings, things are harder,  but then I wouldn't recommend using
IN predicates with >strings anyway - it's better to map the strings to
integers via an extra table (if possible).

Why is it better? If I get a set of strings from the application, I
have to find the fitting integers from the extra table. That again is
one in-call; or number-of-strings calls, which for performance reasons
esp,. on WAN is not nice :(

> Note that you can also write:
>         (x == value1) or (x == value2) ...

Thanks for the reminder!
> That way you avoid having to deal with escaping - you still
> have to construct the SQL depending on the number of values
> you have.
I think that is a good trade. "Constructing SQL" is a easier to test
then "correctly escaping every *** that somebody puts in" :)

> several databases have functions
> for doing substring-search. All you'd have to do is join all
> the members using e.g. ',' (provided none of the members
> has this character) and the do something like:
WOW! Thats a nice trick, thinking out of the box ... thanks!

> BTW, sequences in DB-talk are objects which you can use as
> auto-increment integer source, e.g. for creating ids of
> new rows.
Yes. Maybe that makes it hard to google up a solution for my challenge :)

Thanks for your great hints,

best wishes,

Harald

(cu @ EP2007 ? )

-- 
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