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 ! :::: _______________________________________________ DB-SIG maillist - DB-SIG@python.org http://mail.python.org/mailman/listinfo/db-sig
