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

The data is inserted into the table in Mysql using the INSERT statement. While inserting the data into the table, either the data must be provided in the same order as the column definition in the database or the column names must be provided along with the data while using INSERT statement.

To get and print the ID of the last inserted row, lastrowid is used. This is a special keyword which is used to get the ID of the last inserted row. There are certain prerequisites to be taken care of before using this method.

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

• The ID column must be auto-incremented.

Syntax

cs.lastrowid

Here, cs is the cursor object.

Steps to get id of the inserted row in a table using MySQL in python

• import MySQL connector

• establish connection with the connector using connect()

• create the cursor object using cursor() method

• create a query to INSERT a row into the table

• execute the SQL query using execute() method

• get the ID of the row inserted using the lastrowid.

• close the connection

Suppose we have the following table named “Students”.

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

Example

The ID column in the above column is the primary key and auto-incrementing. We will insert a new row into the table and get the id of the last inserted row.

import mysql.connector
db=mysql.connector.connect(host="your host", user="your username",password="your
password",database="database_name")

cursor=db.cursor()

#insert a new row into the table
query="INSERT INTO Students VALUES(5,“Priya”, “Amritsar”, 90)"
cursor.execute(query)
db.commit()

#print the id of the inserted row
print(cursor.lastrowid)

db.close()

Output

5