[mod_python] SQL question

Mike Looijmans nlv11281 at natlab.research.philips.com
Thu Feb 2 03:30:27 EST 2006

> The script assumes that the first field of each table is a primary key, 
> and this is necessary for performing updates on the table.
> In those cases where the tables don't have a primary key, or when the 
> primary key is composed of two fields, the update cannot be performed 
> correctly.

Another solution is to use an update-where-all scheme (like Delphi uses 
for some table queries). This also helps detect race conditions (other 
people editing the same records) as a side effect. (unrelated to your 
question: I typically use the update-where-changed system so that 
concurrent edits are possible as long as they do not touch the same 
entries. It also gives you the opportunity to present the changes 
someone else made, and have the user decide on how to merge them.)

So if you have a row where "SELECT * FROM customer" returns (51, "Mike", 
"MiLo Software"), and the user edits "Mike" into "Graham", the query to 
be run in update-where-all mode is:

c.execute("UPDATE customer SET owner=%s WHERE customer_id=%s AND 
owner=%s AND name=%s", ("Graham", 51, "Mike", "MiLo Software"))

This will work regardless of the DBMS used, and whether the table has a 
primary key at all (rows should still be unique though...)


More information about the Mod_python mailing list