Delete data from table

Welcome to another tutorial on Python MySQL. Here you learn about Delete Table Data, where we will be using the Delete SQL query and Where clause.

To delete any record from the MySQL table, the DELETE SQL query.

Python MySQL DELETE: Syntax

Below is the syntax.

DELETE FROM table_name WHERE condition

Take note that the syntax WHERE clause typically specifies the condition to pinpoint a particular record that needs to be deleted. If the condition is not specified, then all the records of the table will be deleted.

Python MySQL DELETE Table Data: Example

We will delete the record in the student's table from the table created in our previous tutorial, whose rollno is 3. Check out the code below:

import mysql.connector as mysql

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

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

## executing the query
cursor.execute(query)

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

From the code above, it ran without an error then which means that the row with rollno=3 is deleted successfully.

Below is the output result.

import mysql.connector as mysql

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

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)

Output:

('Mike', 'Computer Science', 'London, 1)
('Ada Chang', 'Computer Science', 'Hong Kong', 2)