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
_______________________________________________ Tutor maillist - Tutor@python.org http://mail.python.org/mailman/listinfo/tutor
