Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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
8K+ Views
