Python - MySQL - Delete data

In this tutorial we will learn to delete data from MySQL database using Python.

Prerequisite

To understand this tutorial you need to have some prior knowledge of MySQL.

Delete table data

To delete data from a table we have the following steps.

1. Connect to the MySQL server using the mysql.connector.connect() method.
2. Create MySQL cursor from the connection created in step 1.
3. Use the cursor to execute the DELETE query.
4. Use the commit() method to save the changes.
5. Close connection.

In this tutorial we will be working with an employee table.

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)

Delete record

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.

In the following Python program we are deleting employee having id 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)