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

Pawandeep Kaur

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

• Related Questions & Answers
• How do I get the id after INSERT into MySQL database in Python?
• How to insert DECIMAL into MySQL database?
• How to get username using ID from another table in MySQL database?
• How to insert data into a MySQL database with Java?
• How do we insert/store a file into MySQL database using JDBC?
• Get the new record key ID from MySQL insert query?
• How to insert/store JSON array into a database using JDBC?
• Java application to insert null value into a MySQL database?
• How to store and retrieve a date into MySQL database using Python?
• How to insert a Python tuple in a MySQL database?
• Insert current date to the database in MySQL?
• How to insert an image in to MySQL database using Java program?
• How to insert a record into a table in a database using JDBC API?
• How to insert into two tables using a single MySQL query?
• How to insert DATE into a MySQL column value using Java?