Getting last insert id from MySQL stored in Python

user_2672227 0
Viewed 464

I am using the MySQL connector in Python (3.9 on Windows) to call a stored procedure that does an insert, but when I try to get the last insert ID I get 0, any advice would be greatly appreciated.

What I am calling:

cursor.callproc(procedure_name, params_list)
print('last id:', cursor.lastrowid)

Stored procedure:

CREATE  PROCEDURE add_image_path()
BEGIN

INSERT into ImagePath values(0, "test");

END
mysql-connector stored-procedures python
1 Answers

cursor.lastrowid is set from mysql_insert_id() instead of LAST_INSERT_ID().

You can manually set it from LAST_INSERT_ID():

cursor.callproc(procedure_name, params_list)
cursor.execute('SELECT LAST_INSERT_ID()'))  # Add this
cursor.lastrowid = cursor.fetchone()[0]     # Add this
print('last id:', cursor.lastrowid)

From https://dev.mysql.com/doc/c-api/8.0/en/mysql-insert-id.html:

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.

The reason for the differences between LAST_INSERT_ID() and mysql_insert_id() is that LAST_INSERT_ID() is made easy to use in scripts while mysql_insert_id() tries to provide more exact information about what happens to the AUTO_INCREMENT column.

user_86017600
Related