How can you delete a record from a table using MySQL in Python?

We may at times need to delete certain rows from a table. Suppose, we have a table of details of students in the class. It is possible that one of the students left the class and hence, we do not require the details of that particular student. Hence, we need to delete that particular row or record from the table.

The “DELETE FROM” statement in MySQL is used to delete a row or record from the table, The “WHERE” clause is used to specify the row to be deleted. If WHERE clause is not used, then all the records will be deleted.

Syntax

Delete all the rows −

DELETE FROM table_name

Delete a specific row −

DELETE FROM table_name WHERE condition

Steps invloved to delete records from 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 using the appropriate mysql statements

• execute the SQL query using execute() method

• commit the changes made using the commit() method

• close the connection

Suppose we have a table named “Student” as follows −

+----------+---------+-----------+------------+
|    Name  | Class   |    City   |    Marks   |
+----------+---------+-----------+------------+
|    Karan |    4    | Amritsar  |    95      |
|    Sahil |    6    | Amritsar  |    93      |
|    Kriti |    3    | Batala    |    88      |
|   Khushi |    9    | Delhi     |    90      |
|    Kirat |    5    | Delhi     |    85      |
+----------+---------+-----------+------------+

Example

Suppose, we have the above table of students and we want to delete the record of Kriti from the above table.

import mysql.connector

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

cursor=db.cursor()

query="DELETE FROM Students WHERE Name='Kriti'"
cursor.execute(query)
db.commit()
query="SELECT * FROM Students"
cursor.execute(query)
for row in cursor:
   print(row)
db.close()

The above code deletes a row from the table and prints the remaining rowsof the table.

Output

(‘Karan’, 4 ,’Amritsar’ , 95)
(‘Sahil’ , 6, ‘Amritsar’ ,93)
(‘Amit’ , 9, ‘Delhi’ , 90)
(‘Priya’ , 5, ‘Delhi’ ,85)

NOTE

The db.commit() in the above code is important. It is used to commit the changes made to the table. Without using commit(), no changes will be made in the table.

Pawandeep Kaur

Published on 10-Jun-2021 12:22:41

• Related Questions & Answers
• How can you delete a table from a database in MySQL Python?
• Delete a specific record from a MySQL table by using AND in WHERE clause
• How can you test if some record exists or not in a MySQL table using Python?
• How to delete last record (on condition) from a table in MySQL?
• How can we delete a single row from a MySQL table?
• How can we delete multiple rows from a MySQL table?
• How can we delete all rows from a MySQL table?
• How can you update certain values in a table in MySQL using Python?
• How can you select data from a table based on some criteria using MySQL in Python?
• How to delete a column from a table in MySQL?
• Delete multiple entries from a MySQL table
• Can we select second largest record from a table without using LIMIT clause in MySQL?
• How can I create a stored procedure to delete values from a MySQL table?
• Delete all records from a table in MySQL?
• How can we search a record from MySQL table having a date as a value in it?