Add access to LAST_INSERT_ID()

[kqshan]

trafficstars

For tables with an AUTO_INCREMENT primary key, there doesn't seem to be a way to access the value of the auto-incremented ID that was generated when inserting a tuple.

For example, if we have an auto-increment table

@schema
class Auto(dj.Lookup):
    definition = """
    id  :int auto_increment
    ---
    name :varchar(12)
    """

and I insert a new tuple into this:

new_tuple = {'name':'foo'}
Auto.insert1(new_tuple)

then how do I get the auto-incremented ID of the tuple I just inserted? (assuming that the name column is not necessarily unique)

This is available in datajoint-matlab as the lastInsertID property of the Relvar class, although that is perhaps a bit misleading because it contains the latest auto-increment ID produced for any table in the current session. (this is just the behavior of the LAST_INSERT_ID() MySQL function that it calls).

So I think that it would be nice if the Connection class had a last_insert_id() method that simply returned the value of a SELECT LAST_INSERT_ID() query. Then at least I could do:

new_tuple = {'name':'foo'}
Auto.insert1(new_tuple)
new_tuple['id'] = Auto.schema.connection.last_insert_id()