Python Delete data from MySQL Table

This lesson demonstrates how to execute the SQL DELETE query from Python to delete data from a MySQL database table.

After reading this article, you will able to delete single, multiple, and all rows, as well as delete a single column and multiple columns from the MySQL table using Python

Prerequisites

Before moving further, Please make sure you have the following in place: –

• Username and password to connect MySQL
• MySQL table name in which you want to insert data.

Python Example to Delete a Single Row from a MySQL table

Follow these steps: –

Example

import mysql.connector

try:
    connection = mysql.connector.connect(host='localhost',
                                         database='electronics',
                                         user='root')
    cursor = connection.cursor()
    print("Laptop table before deleting a row")
    sql_select_query = """select * from Laptop where id = 7"""
    cursor.execute(sql_select_query)
    record = cursor.fetchone()
    print(record)

    # Delete a record
    sql_Delete_query = """Delete from Laptop where id = 7"""
    cursor.execute(sql_Delete_query)
    connection.commit()
    print('number of rows deleted', cursor.rowcount)

    # Verify using select query (optional)
    cursor.execute(sql_select_query)
    records = cursor.fetchall()
    if len(records) == 0:
        print("Record Deleted successfully ")

except mysql.connector.Error as error:
    print("Failed to delete record from table: {}".format(error))
finally:
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

Output

Displaying laptop record Before Deleting it
(7, 'Acer Predator Triton', 2435.0, datetime.date(2019, 8, 17))

Record Deleted successfully 
MySQL connection is closed

Use Python Variable in a SQL query to delete data from the table

Sometimes we need input from the user, for example, when the user is deleting their data from the web portal or any other details through User Interface. in such a scenario, it is always best practice to use a parameterized query.

A prepared statement or parameterized query uses placeholders (%s ) inside any SQL statements that contain input from users. i.e., Using a parameterized query, we can pass Python variables as a query parameter in which placeholders (%s) used for parameters.

import mysql.connector

try:
    connection = mysql.connector.connect(host='localhost',
                                         database='electronics',
                                         user='pynative',
                                         password='pynative@#29')
    cursor = connection.cursor()
    sql_Delete_query = """Delete from Laptop where id = %s"""
    # row to delete
    laptopId = 6
    cursor.execute(sql_Delete_query, (laptopId,))
    connection.commit()
    print("Record Deleted successfully ")

except mysql.connector.Error as error:
    print("Failed to Delete record from table: {}".format(error))
finally:
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

Output:

Record Deleted successfully 
MySQL connection is closed

Note: We defined SQL delete query with placeholder which contains Laptop Id to delete in tuple format. Refer to Select query to fetch data from MySQL table to verify your delete operation result.

Python Delete Multiple Rows from a MySQL Table

Sometimes we need to delete an N-number of rows that match a specific condition. For example, you want to delete employee data from the employee table who left the organization. You can delete multiple rows from the MySQL table using a single delete SQL Query in Python.

import mysql.connector

try:
    connection = mysql.connector.connect(host='localhost',
                                         database='electronics',
                                         user='pynative',
                                         password='pynative@#29')

    cursor = connection.cursor()
    sql_Delete_query = """Delete from Laptop where id = %s"""
    records_to_delete = [(6,), (5,)]
    cursor.executemany(sql_Delete_query, records_to_delete)
    connection.commit()
    print(cursor.rowcount, " Record Deleted successfully")

except mysql.connector.Error as error:
    print("Failed to Delete records from MySQL table: {}".format(error))
finally:
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

Output:

2 Record Deleted successfully 
MySQL connection is closed

Refer to Select query to fetch data from MySQL table to verify your delete operation result.

Let’s  understand the above program: –

• We defined SQL delete query with placeholder which contains Laptop IDs to delete in a list format. This List contains a tuple for each row. Here we created two tuples, so we are deleting two rows.
• Next, we used the cursor’s executemany() method to delete multiple rows of a database table. Using a cursor.rowcount method, we can find how many rows were deleted successfully.

Delete All rows from a table in Python

It is possible to delete all rows from a MySQL database table using a truncate SQL query. Truncate SQL queries remove all data from a table, typically bypassing the number of integrity enforcing mechanisms.

You can refer to the Wikipedia article to read more on SQL truncate. Let’s move to python code to delete all rows from the MySQL table.

import mysql.connector

try:
    connection = mysql.connector.connect(host='localhost',
                                         database='electronics',
                                         user='pynative',
                                         password='pynative@#29')
    cursor = connection.cursor()
    Delete_all_rows = """truncate table Laptop """
    cursor.execute(Delete_all_rows)
    connection.commit()
    print("All Record Deleted successfully ")

except mysql.connector.Error as error:
    print("Failed to Delete all records from database table: {}".format(error))
finally:
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

Output:

All Record Deleted successfully 
MySQL connection is closed

Delete MySQL Table and Database from Python

You can delete old, unused tables and temporary databases and tables using a DROP TABLE and DROP DATABASE statement. Let see the demo.

import mysql.connector

try:
    connection = mysql.connector.connect(host='localhost',
                                         database='electronics',
                                         user='pynative',
                                         password='pynative@#29')
    cursor = connection.cursor()
    delete_table_query = """DROP TABLE Laptop"""
    cursor.execute(delete_table_query)

    delete_database_query = """DROP DATABASE Electronics"""
    cursor.execute(delete_database_query)
    connection.commit()
    print("Table and Database Deleted successfully ")

except mysql.connector.Error as error:
    print("Failed to Delete table and database: {}".format(error))
finally:
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

Output:

Table and Database Deleted successfully 
MySQL connection is closed

Delete MySQL Table column from Python

Use alter table drop column command to delete a column from a MySQL table.

import mysql.connector

try:
    connection = mysql.connector.connect(host='localhost',
                                         database='electronics',
                                         user='pynative',
                                         password='pynative@#29')
    cursor = connection.cursor()
    alter_column = """ALTER TABLE Laptop DROP COLUMN Purchase_date"""
    cursor.execute(alter_column)
    connection.commit()
    print("Column Deleted successfully ")

except mysql.connector.Error as error:
    print("Failed to Delete column: {}".format(error))
finally:
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

Output:

Column Deleted successfully 
MySQL connection is closed

Next Steps

To practice what you learned in this article, Please solve a Python Database Exercise project to Practice and master the Python Database operations.