How to get the id after INSERT into MySQL database using Python?

When inserting data into a MySQL database using Python, you often need to retrieve the ID of the newly inserted record. This is particularly useful for linking records across multiple tables or for tracking purposes.

The lastrowid attribute of the cursor object returns the auto-generated ID of the last INSERT operation. This works only when certain conditions are met ?

Prerequisites

• The ID column must be the primary key in the table

• The ID column must be auto-incremented

• The table must use a storage engine that supports auto-increment (like InnoDB or MyISAM)

Syntax

cursor.lastrowid

Here, cursor is the cursor object created from your database connection.

Setting Up the Example

Let's assume we have a Students table with the following structure ?

+--------+---------+-----------+------------+
|   id   |  Name   |    City   |    Marks   |
+--------+---------+-----------+------------+
|   1    |  Karan  | Amritsar  |     95     |
|   2    |  Sahil  | Amritsar  |     93     |
|   3    |  Kriti  |  Batala   |     88     |
|   4    |  Amit   |  Delhi    |     90     |
+--------+---------+-----------+------------+

Example: Getting ID After Insert

Here's how to insert a new record and retrieve its auto-generated ID ?

import mysql.connector

# Establish database connection
db = mysql.connector.connect(
    host="localhost",
    user="your_username", 
    password="your_password",
    database="your_database"
)

cursor = db.cursor()

# Insert a new row into the table
query = "INSERT INTO Students (Name, City, Marks) VALUES (%s, %s, %s)"
values = ("Priya", "Amritsar", 90)

cursor.execute(query, values)
db.commit()

# Get and print the ID of the inserted row
new_id = cursor.lastrowid
print(f"New record inserted with ID: {new_id}")

# Close the connection
cursor.close()
db.close()

New record inserted with ID: 5

Using Parameterized Queries

It's recommended to use parameterized queries to prevent SQL injection attacks ?

import mysql.connector

try:
    # Database connection
    db = mysql.connector.connect(
        host="localhost",
        user="your_username",
        password="your_password", 
        database="your_database"
    )
    
    cursor = db.cursor()
    
    # Insert multiple records and get their IDs
    students_data = [
        ("John", "Mumbai", 85),
        ("Sarah", "Pune", 92)
    ]
    
    query = "INSERT INTO Students (Name, City, Marks) VALUES (%s, %s, %s)"
    
    for student in students_data:
        cursor.execute(query, student)
        print(f"Inserted student with ID: {cursor.lastrowid}")
    
    db.commit()
    
except mysql.connector.Error as err:
    print(f"Error: {err}")
    db.rollback()
    
finally:
    if db.is_connected():
        cursor.close()
        db.close()

Inserted student with ID: 6
Inserted student with ID: 7

Key Points

• lastrowid returns 0 if the last statement didn't insert any rows or if the table doesn't have an auto-increment column

• Always call commit() before accessing lastrowid

• Use parameterized queries with placeholders (%s) for security

• Handle exceptions properly to avoid connection leaks

Conclusion

The lastrowid attribute provides an easy way to retrieve the auto-generated ID after inserting records into MySQL. Always ensure proper error handling and use parameterized queries for secure database operations.