Remove records of a database table using DELETE query and WHERE condition

plus2net.com offers FREE online classes on Basics of Python for selected few visitors.
Read more on course content , Details about the Program.

We will use SQL DELETE to delete records of our database student table.
We defined my_cursor as connection object.

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 get the number of records deleted in our student table.

Above code will delete one record of our student table .

We can capture the error message if any returned by MySQL database by using try except. Here is the code.

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 is here

Rows Deleted = 3

Using parameterized queries

Place holders are used inside the query and while executing the query the variables are used. This way we can prevent injection attacks using our Query.

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 is here

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()

Rows Deleted = 1

Deleting multiple records by using executemany()

We have more than one record to Delete then we need to run the query multiple time. We can create tuple with our data and then run executemany(). Here is the code.

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 is here

Rows Deleted =  4

Deleting all records

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()

Rows Deleted = 24

We can also use TRUNCATE command to delete all records of a table.

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()

Rows Deleted = 0

Delete record after checking if record exists

We will use SELECT query and rowcount to check if matching record is available or not. If record exists then rowcount will return 1. We will check the returned value by using if condition and then execute the delete query. Message wil be displayed if no matching record is found.

Using sqlalchemy

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

query ='select id from student where id= 6'                
my_data=engine.execute(query)
print("Number or matching records :", my_data.rowcount)
if my_data.rowcount==1 :
    my_data=engine.execute("DELETE FROM  student WHERE  id=6")
    print("Rows Deleted = ",my_data.rowcount)
else:
    print("No matching record")

Using as connection object

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)
    my_connect.close()
else:
    print("No matching record")

	15-07-2020
i want few modification while deleting record from MySQL table. if record doesn't exist then message will appear and if exists then delete that record and message will occur for successfully deletion.

	18-07-2020
Check if record exists then delete. .. This part is added at the end now. Thanks

Delete record in MySQL database table.

Using parameterized queries

Deleting multiple records by using executemany()

Delete record after checking if record exists