[Tutor] Building an SQL query

[Alan G]

> > SELECT * does not normally guarantee anything about the order of
fields
> > returned, so if the table gets an extra field added you might find
the order
>
> I'm using SELECT * specifically for this reason! I have the query
and
> customer specific data layouts stored in a database and am using
ADOpy
> to associate the field names to locations in a data segment.

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.

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


> like to avoid recompiling the query every time this is hit).
> It just hit me that we could store the field names to select
> in the query right along with everything else...

That's what we usually do, so that if we do need to change the data
retrieved for multiple queries we only change it in one place, but
still keep the predictability of defined foield names.

> I think I'll try it to see what sort of increase we get
> because we plan on growing our business.

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.

Alan G.