How to DELETE data from a MySQL database using Python

A guide on how to use Prepared Statements in Python to delete data from a table in a MySQL database safely and effectively.

In this article, you'll learn how to connect to a MySQL database and use Python with Prepared Statements to execute a DELETE statement, allowing you to remove records from a table in the database using multiple parameters.

import mysql.connector

# Connect to the MySQL database
conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="test_db"
)

# Create a cursor object
cursor = conn.cursor()

# DELETE statement with Prepared Statement
delete_query = "DELETE FROM students WHERE id = %s AND name = %s"
params = (1, 'John Doe')

# Execute the DELETE statement
cursor.execute(delete_query, params)

# Commit changes to the database
conn.commit()

# Print the number of records deleted
print(f"{cursor.rowcount} record(s) deleted.")

# Close the connection
cursor.close()
conn.close()

Detailed explanation:

1. import mysql.connector: Imports the mysql.connector library to allow MySQL connections.
2. conn = mysql.connector.connect(...): Connects to the MySQL database using connection details such as host, user, password, and database.
3. cursor = conn.cursor(): Creates a cursor object to execute SQL queries.
4. delete_query = "DELETE FROM students WHERE id = %s AND name = %s": Defines the DELETE statement with specified parameters.
5. params = (1, 'John Doe'): Declares the parameter values to be passed into the DELETE statement.
6. cursor.execute(delete_query, params): Executes the DELETE statement with the specified parameters.
7. conn.commit(): Commits the changes to the database.
8. print(f"{cursor.rowcount} record(s) deleted."): Prints the number of records that were deleted.
9. cursor.close() and conn.close(): Closes the cursor and the connection to the database.

System Requirements:

• Python 3.x
• Library: mysql-connector-python

How to install the libraries needed to run the Python code above:

Use pip to install the library:

pip install mysql-connector-python

Tips:

• Make sure your MySQL server is running before attempting to connect.
• Double-check your connection details like host, user, password, and database to avoid connection errors.
• Use Prepared Statements to protect against SQL injection attacks.