Python MySQL - Delete Table Data

In this tutorial, we will learn how to DELETE MySQL table data in Python where we will be using the DELETE SQL query and the WHERE clause.

The DELETE SQL query is used to delete any record from MySQL table.

Python MySQL DELETE: Syntax

The syntax for the same is given below:

DELETE FROM table_name WHERE condition

Note: In the above syntax, WHERE clause is used to specify the condition to pin point a particular record that you want to delete. If you don't specify the condition, then all of the records of the table will be deleted.

Python MySQL DELETE Table Data: Example

Let us see an example to delete the record in the students table (from the Python MySQL create table tutorial) whose rollno is 4. The code is given below:

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost",
    user = "yoursername",
    passwd = "yourpassword",
    database = "studytonight"
)

cursor = db.cursor()
## defining the Query
query = "DELETE FROM students WHERE Rollno = 4"

## executing the query
cursor.execute(query)

## final step to tell the database that we have changed the table data
db.commit()

If the above code runs without an error then it means that the row with rollno = 4 is deleted successfully.

To check if it exists in the table or not you can use the code given below:

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost",
    user = "yourusername",
    passwd = "yourpassword",
    database = "studytonight"
)

cursor = db.cursor()
## defining the Query
query = "SELECT * FROM students"

## getting records from the table
cursor.execute(query)

## fetching all records from the 'cursor' object
records = cursor.fetchall()

## Showing the data
for record in records:
    print(record)

The output for the above code is given below:

('Ramesh', 'CSE', '149 Indirapuram', 1) ('Peter', 'ME', 'Noida', 2) ('Amy', 'CE', 'New Delhi', 3)

Here is a snapshot of the actual output:

As we can see in the output above, the row with rollno=4 is successfully deleted.