Python and MySQL - Deleting Data
In order to delete data in a MySQL database, the SQL ‘Delete’ statement needs to be used. The following example deletes a record with a specific ‘id’ from the ‘person’ table, which was used in the examples for selecting, inserting and updating data.
Deleting data works in a similar way to the other examples, where a parameterised query is used. Firstly, a connection to the database is established, then the query parameter for the ‘id’ of the record to be deleted is defined as a variable, which is bound in to the following SQL statement when it is executed and a confirmation message is displayed. The execution of the SQL statement is wrapped in a ‘try-except-finally’ block to catch any errors that may arise and close the database connection at the end.
import pymysql # Database connection variable. connect = None try: # Connect to database. connect = pymysql.connect(host='localhost', db='testDB', user='testUN', passwd='testPW') except pymysql.DatabaseError as e: # Confirm unsuccessful connection and stop program execution. print("Database connection unsuccessful.") quit() # Cursor to execute query. cursor = connect.cursor() # Query parameter. id = 2 # SQL to delete data in the person table. sqlDelete = \ "DELETE FROM person \ WHERE id = %s" try: # Execute query and commit changes. cursor.execute(sqlDelete, (id,)) connect.commit() # Confirm successful deletion of person information. print("Person information deleted successfully.") except pymysql.DatabaseError as e: # Confirm error deleting person information and stop program execution. print("Error deleting person information.") quit() finally: # Close database connection. connect.close()
The contents of the ‘person’ table now looks as follows.