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.
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.
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")