Delete record in MySQL database table.

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")
More on Error handling


plus2net.com



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

Post your comments , suggestion , error , requirements etc here




We use cookies to improve your browsing experience. . Learn more
HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
©2000-2020 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer