Re: [MonetDB-users] python cursor missing lastrowid attribute?

26 Nov 2007

      On Nov 26, 2007 3:38 PM, Niels Nes  wrote:
...
On Mon, Nov 26, 2007 at 03:27:39PM -0700, m h wrote:
...
On Nov 26, 2007 3:15 PM, Niels Nes  wrote:
...
On Mon, Nov 26, 2007 at 03:09:38PM -0700, m h wrote:
...
In MonetSQLdb BaseCursor.insert_id, the docstring says to use the
lastrowid attribute.
When I try to access that lastrowid on the cursor instance I get an
AttributeError.
Should I file a bug for this?
I think we should remove this remark. The server only sends rows-affected
info on insert (and other update statements), ie its not easy to
implement this feature (and this is afcourse mostly guessing what the
python BaseCursor.insert_id/lastrowid is supposed to be do?).
Hmmm, most all the other python db cursors implement this.  Here is
Maybe I should have a look at those other cursors.
If the ids are changing often, it might be difficult....
...
...
the pep-249 info:
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.
I guess then we should set it to None. Problem is that we would like
to controll the rowid, ie for example when we insert the rowid may
not be fixed (could change).
Are these changed often?
...
...
The semantics of .lastrowid are undefined in case the last
        executed statement modified more than one row, e.g. when
        using INSERT with .executemany().
Warning Message: "DB-API extension cursor.lastrowid used"
I realize this is "optional" but it seems like along the way support
for this was either available or going to be available.
Currently its not and we went for a different solution for
sequence numbers.
Him, if there is a way to get these sequence numbers that might help
too.  Can it be done via SQL?
...
...
...
Niels
(you known we are looking for experts how are willing to fix our api's)
As db architecture guys we would like other to pickup the apis and improve
them ;-)
...
I don't consider myself to be a db expert ;(
But implementing the sqlalchemy backend is a good testcase for
monetdb.  (Plus it might attract people who are experts along the
way).  Many of the people in the python community had never heard
about Monetdb before I blogged about it a few days back....
Great that they learned MonetDB, maybe the 'cwi' also rings a bell
with them ? If not they may known that guido (mister python) once worked
at the cwi.
Seems like good things come out of cwi.... ;)

The SQLAlchemy requirements for lastrowid are thus:

inserting a row usually needs to return the newly generated primary key
the ORM needs this but its also a pretty standard user requirement
otherwise you dont have a generic way of fetching the row which you just created
DBAPIs are all entirely inconsistent on how this is done which is
usually because the databsase client libs themselves are inconsistent
oracle, PG, firebird all rely upon an explicit sequence being pre-executed
whereas sqlite and mysql have an autoincrementing behavior where you
can get a single-valued PK via cursor.lastrowid