Python-MySQL Delete Record

In this tutorial, we will learn to delete a record(s) from the MySQL table. There are two methods by which we can delete the records- using a simple delete query or using a prepared query. We will see both methods which you can try and use in real-time projects. So, let’s get started!

Also read: Python-MySQL Update Records

Before We Start

Before we demonstrate the examples, you must know how the DELETE query works in MySQL. Let’s revise some terms really quick-

To delete all records-

DELETE FROM table_name;Code language: SQL (Structured Query Language) (sql)

The above statement will delete all records from the table. It is similar to the TRUNCATE statement, but it will continue to assign auto_increment id from the last records instead of starting from 1.

To delete a particular row-

DELETE FROM table_name
WHERE condition;Code language: SQL (Structured Query Language) (sql)

We can use the WHERE statement to target a particular row or set of rows.

You may set the condition with the WHERE clause such as “WHERE id = 1”, “WHERE id>10 AND id<20”, “WHERE id BETWEEN 10 AND 20” and similar to these. Note that, you must set a valid condition to capture the target row(s).

Python-MySQL Delete Records

To demonstrate the delete operation, we are using the “emp” table having the following description and the data.

Delete Record using Simple Query

import mysql.connector
mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="1234",
    database="journaldev"
)
mycursor = mydb.cursor()
query = "DELETE FROM emp WHERE id=1"
mycursor.execute(query)
mydb.commit()
print(mycursor.rowcount, " record(s) deleted")
mydb.close()Code language: Python (python)

In the above code, we first created a cursor object.

Then we write a simple delete query to delete the record having id 1.

Then we execute the query using execute() method.

Note that, unless you write a commit() method, the delete operation won’t take place in the MySQL database table. It is a necessary and the most important statement to delete the record(s).

Let’s run the program and check the emp table to see if the record is deleted.

As you can see, the record having id 1 is deleted.

Delete Multiple Records using Simple Query

import mysql.connector
mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="1234",
    database="journaldev"
)
mycursor = mydb.cursor()
query = "DELETE FROM emp WHERE id>=2 AND id<=5"
mycursor.execute(query)
mydb.commit()
print(mycursor.rowcount, " record(s) deleted")
mydb.close()Code language: Python (python)

Here, we are using the comparison operators (<=, >=) to select the range of the records.

Using the above delete query, we are targeting the rows having id 2,3,4 and 5.

When we execute the above python program, we must get the output – 4 records deleted.

Let’s run the program and see what we get on the terminal and also check the MySQL table to see if the records are deleted.

As you can see, four records have been deleted from the table.

Delete Record by Preventing SQL Injection

It is very important that only valid and proper values must be retrieved from the user before performing operations on the database. However, hackers might try to access or destroy your database using common hacking techniques such as SQL injections.

The mysql.connector module offers you to use %s placeholder to escape the values in the delete query.

Now let’s see it in a practical way.

import mysql.connector
mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="1234",
    database="journaldev"
)
mycursor = mydb.cursor()
query = "DELETE FROM emp WHERE id= %s"
value = (7,)
mycursor.execute(query, value)
mydb.commit()
print(mycursor.rowcount, " record(s) deleted")
mydb.close()Code language: Python (python)

Here, we first write a delete query without mentioning any values.

Then we create a tuple that will hold the values that should be paired with the recently created delete query.

Note that, if you are specifying only a single value, then you must put a comma after that value. Else, you will get an error.

Note that, the tuple must consist of an equal number of values as the %s is specified.

Finally, we execute the query and commit the changes in the database.

Let’s run the program and check the table to see if the changes are made.

As you can see, the record having id 7 is deleted.

Conclusion

We covered how to delete records using a simple query as well as a prepared query in this tutorial. When you’re obtaining inputs from the user, it’s best to apply the second approach for deleting records. When performing a delete action with no user input, the first technique works well.