[DB-SIG] comparing with lists via cx_Oracle

[M.-A. Lemburg]

On 2007-01-10 07:07, Harald Armin Massa wrote:
> 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() :)

No that doesn't work. I was hinting at using the representation
to simplify formatting the SQL, e.g.

'select y from z where x in %r' % tuple_of_integers

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

String lookup is slow. Integer lookup is fast. This approach only
works out if you know the set of strings you want to test for in
advance.

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

Hopefully. I'd love to visit Vilnius which is supposed to be a very
nice city.

Cheers,
-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Jan 10 2007)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::