Python MySQL: Deleting Records with DELETE FROM

Deleting records is a common task in database management, whether you’re cleaning up outdated information or removing specific entries. In this tutorial, we’ll guide you through using Python to execute the DELETE FROM statement in MySQL.

At The Coding College, our tutorials are designed to empower you with hands-on skills for your coding journey.

Prerequisites

Before proceeding, ensure the following:

  • Python Installed: Download the latest version from python.org.
  • MySQL Server Installed: Get it from MySQL official site.
  • MySQL Connector for Python: Install it using pip: 
pip install mysql-connector-python

How to Use DELETE FROM in Python MySQL

The DELETE FROM statement allows you to remove records from a table based on specified conditions.

Step 1: Connect to the Database

Start by connecting to your MySQL database:

import mysql.connector

# Connect to MySQL database
db = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database"
)

print("Connected to the database!")

Replace your_username, your_password, and your_database with your MySQL credentials.

Step 2: Delete Specific Records

To delete specific records, use the WHERE clause with the DELETE FROM statement. For example, to delete a user with the ID of 5:

cursor = db.cursor()

sql = "DELETE FROM users WHERE id = %s"
val = (5,)
cursor.execute(sql, val)

db.commit()

print(cursor.rowcount, "record(s) deleted.")

Step 3: Delete All Records

If you want to delete all records from a table, omit the WHERE clause. For example:

sql = "DELETE FROM users"
cursor.execute(sql)

db.commit()

print(cursor.rowcount, "record(s) deleted.")

Step 4: Confirm Before Deleting

To prevent accidental deletions, confirm the action before executing the query:

confirm = input("Are you sure you want to delete all records? (yes/no): ")
if confirm.lower() == "yes":
    sql = "DELETE FROM users"
    cursor.execute(sql)
    db.commit()
    print(cursor.rowcount, "record(s) deleted.")
else:
    print("Operation canceled.")

Full Python Code Example

Here’s a complete script for deleting records:

import mysql.connector

# Connect to MySQL database
db = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database"
)

cursor = db.cursor()

# Example 1: Delete a specific record
sql = "DELETE FROM users WHERE id = %s"
val = (5,)
cursor.execute(sql, val)
db.commit()
print(cursor.rowcount, "record(s) deleted.")

# Example 2: Delete all records with confirmation
confirm = input("Are you sure you want to delete all records? (yes/no): ")
if confirm.lower() == "yes":
    sql = "DELETE FROM users"
    cursor.execute(sql)
    db.commit()
    print(cursor.rowcount, "record(s) deleted.")
else:
    print("Operation canceled.")

# Close the connection
db.close()

Exercises

Exercise 1: Delete Old Orders

Write a Python script to delete all orders older than one year from an orders table.

Exercise 2: Delete by Name

Delete a customer named “John Doe” from a customers table.

Exercise 3: Delete by Multiple Conditions

Remove all products from a products table where the stock is 0 and the last update was over six months ago.

Why Learn DELETE FROM?

Knowing how to delete records safely is crucial for maintaining database integrity and ensuring smooth application performance. By mastering the DELETE FROM statement, you can clean and optimize your database with ease.

Conclusion

The DELETE FROM statement is a powerful tool for managing and maintaining databases. Whether you’re removing outdated data or clearing an entire table, Python makes it easy to execute this command with precision.

Leave a Comment