Python MySQL – Delete rows from table based on condition
To delete rows from a MySQL table based on a condition in Python program,
- Create a connection to the MySQL database with user credentials and database name, using connect() function.
- Get cursor object to the database using cursor() function.
- Call execute() function on the cursor object, and pass the DELETE FROM table query with WHERE clause. The WHERE clause specifies the condition based on which we need to delete the rows.
Consider that there is a schema named mydatabase in MySQL. The credentials to access this database are, user: root and password: admin1234, and there is a table named fruits in mydatabase.
In the following program, we delete the rows from fruits table where country equals Mexico.
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", password="admin1234", database="mydatabase" ) mycursor = mydb.cursor() try: mycursor.execute("DELETE FROM fruits WHERE country='Mexico'") print('Rows are successfully deleted.') except: print('An exception occurred while deleting rows.') mydb.commit()
Rows are successfully deleted.
Table data after DELETE operation
In this tutorial of Python Examples, we learned how to delete rows from a table based on a condition, from a Python program.