Python mysql.connector cursor.lastrowid always returns 0

Question

I'm using the mysql.connector module for Python 2.7. I have broken my code down to the simplest possible script, but I am still having this problem. The problem is that when I try to get the last row id (which would be LAST_INSERT_ID in MySQL), I get a return of 0, regardless of how many rows have been inserted. Does anyone have the solution for this problem?

My code is as follows:

import mysql.connector
default_config = {
    'user': 'root',
    'password': 'password',
    'host': '127.0.0.1',
    'database': 'test',
    'raise_on_warnings': True,
    'autocommit': True
    }
connection = mysql.connector.connect(**default_config)
cursor = connection.cursor()
args = ('name', 'desc')
cursor.callproc('sp_tools_insert', args)
lastid = cursor.lastrowid
print lastid # This returns 0 every time, regardless of number of inserts

My stored procedure looks like this:

CREATE PROCEDURE `sp_tools_insert`
    (
        IN p_name VARCHAR(45), 
        IN p_description VARCHAR(255)
    )
BEGIN 
INSERT INTO TOOLS
    (
        tool_name, 
        description                   
    )
VALUES 
    ( 
        p_name, 
        p_description
    ); 
END

This is how my TOOLS table is defined:

DROP TABLE IF EXISTS `test`.`TOOLS` ;

CREATE TABLE IF NOT EXISTS `test`.`TOOLS` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tool_name` VARCHAR(45) NOT NULL,
  `description` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

I have verified that the stored procedure is working correctly and the .callproc() call works as expected. The only thing not working is the .lastrowid call. Any suggestions?

Answer 1

The lastrowid property:

… returns the value generated for an AUTO_INCREMENT column by the previous INSERT or UPDATE statement …

And you haven't run an INSERT or UPDATE statement, you've run a CALL statement.

To get the details, you have to follow the links to the C API docs for the mysql_insert_id function that lastrowid calls. In particular:

mysql_insert_id() returns 0 following a CALL statement for a stored procedure that generates an AUTO_INCREMENT value because in this case mysql_insert_id() applies to CALL and not the statement within the procedure. Within the procedure, you can use LAST_INSERT_ID() at the SQL level to obtain the AUTO_INCREMENT value.

I'm assuming here that your TOOLS table actually does have an AUTO_INCREMENT column; otherwise, there is no value for you to get in the first place.

Also, note that this is just one of the ways in which, contrary to your assertion, lastrowid and LAST_INSERT_ID() are not the same thing. It's worth reading the docs for LAST_INSERT_ID as well.