[Tutor] Building an SQL query

[Greg Lindstrom]

On 6/2/05, Alan G <alan.gauld at freenet.co.uk> wrote:
> 
> 
> Its a really bad idea to use SELECT * FROM in production code.
> There are two main reasons:
> 1) If the database structure changes your code is likely to break since 
> 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 
> changing. At the very least there will be an extra item in your tuple 
> ofvalues returned whichj may well break your code.



Alan, 

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. Doing it this way 
allows us to modify the query and/or the layout in the database without 
touching the code that does all of the heavy lifting. Using this strategy, 
we are able to perform all of our extractions to customer specific layouts 
with one rather small Python routine (this was the topic I spoke on at PyCon 
2005) and with the web pages we've created our non-technical personnel can 
create/modify customer layouts. It started off as a project to do our daily 
data pulls but has grown to handle virtually every report we generate.


2) Using * also prevents the database from precompiling your query and 
> caching it, thus you will slow down the processing by forcing a SQL compile 
> step each time. (This is true on Oracle, DB2 and Interbase, don't know about 
> Postgres but assume it is similar)


You are correct and in the future this may be a problem but currently we 
will accept the time penalty to gain the flexibility described above.

> query = '''
> > UPDATE my_table
> > SET state = 'processed'
> > WHERE id IN ids_to_process
> > '''
> What have you tried? What happened? It should just be a case
> of using variable interpolation as you did for the Select.


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('[', '(')
query = query.replace(']', ')')
results = adocursor.Execute(query)

Notice that I have to replace [ with ( and ] with ). A small bother but the 
results are allowing me to perform much more complex queries out of the 
database. I'm always open to ways to improve this (in particular I would 
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...I think I'll try it to see what sort of increase we get 
because we plan on growing our business.

Thanks for responding,
--greg
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mail.python.org/pipermail/tutor/attachments/20050602/96985a5f/attachment.htm