PYnative

Python Programming

  • Learn Python
  • Exercises
  • Quizzes
  • Code Editor
  • Tricks
Home » Python » Databases » Python Delete data from MySQL Table

Python Delete data from MySQL Table

Updated on: March 9, 2021 | 1 Comment

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

Further Reading:

  • Solve Python MySQL Exercise
  • Read Python MySQL Tutorial (Complete Guide)

Table of contents

  • Prerequisites
  • Python Example to Delete a Single Row from a MySQL table
  • Use Python Variable in a SQL query to delete data from the table
  • Python Delete Multiple Rows from a MySQL Table
  • Delete All rows from a table in Python
  • Delete MySQL Table and Database from Python
  • Delete MySQL Table column from Python
  • Next Steps

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.

To perform a delete operation, I am using a Laptop table present in my MySQL server.

If a table is not present in your MySQL server, you can refer to our article to create a MySQL table from Python.

You can also download a SQL query file, which contains SQL queries for table creation and data so that you can use this table for your INSERT operations.

MySQL Laptop table with data
MySQL Laptop table with data

Python Example to Delete a Single Row from a MySQL table

Follow these steps: –

How to delete a row in MySQL using Python

  1. Connect to MySQL from Python

    Refer to Python MySQL database connection to connect to MySQL database from Python using MySQL Connector module

  2. Define a SQL Delete Query

    Next, prepare a SQL delete query to delete a row from a table. Delete query contains the row to be deleted based on a condition placed in where clause of a query.
    For example, DELETE FROM MySQL_table WHERE id=10;

  3. Get Cursor Object from Connection

    Next, use a connection.cursor() method to create a cursor object. This method creates a new MySQLCursor object.

  4. Execute the delete query using execute() method

    Execute the delete query using the cursor.execute() method. This method executes the operation stored in the delete query.
    After a successful delete operation, the execute() method returns us the number of rows affected.

  5. Commit your changes

    After successfully executing a delete operation, make changes persistent into a database using the commit() of a connection class.

  6. Get the number of rows affected

    Use a cursor.rowcount method to get the number of rows affected. The count depends on how many rows you are deleting.
    You can also Execute a MySQL select query from Python to Verify the result.

  7. Close the cursor object and database connection object

    use cursor.clsoe() and connection.clsoe() method to close open connections after your work completes.

python delete data from MySQL table
python delete data from MySQL table

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
MySQL Laptop table after deleting record
MySQL Laptop table after deleting record

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
MySQL Laptop table after deleting multiple records
MySQL Laptop table after deleting multiple records

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.

Filed Under: Python, Python Databases

Did you find this page helpful? Let others know about it. Sharing helps me continue to create free Python resources.

TweetF  sharein  shareP  Pin

About Vishal

Founder of PYnative.com I am a Python developer and I love to write articles to help developers. Follow me on Twitter. All the best for your future Python endeavors!

Related Tutorial Topics:

Python Python Databases

Python Exercises and Quizzes

Free coding exercises and quizzes cover Python basics, data structure, data analytics, and more.

  • 15+ Topic-specific Exercises and Quizzes
  • Each Exercise contains 10 questions
  • Each Quiz contains 12-15 MCQ
Exercises
Quizzes

Posted In

Python Python Databases
TweetF  sharein  shareP  Pin

  Python MySQL

  • Python MySQL Connection Guide
  • Python MySQL Insert
  • Python MySQL Select
  • Python MySQL Update
  • Python MySQL Delete
  • Call MySQL Stored Procedure
  • Python MySQL Parameterized Query
  • Python MySQL Transactions
  • Python MySQL Connection Pooling
  • Python MySQL BLOB
  • Python Database Exercise

All Python Topics

Python Basics Python Exercises Python Quizzes Python File Handling Python OOP Python Date and Time Python Random Python Regex Python Pandas Python Databases Python MySQL Python PostgreSQL Python SQLite Python JSON

About PYnative

PYnative.com is for Python lovers. Here, You can get Tutorials, Exercises, and Quizzes to practice and improve your Python skills.

Explore Python

  • Learn Python
  • Python Basics
  • Python Databases
  • Python Exercises
  • Python Quizzes
  • Online Python Code Editor
  • Python Tricks

Follow Us

To get New Python Tutorials, Exercises, and Quizzes

  • Twitter
  • Facebook
  • Sitemap

Legal Stuff

  • About Us
  • Contact Us

We use cookies to improve your experience. While using PYnative, you agree to have read and accepted our Terms Of Use, Cookie Policy, and Privacy Policy.

Copyright © 2018–2023 pynative.com