When working with MySQL databases in Python, deleting records is just as important as inserting or selecting them. In this tutorial, you’ll learn how to safely delete data from a MySQL table using Python and the mysql-connector-python library.

Table of Contents

1. What is the DELETE Statement?

2. Prerequisites

3. Install MySQL Connector

4. Connect to MySQL Database

5. DELETE with WHERE Clause

6. DELETE Multiple Records

7. Preventing Accidental Deletes

8. Full Working Example

9. Tips and Common Pitfalls

✅ 1. What is the DELETE Statement?

The DELETE statement in SQL is used to remove one or more records from a table.

Basic SQL Syntax:

DELETE FROM table_name WHERE condition;

⚠️ Always use a WHERE clause unless you intend to delete all records.

⚙️ 2. Prerequisites

Make sure you have the following:

• Python installed

• A running MySQL Server

• A MySQL database with a table and data

• The mysql-connector-python library installed

3. Install MySQL Connector

Install the required MySQL library:

pip install mysql-connector-python

4. Connect to MySQL Database

import mysql.connector

db = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="mydatabase"
)

cursor = db.cursor()

Replace your_username, your_password, and mydatabase with your actual credentials.

5. DELETE with WHERE Clause

The safest way to delete data is by specifying a condition:

sql = "DELETE FROM users WHERE id = %s"
val = (3,)

cursor.execute(sql, val)
db.commit()

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

Explanation:

• %s is a placeholder to prevent SQL injection

• val is a tuple with the value to match

• .commit() is necessary to apply changes to the database

6. DELETE Multiple Records

You can delete multiple records by expanding your condition:

sql = "DELETE FROM users WHERE age < %s"
val = (18,)

cursor.execute(sql, val)
db.commit()

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

This removes all users under the age of 18.

7. Preventing Accidental Deletes

NEVER run a DELETE statement without a WHERE clause unless you’re intentionally clearing a table.

❌ Dangerous:

cursor.execute("DELETE FROM users")

✅ Safer:

sql = "DELETE FROM users WHERE email = %s"
val = ("[email protected]",)

8. Full Working Example

import mysql.connector
from mysql.connector import Error

def delete_user(user_id):
    try:
        db = mysql.connector.connect(
            host="localhost",
            user="root",
            password="your_password",
            database="mydatabase"
        )

        cursor = db.cursor()

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

        print(f"{cursor.rowcount} record(s) deleted")

    except Error as e:
        print("Error:", e)

    finally:
        if db.is_connected():
            db.close()
            print("Database connection closed.")

# Example usage
delete_user(5)

⚠️ 9. Tips and Common Pitfalls

✅ Best Practices

• Always use parameterized queries to avoid SQL injection.

• Double-check your WHERE clause before executing.

• Use .rowcount to confirm how many rows were deleted.

• Perform backups before destructive operations.

Summary Table

Final Thoughts

Using the DELETE statement in Python with MySQL is powerful but should be handled with care. Always ensure your queries are specific, safe, and use parameterization. With good practices, deleting data becomes a reliable part of managing your applications.