Python MySQL Delete Operation

Python MySQL Delete Operation: Like the update operation where we were updating required field from a SQL table, we can also delete an entry from the table which we have made by performing INSERT operation on the table. We can delete any entry from the table using the DELETE method in our Python program to perform this operation on SQL table.

Note: While performing DELETE operation on SQL table we must ensure that we have used WHERE clause with the DELETE method in our Python program otherwise all the entries from the table will be removed. The where clause will used to specify which entry we want to delete from the table.

Syntax: Following syntax we need to follow when we are performing Delete operation on SQL tables through Python program:

> delete from TableName where id = ID

Now, we will use this DELETE method on our 'StudentTable' to delete two entries in two different examples and then we print the table after deleting the specific entry. We will use the following syntax in the two examples:

> delete from StudentTable where id = ID (ID of students)

Consider the following two examples:

Examples 1: Deleting an entry from StudentTable using Delete operation on it where id = 2601:

 # import the mysql.connector module
 import mysql.connector
 # create the connection object with database
 MyConnectionObject = mysql.connector.connect(host = "localhost", user = "root",passwd = "2401", database = "OurPython1stDB")
 # print the defined connection object
 print(MyConnectionObject)
 # define the cursor object with cursor() function
 CursorObject = MyConnectionObject.cursor()
 # print the defined cursor object
 print(CursorObject)
 # use DELETE FROM method in try and catch method
 try: 
     CursorObject.execute("delete from Studenttable where id = 2601") # Entry field where ID = 2601 deleted from the table
     MyConnectionObject.commit() # it commits the updation in the StudentTable
     print("The entry field in StudentTable where id = 2601 is successfully removed from the table.")
 except: 
     MyConnectionObject.rollback()
 # print the updated table using fetchall() function on cursorobject
 CursorObject.execute("SELECT * FROM StudentTable")
 ResultOutput = CursorObject.fetchall()
 # print the updted table with for loop
 for z in ResultOutput:
   print(z)
 # close the connection using close() function
 MyConnectionObject.close() 

Output:

 <mysql.connector.connection.MySQLConnection object at 0x0000024832C97CD0>
 MySQLCursor: (Nothing executed yet)
 The entry field in StudentTable where id = 2601 is successfully removed from the table.
 ('Jonas', 2401, 'Science', 'A', 'Physics')
 ('Delta', 2402, 'Science', 'A', 'Physics')
 ('Lyon', 2509, 'Science', 'B', 'Maths')
 ('Peter', 2703, 'Science', 'C', 'Biology')
 ('Jonathan', 2901, 'Science', 'E', 'Biotechnology') 

Example 2: Deleting an entry from StudentTable using Delete operation on it where id = 2402:

 # import the mysql.connector module
 import mysql.connector
 # create the connection object with database
 MyConnectionObject = mysql.connector.connect(host = "localhost", user = "root",passwd = "2401", database = "OurPython1stDB")
 # print the defined connection object
 print(MyConnectionObject)
 # define the cursor object with cursor() function
 CursorObject = MyConnectionObject.cursor()
 # print the defined cursor object
 print(CursorObject)
 # use DELETE FROM method in try and catch method
 try: 
     CursorObject.execute("delete from Studenttable where id = 2402") # Entry field where ID = 2402 deleted from the table
     MyConnectionObject.commit() # it commits the updation in the StudentTable
     print("The entry field in StudentTable where id = 2402 is successfully removed from the table.")
 except: 
     MyConnectionObject.rollback()
 # print the updated table using fetchall() function on cursorobject
 CursorObject.execute("SELECT * FROM StudentTable")
 ResultOutput = CursorObject.fetchall()
 # print the updated table with for loop
 for z in ResultOutput:
   print(z)
 # close the connection using close() function
 MyConnectionObject.close() 

Output:

 <mysql.connector.connection.MySQLConnection object at 0x0000024832C97CD0>
 MySQLCursor: (Nothing executed yet)
 The entry field in StudentTable where id = 2402 is successfully removed from the table.
 ('Jonas', 2401, 'Science', 'A', 'Physics')
 ('Lyon', 2509, 'Science', 'B', 'Maths')
 ('Peter', 2703, 'Science', 'C', 'Biology')
 ('Jonathan', 2901, 'Science', 'E', 'Biotechnology') 

Explanation: In the above two examples, we have first imported mysql.connector module in our Python program to access MySQL database. Then, we have defined a connection object named 'MyConnectionObject' in the program.

We have used connect() method on 'MyConnectionObject' with specifying the database. Then, we have printed the connection object we have defined. After that, we have used cursor() function to define a cursor object in the program with name CursorObject. After that we have printed the cursor object.

Then, we used Try and catch method in our program to perform the Delete operation. Then we used the execute() function with the CursorObject and after that we have used DELETE FROM method with the where clause i.e., where ID = 2402 or 2901. Using where clause after FROM keyword specifically indicates which entry we want to remove from the table. In the first example we have removed entry of student where ID = 2601 and in second example we have removed entry of student where ID = 2402.

Then, we used commit() function with connection object to commit this updation in the table in database. After that, we have closed 'try and catch' method using the rollback() function with MyConnectionObject in catch statement. Then, we have used fetchall() function with the cursor object to define the ResultOutput through which we will print the updated table in the output of the program.

We used ResultOutput inside the for loop in our program so that we can easily print updated table as output of program. Then, we closed the connection with database using close() function with MyConnectionObject. Output will be printed after Python successfully run the program and access the database.

• We can use DELETE FROM method in any Python program like this we have used in above two examples to update any field from a given SQL table. We just have to specify the table name and use the where clause by specifying the ID of entry that we want to remove from the table.