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.