How to Retrieve a MySQL Auto-Incremented Primary Key After INSERT Using Python?

Retrieve Primary Key Post-INSERT with Python in MySQL

When executing an INSERT INTO statement in a MySQL database using Python, it's often necessary to retrieve the primary key (typically auto-incremented) of the newly created record. Here's how you can accomplish this:

Method 1: Using cursor.lastrowid

After executing an insert statement, the cursor object holds a reference to the last inserted row. You can use the cursor.lastrowid attribute to retrieve the auto-generated primary key value:

import mysql.connector

connection = mysql.connector.connect(...)
cursor = connection.cursor()

height = 100
cursor.execute("INSERT INTO mytable(height) VALUES(%s)", (height,))
connection.commit()

new_id = cursor.lastrowid
cursor.close()
connection.close()

Method 2: Using connection.insert_id()

Some database connectors provide a insert_id() method on the connection object. This method returns the last auto-generated ID inserted on that particular connection:

import mysql.connector

connection = mysql.connector.connect(...)
cursor = connection.cursor()

height = 100
cursor.execute("INSERT INTO mytable(height) VALUES(%s)", (height,))
connection.commit()

new_id = connection.insert_id()
cursor.close()
connection.close()

The above is the detailed content of How to Retrieve a MySQL Auto-Incremented Primary Key After INSERT Using Python?. For more information, please follow other related articles on the PHP Chinese website!