How to Retrieve Auto-Generated Keys After INSERT in MySQL with Python?

Retrieving the Auto-Generated Key after an INSERT Statement in MySQL with Python

When performing an INSERT operation in a MySQL database using Python, it is often necessary to retrieve the primary key or auto-generated ID of the newly inserted row. This can be useful for referencing the inserted row in subsequent operations or for maintaining the uniqueness of the row.

Using cursor.lastrowid

The Python MySQL Connector provides a cursor object that, when initialized, can be used to execute SQL statements and retrieve resulting data. After an INSERT statement has been executed, the cursor.lastrowid attribute contains the primary key or auto-generated ID of the newly inserted row.

For example, consider the following code:

import mysql.connector

conn = mysql.connector.connect(...)
cursor = conn.cursor()
cursor.execute("INSERT INTO mytable(height) VALUES(%s)", (height))
inserted_id = cursor.lastrowid

In this code, the lastrowid attribute is retrieved and stored in the inserted_id variable. This ID represents the primary key of the newly inserted row.

Using connection.insert_id()

Alternatively, the connection object itself provides an insert_id attribute that returns the ID of the last row inserted through the connection. This attribute is available even after the cursor has been closed.

The following code demonstrates how to use connection.insert_id():

import mysql.connector

conn = mysql.connector.connect(...)
cursor = conn.cursor()
cursor.execute("INSERT INTO mytable(height) VALUES(%s)", (height))
inserted_id = conn.insert_id()

Regardless of the method used, the retrieved insert_id or lastrowid can be utilized to identify the newly inserted row in the database.

The above is the detailed content of How to Retrieve Auto-Generated Keys After INSERT in MySQL with Python?. For more information, please follow other related articles on the PHP Chinese website!