[DB-SIG] comparing with lists via cx_Oracle
Harald Armin Massa
haraldarminmassa at gmail.com
Wed Jan 10 07:07:09 CET 2007
> 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,
(cu @ EP2007 ? )
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Python: the only language with more web frameworks than keywords.
More information about the DB-SIG