Python MySQL – Delete Data from a Table

Summary: in this tutorial, you will learn how to delete data from a table in the MySQL database using Python.

This tutorial picks up where Updating Data in a Table tutorial left off.

To delete data from a table from a Python program, you follow these steps:

• First, connect to the database by creating a new MySQLConnection object.
• Next, create a new cursor object from the Connection object.
• Then, call the execute() method of the cursor object.
• After that, commit the changes by calling the commit() method of the MySQLConnection object.
• Finally, close the cursor and database connection by calling  close() method of the corresponding object.

Deleting program

The The following example shows you how to delete a book specified by book id:

from mysql.connector import MySQLConnection, Error
from config import read_config

def delete_book(book_id):
    # read database configuration
    config = read_config()

    # prepare query and data
    query = "DELETE FROM books WHERE id = %s"

    data = (book_id, ) 

    affected_rows = 0  # Initialize the variable to store the number of affected rows

    try:
        # connect to the database
        with MySQLConnection(**config) as conn:
            # update book title
            with conn.cursor() as cursor:
                cursor.execute(query, data)

                # get the number of affected rows
                affected_rows = cursor.rowcount

            # accept the changes
            conn.commit()

    except Error as error:
        print(error)

    return affected_rows  # Return the number of affected rows

if __name__ == '__main__':
    affected_rows = delete_book(37)
    print(f'Number of affected rows: {affected_rows}')
Code language: Python (python)

How it works.

How it works.

First, import the necessary modules including MySQLConnection and Error are classes from the mysql.connector module, and read_config is a function from a custom module config.

from mysql.connector import MySQLConnection, Error
from config import read_configCode language: Python (python)

Second, define a function named delete_book that takes one parameter book_id. The function reads the database configuration and prepares an SQL query to delete the book with the specified ID:

def delete_book(book_id):
    # read database configuration
    config = read_config()

    # prepare query and data
    query = "DELETE FROM books WHERE id = %s"

    data = (book_id, ) Code language: Python (python)

Third, open a database connection (MySQLConnection) with the configuration from the configuration file and create a cursor (cursor). Inside the nested with statement, delete the book specified by the ID and return the number of affected rows obtained from cursor.rowcount:

affected_rows = 0  # Initialize the variable to store the number of affected rows

    try:
        # connect to the database
        with MySQLConnection(**config) as conn:
            # update book title
            with conn.cursor() as cursor:
                cursor.execute(query, data)

                # get the number of affected rows
                affected_rows = cursor.rowcount

            # accept the changes
            conn.commit()Code language: Python (python)

Fourth, display the error if it occurs during the delete:

    except Error as error:
        print(error)Code language: JavaScript (javascript)

Fifth, return the number of affected rows:

return affected_rows  # Return the number of affected rowsCode language: PHP (php)

Finally, call the delete_book() function to delete the book with id 37:

if __name__ == '__main__':
    affected_rows = delete_book(37)
    print(f'Number of affected rows: {affected_rows}')Code language: Python (python)

Summary

• Call the execute() method of a cursor object to run the DELETE statement to delete data in a table and the commit() method of a connection object to apply the changes to the database.