Python MySQL DELETE Query – Remove Data from Database Easily

Working with databases is a key part of backend development, and Python makes it incredibly easy with the help of the MySQL Connector library. In this blog post, we’ll explain how to use the DELETE query in Python to remove unwanted or outdated records from your MySQL database.

Whether you're managing a user list, product inventory, or blog posts, the DELETE query helps you maintain a clean and optimized database.

Prerequisites

Before running DELETE queries in Python, make sure:

• MySQL server is installed and running.

• Python mysql-connector-python library is installed. Install it using pip if you haven’t:

  pip install mysql-connector-python
  

• You have access to a MySQL database and table.

Sample Database Structure

Let’s assume you have a table called users:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

Python Code to DELETE Data

Here’s how you can write a Python script to delete a record from the users table:

import mysql.connector

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

cursor = conn.cursor()

# Define the DELETE query
sql = "DELETE FROM users WHERE id = %s"
val = (3,)  # Example: Delete user with id 3

# Execute the query
cursor.execute(sql, val)

# Commit changes to the database
conn.commit()

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

# Close the connection
cursor.close()
conn.close()

Important Points

• Always use placeholders (%s) to avoid SQL injection.
• conn.commit() is crucial to apply the changes.
• Always handle connections with care – close them after operations.
• It's good practice to check cursor.rowcount to know how many records were deleted.

Use DELETE with Caution

The DELETE query permanently removes data from your table. Make sure:

• You’re targeting the correct record.
• You’ve taken a backup if needed.
• You test your queries in development before applying them on live data.

Use Case: Deleting a Blog Post

Let’s say you run a blog and want to allow admins to delete outdated posts. Here's a modified query for that:

sql = "DELETE FROM blog_posts WHERE slug = %s"
val = ('how-to-learn-python',)

Final Thoughts

The DELETE query is essential for keeping your data relevant and optimized. With Python and MySQL Connector, managing your database becomes seamless and secure.