mysql and postgresql backends get_last_insert_id() do extra queries

[anonymous]

PEP-249 specifies:

    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().

        Warning Message: "DB-API extension cursor.lastrowid used"

While this is an extension, it is supported by both MySQLdb and psycopg (including version 1). Therefore, it is only necessary in base.py to do this:

def get_last_insert_id(cursor, table_name, pk_name):
    return cursor.lastrowid

The sqlite3 backend already does this, in fact.

I've been testing the above change with MySQLdb for awhile and it works fine. I have not tried it with pyscopg1, but judging from the source code, it should work: ​http://initd.org/tracker/psycopg/browser/psycopg1/trunk/cursor.c (look for lastrowid defined on the cursor object).

[Andy Dustman <farcepest@…>]

That was me, BTW.

[Adrian Holovaty]

(In [2645]) Fixed #1590 -- Changed MySQL get_last_insert_id() implementation to use cursor.lastrowid instead of a separate SELECT statement. Thanks, Andy Dustman

[Adrian Holovaty]

Thanks for reporting this. I checked in the change for MySQL, but it looks like the Postgres cursor.lastrowid doesn't return the ID of the record, it returns some other strange number.

[Andy Dustman <farcepest@…>]

PostgreSQL might be returning the OID of the row in this case.

​http://www.postgresql.org/docs/8.0/static/datatype-oid.html