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

Python Server Side Programming Programming

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

Pawandeep Kaur

Published on 10-Jun-2021 13:16:41

Previous Page Print Page