Opened 8 years ago

Closed 8 years ago

Last modified 8 years ago

#1590 closed enhancement (fixed)

mysql and postgresql backends get_last_insert_id() do extra queries

Reported by: anonymous Owned by: adrian
Component: Core (Other) Version: magic-removal
Severity: minor Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:


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 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: (look for lastrowid defined on the cursor object).

Attachments (0)

Change History (4)

comment:1 Changed 8 years ago by Andy Dustman <farcepest@…>

  • Type changed from defect to enhancement

That was me, BTW.

comment:2 Changed 8 years ago by adrian

  • Resolution set to fixed
  • Status changed from new to closed

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

comment:3 Changed 8 years ago by adrian

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.

comment:4 Changed 8 years ago by Andy Dustman <farcepest@…>

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

Add Comment

Modify Ticket

Change Properties
<Author field>
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'

E-mail address and user name can be saved in the Preferences.

Note: See TracTickets for help on using tickets.