Python MySQL DELETE: Removing Records, Batch Deletion & Safety Checks

Interactive Google Colab Practice Notebook

Practice deleting single records, parameterized queries, bulk deletes using executemany(), table truncation, and conditional existence checks directly inside Google Colab.

View on GitHub

We will use SQL DELETE to remove records from our database student table using mysql.connector.

We defined my_cursor and my_connect in our database connection guide. Remember that data-modifying statements require an explicit commit() to permanently save changes unless autocommit is enabled on your connection.

my_cursor = my_connect.cursor()
my_cursor.execute("DELETE FROM student WHERE id=25")
my_connect.commit()
print("Rows Deleted =", my_cursor.rowcount)
my_connect.close()

Output:

Rows Deleted = 1

We used rowcount to verify the number of records deleted from our student table.

Handling Errors During Deletion

If an error occurs (such as an incorrect column name or constraint violation), MySQL returns an error message. We can catch and display database errors using try-except exception blocks.

my_cursor = my_connect.cursor()
try:
    my_cursor.execute("DELETE FROM student WHERE Class='Five'")
    my_connect.commit()
    print("Rows Deleted =", my_cursor.rowcount)
except mysql.connector.Error as my_error:
    print(my_error)
my_connect.close()

Output:

Rows Deleted = 3

Using Parameterized Queries for Security

Placeholders (%s) should be used inside the SQL string while passing variables separately as a tuple or list. This standard practice prevents SQL injection attacks when handling untrusted user input.

my_cursor = my_connect.cursor()
try:
    query = "DELETE FROM student WHERE Class=%s"
    my_data = ('Six',)
    my_cursor.execute(query, my_data)
    my_connect.commit()
    print("Rows Deleted =", my_cursor.rowcount)
except mysql.connector.Error as my_error:
    print(my_error)
my_connect.close()

Output:

Rows Deleted = 7

my_cursor = my_connect.cursor()
try:
    query = "DELETE FROM student WHERE id=%s"
    my_data = (7,)
    my_cursor.execute(query, my_data)
    my_connect.commit()
    print("Rows Deleted =", my_cursor.rowcount)
except mysql.connector.Error as my_error:
    print(my_error)
my_connect.close()

Output:

Rows Deleted = 1

Deleting Multiple Records Using executemany()

When deleting multiple distinct records in bulk, passing a list of parameter tuples to executemany() is significantly more efficient than running separate queries in a loop.

my_cursor = my_connect.cursor()
try:
    query = "DELETE FROM student WHERE id=%s"
    my_data = [(7,), (5,), (4,), (3,)]
    my_cursor.executemany(query, my_data)
    my_connect.commit()
    print("Rows Deleted =", my_cursor.rowcount)
except mysql.connector.Error as my_error:
    print(my_error)
my_connect.close()

Output:

Rows Deleted = 4

Deleting All Records (DELETE vs. TRUNCATE)

To remove all records from a table without dropping its structure, you can execute a DELETE FROM statement without a WHERE clause:

my_cursor = my_connect.cursor()
try:
    query = "DELETE FROM student"
    my_cursor.execute(query)
    my_connect.commit()
    print("Rows Deleted =", my_cursor.rowcount)
except mysql.connector.Error as my_error:
    print(my_error)
my_connect.close()

Output:

Rows Deleted = 24

Alternatively, you can use the TRUNCATE TABLE command, which drops and recreates the table internally for high-speed clearing and resets auto-increment counters:

my_cursor = my_connect.cursor()
try:
    query = "TRUNCATE student"
    my_cursor.execute(query)
    my_connect.commit()
    print("Rows Deleted =", my_cursor.rowcount)
except mysql.connector.Error as my_error:
    print(my_error)
my_connect.close()

Output:

Rows Deleted = 0

Deleting Records After Checking If They Exist

Before executing a delete statement, you can check if a matching record exists using a SELECT query combined with rowcount or conditional logic:

Using SQLAlchemy 2.0+:

from sqlalchemy import create_engine, text
engine = create_engine("mysql+mysqldb://userid:password@localhost/my_tutorial")

query = "SELECT id FROM student WHERE id=6"                
with engine.begin() as conn:
    result = conn.execute(text(query))
    rows = result.fetchall()
    if len(rows) > 0:
        del_res = conn.execute(text("DELETE FROM student WHERE id=6"))
        print("Rows Deleted =", del_res.rowcount)
    else:
        print("No matching record")

Using MySQL Connector:

import mysql.connector

my_connect = mysql.connector.connect(
  host="localhost",
  user="userid",
  passwd="password",
  database="my_tutorial"
)
####### end of connection ####
my_cursor = my_connect.cursor(buffered=True)
query = "SELECT id FROM student WHERE id=5" 
my_cursor.execute(query)
if my_cursor.rowcount == 1:
    my_cursor.execute("DELETE FROM student WHERE id=5")
    my_connect.commit()
    print("Rows Deleted =", my_cursor.rowcount)
else:
    print("No matching record")
my_connect.close()

Frequently Asked Questions & Common Errors

Related Tutorials & Next Steps

• Python MySQL DROP Table Guide

  Learn how to completely delete tables, constraints, and databases using DROP queries.

• Python MySQL UPDATE Guide

  Learn how to modify and update existing records in MySQL tables.

• Python MySQL cursor.rowcount

  Understand how row counts behave across SELECT statements, updates, and deletes.

• Student Table SQL Dump File

  Download the complete student.sql database dump file featuring sample records with the gender column schema.

« Delete MySQL table query « More on Error handling

« Python functions for MySQL Collect records Python- Tutorials »

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect:

← Subscribe to our YouTube Channel here