[Tutor] Building an SQL query

[Greg Lindstrom]

> 
> Hmm, I dunno ADOpy but assume it somehow miraculously turns your data
> set into a dictionary of some sort? How it guesses which order the
> SELECT will return the fields is a mystery to me, but maybe it has
> knowledge of the Postgres hashing function or somesuch.

 Yeah. I used to do it by hand by looking at the description of the cursor 
object, which holds the name of each field as defined in the database with 
other stuff. You can then get the field value by getting the index of the 
field name, then hitting the row data. It's not a lot of fun, but I did it 
because I didn't want to depend on the order being returned in case we 
wanted to drop a field for some reason.
 > Here's what works for me (and a tip-o-the-hat to Bob Gailer for his
help)
>
> query = '''UPDATE my_table SET state = 'processed' WHERE id IN %s'''
%
> str(ids_to_process)
> query = query.replace('[', '(')

>Why not convert the list to a tuple before applying str():

>str(tuple(ids_to_process))
  Didn't think of it...thanks :-)

>Unless ADOpy is very slow I wouldn't expect a huge performance
>increase since it will only be the compile phase, but if you
>hit the query a lot then maybe 5-10%. You are more likely to
>see the benefit in a drop CPU loading on the server.
  Which is good, too.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mail.python.org/pipermail/tutor/attachments/20050602/dd93a1e9/attachment.html