Geek Answers Handbook

Python mysql.connector cursor.lastrowid always returns 0

I am using the mysql.connector module for Python 2.7. I broke my code to the simplest script, but I still have this problem. The problem is that when I try to get the identifier of the last row (which would be LAST_INSERT_IDin MySQL), I get a return of 0, regardless of how many rows were inserted. Anyone have a solution to 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 is 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
    ); 
END

This is how my table is defined TOOLS:

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 checked that the stored procedure is working correctly and the call is .callproc()working as expected. The only thing that doesn't work is the challenge .lastrowid. Any suggestions?

+4
2

lastrowid:

... , AUTO_INCREMENT INSERT UPDATE...

INSERT UPDATE, CALL.

, API C API mysql_insert_id, lastrowid. :

mysql_insert_id() 0 CALL , AUTO_INCREMENT, mysql_insert_id() CALL, . LAST_INSERT_ID() SQL, AUTO_INCREMENT.

, TOOLS AUTO_INCREMENT; .

, , , , lastrowid LAST_INSERT_ID() . LAST_INSERT_ID.

+7

@abarnert , , .lastrowid , , INSERT UPDATE, CALL. - :

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

, , , , ​​ , , SELECT.

:

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

, .lastrowid , .callproc() .execute(). - , , !

+1

More articles:


All Articles