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?

By "built-in mysql connector", you mean the one that comes from the MySQL project ([MySQL Connector/Python](http://dev.mysql.com/doc/connector-python/en/))? That doesn't come built-in with standard Python, or most third-party Pythons, any more than the other two popular MySQL libraries do. — abarnert, Dec 02 '14 at 22:50
Okay, I clarified the question and added the definition for the `TOOLS` table. — Blairg23, Dec 03 '14 at 01:13
This was my problem too. But the source of the returned 0L after each INSERT or UPDATE was because the primary ID on my table was not auto-incrementable. It was a primary and unique ID but not sequential. So this -- http://stackoverflow.com/questions/2548493/how-do-i-get-the-id-after-insert-into-mysql-database-with-python -- was more relevant for fixing. — Marc Maxmeister, Dec 29 '15 at 16:34

abarnert · Answer 1 · 2014-12-02T23:02:02.530

9

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.

edited Dec 02 '14 at 23:02

answered Dec 02 '14 at 22:56

abarnert

354,177
51
601
671

Ah, okay. I was under the impression that `.lastrowid` made a call to the `LAST_INSERT_ID` to get the ID of the last insertion that was made. I definitely have the `AUTO_INCREMENT` column checked (I updated my question to reflect the definition of the `TOOLS` table). This is super good information. Thanks! However, how do I get this `lastrowid` in the case of calling a stored procedure? Do I just need to make a call to `SELECT LAST_INSERT_ID` within my stored procedure? – Blairg23 Dec 03 '14 at 01:16
@Blairg23: IIRC, that isn't sufficient; that just gets the value within the stored procedure. Read the help; it explains that you have to actually call `LAST_INSERT_ID(expr)`, with an argument, to get a value. So I think `SELECT LAST_INSERT_ID(LAST_INSERT_ID())` will work, as silly as it seems. But the usual solution for cases like this is to use a stored _function_ rather than a stored procedure, and just return the `LAST_INSERTED_ID` value that way. – abarnert Dec 03 '14 at 01:24
From MySQL, I can just call `SELECT LAST_INSERT_ID()` and it works perfectly. I'm not sure what they want for `expr`, but perhaps they're talking about a specific table or something. – Blairg23 Dec 03 '14 at 01:45
I was just hoping there was a way to do it outside of MySQL, from the cursor object. – Blairg23 Dec 03 '14 at 01:47
@Blairg23: It seems like you're just stabbing in the dark here to avoid reading a couple pages of docs. When you call `LAST_INSERT_ID` with an expression, it _sets_ the last insert ID to the value of that expression. Which forces `mysql_insert_id` to be updated as well. Unlike inserting a row with an `AUTO_INCREMENT` column, which only forces `mysql_insert_id` to be updated if it's done in a direct `INSERT` statement (as opposed to a stored procedure call). – abarnert Dec 03 '14 at 01:55
No, I've read the docs. I read them before I posted my question. I have used `SELECT LAST_INSERT_ID()` within MySQL and it worked as expected. What I was saying is that I was under the assumption that `lastrowid` was the Pythonic implementation of that SQL statement. – Blairg23 Dec 03 '14 at 02:52
Note: in the docs it says this `For example, after inserting a row that generates an AUTO_INCREMENT value, you can get the value like this: mysql> SELECT LAST_INSERT_ID(); -> 195 The currently executing statement does not affect the value of LAST_INSERT_ID(). ` – Blairg23 Dec 03 '14 at 02:53
An interesting note is that http://stackoverflow.com/questions/21163686/python-mysqldb-cursor-lastrowid-returns-0 was having the same issues with cursor (although he's using the `mysqldb` Python module), even though he's executing an `INSERT` SQL statement with the cursor. This was actually the question that spawned my question, because no one answered his (and because I'm using `SQL Connector/Python`). – Blairg23 Dec 03 '14 at 02:58
Sorry to add another comment, but I also was wondering why someone would ever use the `.lastrowid` cursor command if you only ever use `.callproc()` or `.execute` from cursor? Even if you are executing an SQL statement, it doesn't return the correct `.lastrowid` (I've tested this), so why bother using it at all? – Blairg23 Dec 03 '14 at 03:04
And yet again, I am finding that I was correct to begin with. This is the doc on `.lastrowid`: `The lastrowid property is like the mysql_insert_id() C API function; `, which is precisely what I said to begin with. – Blairg23 Dec 03 '14 at 19:17
@Blairg23: No, it's not. You originally said that `.lastrowid` is the same as `LAST_INSERT_ID()`. You now say that `.lastrowid` is the same as `mysql_insert_id()`, which is true, but it does _not_ mean it's the same as `LAST_INSERT_ID()`, because `mysql_insert_id()` is not the same as `LAST_INSERT_ID()`. I already explained all of that. – abarnert Dec 05 '14 at 18:42
I misunderstood, sorry. – Blairg23 Dec 09 '14 at 19:58

score 2 · Answer 2 · answered Dec 03 '14 at 19:29

@abarnert was correct in saying that the reason .lastrowid is not working as expected is that I am not making a call to INSERT or UPDATE, but rather CALL. Therefore, the only way to get around this problem is to change my stored procedure as follows:

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
    )
SELECT LAST_INSERT_ID();
END

Now, when you call that stored procedure, you should expect it to return the ID of the row that was inserted in the same way you would get a variable back from any call to a stored procedure that calls a SELECT statement.

In this example:

args = ('name', 'desc')
cursor.callproc('sp_tools_insert', args)
for result in cursor.stored_results():
    print result.fetchall() # Or result.fetchone() for just the first result

I have still not figured out why .lastrowid even exists since it never works with a .callproc() or .execute() statement, however. If anyone has any insight into this problem, please let me know!

First, this is one of the two solutions I suggested to you: either have a stored function that returns the `LAST_INSERT_ID`, or explicitly set the `LAST_INSERT_ID` in your stored procedure (by calling the one-argument version instead of the zero-argument version). — abarnert, Dec 05 '14 at 18:45
Second, the question you linked to as proof that `.lastrowid` never works in a `.execute` statement says the exact opposite: "for every user object, `return cursor.lastrowid` returns the actual id…". It doesn't work for his _other_ table for some reason which nobody could diagnose because he didn't respond to comments asking for clarification, but in general it does work, which is why thousands of apps that use it all work. — abarnert, Dec 05 '14 at 18:46

Python mysql.connector cursor.lastrowid always returns 0

2 Answers2