W3docs
en
Log in

Python MySQL DELETE – Remove Rows from a Table

Learn how to delete rows from a MySQL database in Python using mysql-connector-python, with safe parameterized queries, bulk deletes, and error handling.

Removing records from a MySQL database is a routine task in any data-driven Python application — whether you are purging expired sessions, removing a user account, or archiving old orders. This chapter shows you how to use the DELETE SQL statement through the mysql-connector-python library, covering single-row deletes, bulk deletes, safe parameterized queries, transactions, and error handling.

Prerequisites

You need the following before running the examples:

  • Python 3.8 or later installed
  • mysql-connector-python installed (pip install mysql-connector-python)
  • A running MySQL server (local or remote)
  • A database and table to work with (see MySQL Get Started and MySQL Create Table)

The examples below assume you already have a database called mydatabase that contains a customers table created and populated in the MySQL Insert chapter.

The DELETE Statement

The SQL DELETE statement removes one or more rows from a table:

DELETE FROM table_name WHERE condition;

Always include a WHERE clause. Without it, every row in the table is deleted. MySQL does not warn you — the entire table is wiped instantly.

Connecting to MySQL

Before executing any statement you must open a connection and create a cursor object. The cursor is the object that sends SQL to the server and retrieves results.

import mysql.connector
from mysql.connector import Error

mydb = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="mydatabase"
)

mycursor = mydb.cursor()

Deleting a Single Row

The safest way to target one specific row is a parameterized query. Pass the value as a Python tuple — the connector replaces the %s placeholder after escaping the value, which prevents SQL injection.

import mysql.connector
from mysql.connector import Error

try:
    mydb = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword",
        database="mydatabase"
    )
    mycursor = mydb.cursor()

    sql = "DELETE FROM customers WHERE name = %s"
    val = ("John",)                   # note the trailing comma — this is a tuple

    mycursor.execute(sql, val)
    mydb.commit()                     # write the change to disk

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

except Error as e:
    print(f"Error: {e}")
    mydb.rollback()                   # undo any partial changes on failure

finally:
    if mydb.is_connected():
        mycursor.close()
        mydb.close()

cursor.rowcount tells you how many rows the statement actually removed. A value of 0 means the WHERE condition matched nothing — the query ran without error, but nothing was deleted.

Why commit() is required

mysql-connector-python opens connections in autocommit off mode by default. Until you call mydb.commit(), the deletion exists only inside your current transaction and is invisible to other connections. If your script crashes before committing, the deletion is automatically rolled back. Call mydb.rollback() explicitly in the except block to make the intent clear.

Deleting Multiple Rows with a Single Statement

If you want to delete all rows that match a pattern, keep the WHERE clause but broaden the condition. Only one execute() call is needed.

import mysql.connector
from mysql.connector import Error

try:
    mydb = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword",
        database="mydatabase"
    )
    mycursor = mydb.cursor()

    # Delete every customer whose address contains "Highway"
    sql = "DELETE FROM customers WHERE address LIKE %s"
    val = ("%Highway%",)

    mycursor.execute(sql, val)
    mydb.commit()

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

except Error as e:
    print(f"Error: {e}")
    mydb.rollback()

finally:
    if mydb.is_connected():
        mycursor.close()
        mydb.close()

Deleting Rows by Multiple IDs (executemany)

When you have a list of primary keys to remove, use executemany() to run the same parameterized statement once per item in a single round-trip:

import mysql.connector
from mysql.connector import Error

ids_to_delete = [(5,), (12,), (17,), (34,)]   # list of 1-tuples

try:
    mydb = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword",
        database="mydatabase"
    )
    mycursor = mydb.cursor()

    sql = "DELETE FROM customers WHERE id = %s"
    mycursor.executemany(sql, ids_to_delete)
    mydb.commit()

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

except Error as e:
    print(f"Error: {e}")
    mydb.rollback()

finally:
    if mydb.is_connected():
        mycursor.close()
        mydb.close()

executemany() wraps all the individual deletes inside a single transaction, so either all succeed or none do.

Opening the connection inside a with statement ensures it is always closed, even if an exception occurs mid-way. This is the cleanest pattern for production code:

import mysql.connector
from mysql.connector import Error

db_config = {
    "host": "localhost",
    "user": "yourusername",
    "password": "yourpassword",
    "database": "mydatabase",
}

try:
    with mysql.connector.connect(**db_config) as mydb:
        with mydb.cursor() as mycursor:
            sql = "DELETE FROM customers WHERE name = %s"
            mycursor.execute(sql, ("Alice",))
            mydb.commit()
            print(mycursor.rowcount, "record(s) deleted")

except Error as e:
    print(f"Error: {e}")

The with block closes both the cursor and the connection when the block exits, regardless of whether an exception was raised.

Counting Rows Before Deleting (Dry Run)

Before running a destructive DELETE, it is good practice to first run a SELECT COUNT(*) with the same WHERE clause. This acts as a dry run that tells you how many rows will be affected:

import mysql.connector
from mysql.connector import Error

try:
    mydb = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword",
        database="mydatabase"
    )
    mycursor = mydb.cursor()

    condition = "%Highway%"

    # Dry run: count matching rows first
    mycursor.execute("SELECT COUNT(*) FROM customers WHERE address LIKE %s", (condition,))
    count = mycursor.fetchone()[0]
    print(f"{count} row(s) would be deleted")

    if count > 0:
        mycursor.execute("DELETE FROM customers WHERE address LIKE %s", (condition,))
        mydb.commit()
        print(f"{mycursor.rowcount} row(s) deleted")

except Error as e:
    print(f"Error: {e}")
    mydb.rollback()

finally:
    if mydb.is_connected():
        mycursor.close()
        mydb.close()

Common Mistakes

Forgetting the WHERE clause

# DANGER: deletes every row in the table
mycursor.execute("DELETE FROM customers")
mydb.commit()

Always double-check that your WHERE clause is present and correct before calling commit().

Passing a string instead of a tuple

# Wrong — passes the string "John" character by character
mycursor.execute("DELETE FROM customers WHERE name = %s", "John")

# Correct — wrap the value in a tuple
mycursor.execute("DELETE FROM customers WHERE name = %s", ("John",))

Not calling commit()

mycursor.execute("DELETE FROM customers WHERE name = %s", ("John",))
# Missing mydb.commit() — the deletion will be silently rolled back
# when the connection closes

Best Practices

  • Use parameterized queries (%s placeholders) at all times. Never build SQL strings with f-strings or + concatenation using user-supplied data — this opens a SQL injection vulnerability.
  • Always commit or rollback explicitly. Relying on the connection closing to roll back a transaction is confusing; make the outcome explicit in your code.
  • Check rowcount after the delete to confirm the expected number of rows were removed.
  • Use transactions for multi-step deletes. If you need to delete rows from several related tables (e.g., an order and its line items), wrap the operations in a single transaction so you never end up with orphaned data.
  • Add appropriate indexes on columns used in WHERE clauses. A delete that scans the entire table is slow on large datasets.
Was this page helpful?