[DB-SIG] UPDATE from SELECT?

[Steve Dewey]

I am writing a class to provide the following services:

  - execution of a db query
  - local storage of result set
  - navigation of the result set
  - field level data access by column name
  - storage of record modifications with incremental rollback
  - application of changes back to database, initially per record
    but later possibly in batch mode
  - insertion into result set with subsequent insertion into database
  - deletion from result set with subsequent deletion from database

Initially I am restricting the target database to MySQL. I am using
MySQLdb. I am also using the db_row module for data storage. I have
implemented the first five services on my list ( actually the rollback
is not yet implemented but the necessary data structure is there, I just
need to write the rollback function.)

I am now working on the sixth item, "application of changes", and I
realise I have some choices to make. To this point the only metadata my
class has required is the query and the name of the database to be
queried. I am retrieving the column names from the cursor.description
attribute. Now I need an UPDATE statement and it appears to me that it
must be built up. I have the column names and the column values, but the
table name is embedded in the string representing the query.

So my question is :

Should I attempt to parse the query string within my class for the table
name and any other tokens I need to build my UPDATE statement or should
I pass the individual elements in and build my SELECT and UPDATE
statements( and later on my INSERT and DELETE statements) inside my
class?

Is there any Python code out there that will derive an UPDATE or INSERT
from a SELECT?

If I pass the individual elements into my class (i.e. table name, where
clause, order by columns, ...) what are the necessary elements? How far
down do I have to break my syntax? Note that I am restricting my query
to those which are updatable (i.e. one table, no aggregate functions, no
DISTINCT, no GROUP BY or HAVING, ...)

Finally, am I reinventing the wheel? I'm certain this has been done
before, but I haven't found any open source Python code out there that
does what I'm trying to do. Bits and pieces yes (e.g. db_row), but not
the complete package.