[Tutor] Building an SQL query
tubaranger at gmail.com
Thu Jun 2 23:05:53 CEST 2005
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.
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''' %
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,
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the Tutor