Python - MySQL Delete Record

In this tutorial, we will learn how to delete records from a MySQL database using Python. We will start by sharing a full program that deletes a record from a database and then explain each section of the program in detail.

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
sql = "DELETE FROM customers WHERE address = 'Mountain 21'"
mycursor.execute(sql)
mydb.commit()
print(mycursor.rowcount, "record(s) deleted")

Explanation:

Let's go through the code line by line to understand it better:

import mysql.connector

We start by importing the mysql.connector module that allows us to connect to the MySQL database.

mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mydatabase"
)

Next, we connect to the MySQL database using the connect() method of the mysql.connector module. We pass the database connection details such as host, user, password, and database as arguments to the connect() method. Replace the values with your own credentials.

mycursor = mydb.cursor()

After connecting to the database, we create a cursor object that allows us to execute SQL queries on the database.

sql = "DELETE FROM customers WHERE address = 'Mountain 21'"

We define the SQL query to delete a record from the customers table where the address column is equal to 'Mountain 21'. Replace the table and column names with your own.

mycursor.execute(sql)

Next, we execute the SQL query using the execute() method of the cursor object.

mydb.commit()

After executing the query, we need to commit the changes to the database using the commit() method of the database connection object.

print(mycursor.rowcount, "record(s) deleted")

Finally, we print the number of records that have been deleted from the table using the rowcount property of the cursor object.