Python MySQL - Delete Data

---

To delete records from a MySQL table, you need to use the DELETE FROM statement. To remove specific records, you need to use WHERE clause along with it.

Syntax

Following is the syntax of the DELETE query in MYSQL −

DELETE FROM table_name [WHERE Clause]

Example

Assume we have created a table in MySQL with name EMPLOYEES as −

mysql> CREATE TABLE EMPLOYEE(
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   SEX CHAR(1),
   INCOME FLOAT
);
Query OK, 0 rows affected (0.36 sec)

And if we have inserted 4 records in to it using INSERT statements as −

mysql> INSERT INTO EMPLOYEE VALUES
   ('Krishna', 'Sharma', 19, 'M', 2000),
   ('Raj', 'Kandukuri', 20, 'M', 7000),
   ('Ramya', 'Ramapriya', 25, 'F', 5000),
   ('Mac', 'Mohan', 26, 'M', 2000);

Following MySQL statement deletes the record of the employee with FIRST_NAME ”Mac”.

mysql> DELETE FROM EMPLOYEE WHERE FIRST_NAME = 'Mac';
Query OK, 1 row affected (0.12 sec)

If you retrieve the contents of the table, you can see only 3 records since we have deleted one.

mysql> select * from EMPLOYEE;
+------------+-----------+------+------+--------+
| FIRST_NAME | LAST_NAME | AGE  | SEX  | INCOME |
+------------+-----------+------+------+--------+
| Krishna    | Sharma    | 20   | M    | 2000   |
| Raj        | Kandukuri | 21   | M    | 7000   |
| Ramya      | Ramapriya | 25   | F    | 5000   |
+------------+-----------+------+------+--------+
3 rows in set (0.00 sec)

If you execute the DELETE statement without the WHERE clause all the records from the specified table will be deleted.

mysql> DELETE FROM EMPLOYEE;
Query OK, 3 rows affected (0.09 sec)

If you retrieve the contents of the table, you will get an empty set as shown below −

mysql> select * from EMPLOYEE;
Empty set (0.00 sec)

Removing records of a table using python

DELETE operation is required when you want to delete some records from your database.

To delete the records in a table −

• import mysql.connector package.

• Create a connection object using the mysql.connector.connect() method, by passing the user name, password, host (optional default: localhost) and, database (optional) as parameters to it.

• Create a cursor object by invoking the cursor() method on the connection object created above.

• Then, execute the DELETE statement by passing it as a parameter to the execute() method.

Example

Following program deletes all the records from EMPLOYEE whose AGE is more than 20 −

import mysql.connector

#establishing the connection
conn = mysql.connector.connect(
   user='root', password='password', host='127.0.0.1', database='mydb')

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Retrieving single row
print("Contents of the table: ")
cursor.execute("SELECT * from EMPLOYEE")
print(cursor.fetchall())

#Preparing the query to delete records
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (25)

try:
   # Execute the SQL command
   cursor.execute(sql)
   
   # Commit your changes in the database
   conn.commit()
except:
   # Roll back in case there is any error
   conn.rollback()

#Retrieving data
print("Contents of the table after delete operation ")
cursor.execute("SELECT * from EMPLOYEE")
print(cursor.fetchall())

#Closing the connection
conn.close()

Output

Contents of the table:
[('Krishna', 'Sharma', 22, 'M', 2000.0), 
   ('Raj', 'Kandukuri', 23, 'M', 7000.0), 
   ('Ramya', 'Ramapriya', 26, 'F', 5000.0), 
   ('Mac', 'Mohan', 20, 'M', 2000.0), 
   ('Ramya', 'Rama priya', 27, 'F', 9000.0)]

Contents of the table after delete operation:
[('Krishna', 'Sharma', 22, 'M', 2000.0), 
   ('Raj', 'Kandukuri', 23, 'M', 7000.0), 
   ('Mac', 'Mohan', 20, 'M', 2000.0)]