Python MySQL Delete Record
In this page:
Deleting a Specific Row
cursor.execute("DELETE FROM customers WHERE address = %s", ("Mountain 21",)) removes only the row(s) matching the given WHERE condition, leaving every other row in the table untouched.
Note: Always test a DELETE's WHERE clause first as a SELECT, confirming exactly which rows it would match, before actually running the DELETE.
Warning: A DELETE statement with a WHERE clause matching multiple rows removes all of them at once -- make sure the condition is specific enough if only one row is intended.
Example: Deleting a Specific Row
from unittest.mock import MagicMock
conn = MagicMock()
cursor = conn.cursor()
cursor.execute("DELETE FROM customers WHERE address = %s", ("Mountain 21",))
conn.commit()
print("Matching row(s) deleted")The Danger of Deleting Without WHERE
DELETE FROM customers with no WHERE clause at all is syntactically valid SQL that deletes every single row in the table -- unlike DROP TABLE it leaves the empty table structure intact, but all the data itself is gone.
Note: Before running any DELETE statement in a real script, read it back to yourself and explicitly confirm a WHERE clause is present and correctly scoped.
Warning: An accidental DELETE FROM table with no WHERE is one of the most common and costly real-world database mistakes -- there is no built-in confirmation prompt to stop it.
Example: The Danger of Deleting Without WHERE
from unittest.mock import MagicMock
conn = MagicMock()
cursor = conn.cursor()
# DANGER: no WHERE clause -- this deletes every row in the table
sql = "DELETE FROM customers"
print("Always double-check for a WHERE clause before running:", sql)Deleting Multiple Matching Rows
A WHERE clause using a broader condition -- like a LIKE pattern, or a comparison operator such as < -- can match and delete several rows in a single DELETE statement, exactly like a SELECT with the same WHERE would return several rows.
Note: Run the equivalent SELECT with the same WHERE clause first to see exactly how many rows would be affected, whenever a DELETE's condition is not a single unique match.
Warning: A broad LIKE pattern in a DELETE's WHERE clause can match far more rows than intended if it is less specific than you assumed.
Example: Deleting Multiple Matching Rows
from unittest.mock import MagicMock
conn = MagicMock()
cursor = conn.cursor()
cursor.execute("DELETE FROM customers WHERE city = %s", ("Austin",))
conn.commit()
print("All matching rows removed in one statement")Handling Foreign Key Constraints on Delete
Deleting a row that is still referenced by a FOREIGN KEY in another table raises an integrity error by default -- MySQL refuses the delete to avoid leaving orphaned references, unless the foreign key was specifically defined with ON DELETE CASCADE.
Note: Delete or reassign dependent child rows first (in the referencing table) before deleting the parent row they depend on, unless ON DELETE CASCADE is explicitly set up.
Warning: ON DELETE CASCADE automatically deletes dependent child rows too -- a powerful but dangerous option that can silently remove far more data than expected if misunderstood.
Example: Handling Foreign Key Constraints on Delete
from unittest.mock import MagicMock
class Error(Exception):
pass
conn = MagicMock()
cursor = conn.cursor()
cursor.execute.side_effect = Error("Cannot delete: foreign key constraint")
try:
cursor.execute("DELETE FROM customers WHERE id = %s", (1,))
except Error as e:
print("Blocked:", e)Using Transactions for Safer Deletes
Wrapping a delete operation with an explicit try/except and conn.rollback() on failure ensures that if anything goes wrong partway through a multi-step delete, the database is left in its original, consistent state rather than a partially-completed one.
Note: For any delete operation involving more than one table or step, use try/except with an explicit rollback() on failure, treating the whole operation as one atomic unit.
Warning: Without a rollback on failure, a multi-step delete that fails partway through can leave the database with only some of the intended rows removed, an inconsistent in-between state.
Example: Using Transactions for Safer Deletes
from unittest.mock import MagicMock
class Error(Exception):
pass
conn = MagicMock()
cursor = conn.cursor()
try:
cursor.execute("DELETE FROM customers WHERE id = %s", (1,))
conn.commit()
except Error:
conn.rollback()
print("Rolled back on failure")
else:
print("Delete committed safely")- Running DELETE FROM tableName without any WHERE clause, which deletes every single row in the table instead of a specific one -- one of the most costly mistakes possible in database work.
- Forgetting conn.commit() after a DELETE, leaving the deletion unsaved.
- Not checking cursor.rowcount after a delete to confirm how many rows (if any) were actually affected, silently assuming the intended row was removed.
- DELETE FROM table WHERE condition removes only the rows matching that condition.
- Omitting the WHERE clause deletes every row in the table -- always double-check it is present before running a DELETE.
- conn.commit() must be called after DELETE to permanently save the change.
DELETE FROM syntax is standard SQL, executed identically across all mysql-connector-python versions.
Chapter Quiz — Complete all 12 topics to unlock
0/12 topics done
Complete these topics first:
- Python MySQL Get Started
- Python MySQL Create Database
- Python MySQL Create Table
- Python MySQL Insert Into Table
- Python MySQL Select From Table
- Python MySQL Select With a Filter
- Python MySQL Order By
- Python MySQL Delete Record
- Python MySQL Drop Table
- Python MySQL Update Table
- Python MySQL Limit
- Python MySQL Join