Python
In this tutorial we will learn to delete data from MySQL database using Python.
To understand this tutorial you need to have some prior knowledge of MySQL.
Note!
For complete MySQL tutorial click here.
To learn more about DELETE query check out this MySQL - DELETE FROM Table tutorial.
To delete data from a table we have the following steps.
mysql.connector.connect()
commit()
In this tutorial we will be working with an employee table.
employee
The table already has some rows which are shown below.
mysql> SELECT * FROM employee; +------------+-----------+----------+---------------------+ | employeeid | firstname | lastname | created_at | +------------+-----------+----------+---------------------+ | e01 | John | Doe | 2018-01-01 10:20:30 | | e02 | Jane | Doe | 2018-01-01 12:13:14 | | e03 | Peter | Parker | 2018-01-02 15:16:17 | | e04 | Bruce | Banner | 2018-01-03 10:20:30 | | e05 | Bruce | Wayne | 2018-01-04 12:00:00 | | e06 | Tony | Stark | 2018-01-05 12:13:14 | | e07 | Doctor | Strange | 2018-01-05 13:14:15 | | e08 | Dr. | Who | 2018-01-05 13:14:15 | +------------+-----------+----------+---------------------+ 8 rows in set (0.00 sec)
To delete records from a table we take help of the execute() method and pass SQL query and value that helps us to delete the record from the table.
execute()
In the following Python program we are deleting employee having id e08.
e08
# import module import mysql.connector # import errorcode from mysql.connector import errorcode # get db connection try: cnx = mysql.connector.connect( user='yusufshakeel', password='', host='127.0.0.1', database='mydb' ) except mysql.connector.Error as err: if err.errno == errorcode.ER_ACCESS_DENIED_ERROR: print('Invalid credential. Unable to access database.') elif err.errno == errorcode.ER_BAD_DB_ERROR: print('Database does not exists') else: print('Failed to connect to database') # now delete data try: # cursor cnxCursor = cnx.cursor() # placeholder employeeid = 'e08' # sql query query = "DELETE FROM employee WHERE employeeid = %s" # data data = (employeeid,) # execute cnxCursor.execute(query, data) # commit cnx.commit() # total number of rows inserted print("Total rows deleted: %d" % cnxCursor.rowcount) except mysql.connector.Error as err: print("Error:", err.message) # close connection cnx.close() except: print("Unknown error occurred!") # close connection cnx.close() finally: # close cursor cnxCursor.close() # close connection cnx.close()
On success the above code will give us the following output.
Total rows deleted: 1
If we check the employeetable we will get the following output.
mysql> SELECT * FROM employee; +------------+-----------+----------+---------------------+ | employeeid | firstname | lastname | created_at | +------------+-----------+----------+---------------------+ | e01 | John | Doe | 2018-01-01 10:20:30 | | e02 | Jane | Doe | 2018-01-01 12:13:14 | | e03 | Peter | Parker | 2018-01-02 15:16:17 | | e04 | Bruce | Banner | 2018-01-03 10:20:30 | | e05 | Bruce | Wayne | 2018-01-04 12:00:00 | | e06 | Tony | Stark | 2018-01-05 12:13:14 | | e07 | Doctor | Strange | 2018-01-05 13:14:15 | +------------+-----------+----------+---------------------+ 7 rows in set (0.00 sec)