Python Tutorial
Python MySQL - Delete
Remove rows that match a condition — carefully, so you never wipe the whole table.
Delete With a Condition
DELETE removes rows matching the WHERE clause. Use parameters, and commit to persist the change.
import mysql.connector
conn = mysql.connector.connect(
host="localhost", user="root", password="secret", database="mydatabase")
cursor = conn.cursor()
sql = "DELETE FROM customers WHERE address = %s"
cursor.execute(sql, ("Mountain 21",))
conn.commit()
print(cursor.rowcount, "record(s) deleted")The Missing WHERE Trap
A DELETE without a WHERE deletes every row in the table. This is the single most dangerous SQL mistake.
# DELETE FROM customers; <-- removes ALL rows! Almost never what you want.Before running a delete, run the same WHERE as a SELECT first to confirm exactly which rows will be affected.
Preview Before Deleting
where = "WHERE created_at < %s"
params = ("2023-01-01",)
# 1) inspect what would be removed
cursor.execute("SELECT COUNT(*) FROM customers " + where, params)
print("Will delete:", cursor.fetchone()[0], "rows")
# 2) then delete
cursor.execute("DELETE FROM customers " + where, params)
conn.commit()Check How Many Rows Changed
cursor.rowcount tells you how many rows the last statement affected — 0 means nothing matched.
cursor.execute("DELETE FROM customers WHERE id = %s", (999,))
conn.commit()
if cursor.rowcount == 0:
print("No customer with that id.")Soft Deletes and Foreign Keys
- Soft delete: instead of removing the row, set a
deleted_attimestamp and filter it out in queries — keeps history and is reversible. - Foreign keys: deleting a parent row may fail or cascade to child rows depending on the
ON DELETErule (RESTRICT,CASCADE,SET NULL).
Best Practices
- Always include a
WHEREclause unless you truly mean to empty the table. - Preview with
SELECT COUNT(*)using the same condition. - Use parameters and commit; wrap risky deletes in a transaction you can roll back.
- Consider soft deletes for data you might need to recover or audit.
Try It Yourself
Exercise 1: Before a big delete, how do you preview how many rows will be removed?
Show solution
cursor.execute("SELECT COUNT(*) FROM customers WHERE active = 0")
print(cursor.fetchone()[0]) # rows the DELETE would removeExercise 2: What does DELETE FROM customers (no WHERE) do?
Show solution
It deletes every row in the table — almost never what you want. Always include a WHERE clause.
📘 Real-World Deep Dive
Knowing <strong>MySQL Delete (MySQL)</strong> well is what turns MySQL from a curiosity into a daily tool — you'll reach for it in nearly every real project.
Real-Life Scenario
An end-to-end usage of MySQL Delete that you'd actually see in a data pipeline or analytics notebook.
Real-Life Example
import mysql.connector
conn = mysql.connector.connect(database="shop")
cur = conn.cursor()
cur.execute("DELETE FROM users WHERE email = %s", ("someone@x",))
conn.commit(); print(cur.rowcount, "deleted")Expected Output
(see source)Common mistakes
- Reading with the default cursor returns tuples — switch to
DictCursorfor named access. - Strings without parameter binding produce SQL-injection holes — never use f-strings in queries.
- Connecting through a long-lived daemon pool: make sure
pool_reset_connectionis on to avoid session state leakage. - Treating MySQL Delete as a black box without reading the docs — the API has subtle defaults that bite when you scale.
🚀 Performance & Best Practices
- Use prepared statements (
cursor.executemany) for bulk inserts. - Fetch with
cursor.fetchmany(1000)in streams instead offetchall(). - Use
pymysql's server-side cursors for very large result sets. - When working with MySQL, prefer vectorised / batched operations over Python loops.
🧪 Try It Yourself
- Reproduce the snippet on a representative slice of your own data.
- Profile the snippet with
cProfileortimeitand find the single biggest improvement. - Generalise the snippet into a small, reusable function you can drop into future projects.