[egenix-users] how to insert new row, update in-memory object with same row from database?

[M.-A. Lemburg]

Michael Rothwell wrote:
> M.-A. Lemburg wrote:
> 
>> The best way to do this in a database independent way is to add
>> an ID column to all tables you are using (+ a timestamp column if
>> you want to edit them with MS Access).
>>
>> ODBC only allows you to query the table for "special" columns
>> which are best suited for e.g. identifying rows (see cursor.
>> specialcolumns()).
> 
> 
> 
> Thanks. I decided to see how ADO does this, because it seems to work 
> there. Upon investigation, ADO's RecordSet.AddNew() + .Update() does not 
> update the in-memory object if there is not an identity field in the 
> table, although the insert works. If there's a timestamp field (or an 
> identity field), then a second .Update() of the new record works. If 
> there's not, ADO botches the update and returns this error message:
> 
> "Key column information is insufficient or incorrect. Too many rows were 
> affected by update."
> 
> So it seems that ADO relies on "special columns" too.
> 
> It would be nice if a standard part of ODBC (and database APIs in 
> general) was a method to get at just-inserted rows. Inside a trigger, 
> one can just look at the "inserted" table (on SQL Server, anyway).

The DB-API lists a standard extension for this purpose:

Cursor Attribute .lastrowid

         This read-only attribute provides the rowid of the last
         modified row (most databases return a rowid only when a single
         INSERT operation is performed). If the operation does not set
         a rowid or if the database does not support rowids, this
         attribute should be set to None.

         The semantics of .lastrowid are undefined in case the last
         executed statement modified more than one row, e.g. when
         using INSERT with .executemany().

However, only few databases provide this information to the
application.

What I'd suggest is to use a database sequence generator to
create unique IDs and then use these during the INSERT. Most
database provide something along these lines or can simulate
such a generator using auto-increment columns.

That way you know the identity of the row *before* inserting
it -- usually much better if you plan to use contraints and
the like on the table.

-- 
Marc-Andre Lemburg
CEO eGenix.com Software GmbH
_______________________________________________________________________
eGenix.com -- Makers of the Python mx Extensions: mxDateTime,mxODBC,...
Python Consulting:                               http://www.egenix.com/
Python Software:                    http://www.egenix.com/files/python/