← Back to Python Course | Chapter 15: Python & MySQL | Lesson 8 of 12

Python MySQL Delete Record

Rows are removed from a table with a DELETE FROM statement -- always paired with a WHERE clause to target specific rows, since an unqualified DELETE FROM with no WHERE removes every row in the table.

In this page:

  1. Deleting a Specific Row
  2. The Danger of Deleting Without WHERE
  3. Deleting Multiple Matching Rows
  4. Handling Foreign Key Constraints on Delete
  5. Using Transactions for Safer Deletes
Syntax
python 
sql = "DELETE FROM table_name WHERE column = %s"
cursor.execute(sql, (value,))
connection.commit()

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

python 
from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor()
cursor.execute("DELETE FROM customers WHERE address = %s", ("Mountain 21",))  # only matching rows are removed
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

python 
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

python 
from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor()
cursor.execute("DELETE FROM customers WHERE city = %s", ("Austin",))  # broader condition can match several rows
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

python 
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,))  # blocked by a referencing foreign key
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

python 
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()  # restores the database to its prior consistent state
    print("Rolled back on failure")
else:
    print("Delete committed safely")
Related Topics

Python Delete Files · Python MySQL Order By · Python MySQL Drop Table · Python MySQL Select With a Filter

{# common_mistakes/chapter_summary/browser_support: on Hindi pages the view already swaps in the hi_ translation fields (or blanks these out if untranslated), so this renders correctly for both languages without a lang_code check here. #}
Common Mistakes
  1. 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.
  2. Forgetting conn.commit() after a DELETE, leaving the deletion unsaved.
  3. Not checking cursor.rowcount after a delete to confirm how many rows (if any) were actually affected, silently assuming the intended row was removed.
Chapter Summary
  • 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.
Browser Support

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:

← Previous Lesson Python MySQL Order By Next Lesson → Python MySQL Drop Table

Login to run this code

C/C++/Java/PHP execution requires a free account. Your code is saved — you'll land right back in the editor after logging in.