Get last row id from runQuery

Question

I am inserting with twisted runQuery into mysql. How to i get the last inserted row id from the transaction? Actually i do another sql query after insert, but this isnt really elegant.

according to (Twisted adbapi: number affected rows and last insert id), there should be a lastrowid property but this seems not to be true.

@inlineCallbacks
def get_or_create_node_id(self, node_name):
    try:
        record_exists = yield self.db.runQuery("SELECT COUNT(*) FROM nodes WHERE name = %s", (node_name))
    except ConnectionError:
        log.error("db interaction failed due to connection error")
    if record_exists[0]['COUNT(*)'] == 0:
        log.info("creating new nodes entry {node_name}", node_name=node_name)
        try:
            call_insert = yield self.db.runQuery("INSERT INTO nodes (name) VALUES (%s)", (node_name))
            node_id = yield self.db.runQuery("SELECT id FROM nodes WHERE name = %s", (node_name))
            returnValue(int(node_id[0]['id']))
        except ConnectionError:
            log.error("db interaction failed due to connection error")
    else:
        try:
            node_id = yield self.db.runQuery("SELECT id FROM nodes WHERE name = %s", (node_name))
            returnValue(int(node_id[0]['id']))
        except ConnectionError:
            log.error("db interaction failed due to connection error")

Alok Singhal · Answer 1 · 2021-10-01T17:54:16.657

You can use LAST_INSERT_ID(). Details are described in MySQL documentation.

With no argument, LAST_INSERT_ID() returns a BIGINT UNSIGNED (64-bit) value representing the first automatically generated value successfully inserted for an AUTO_INCREMENT column as a result of the most recently executed INSERT statement. The value of LAST_INSERT_ID() remains unchanged if no rows are successfully inserted.

Edit: you should also be able to use runInteraction() and access lastRowId():

node_id = yield self.db.runInteraction(get_node_id, node_name)
...


def get_node_id(cursor, name):
    cursor.execute("INSERT INTO nodes (name) VALUES (%s)", name)
    return cursor.lastRowId()

Get last row id from runQuery

1 Answers1