Python – MySQL – Delete Data

Python provides several libraries to connect and interact with databases like MySQL, MongoDB, Oracle, and many more. We can use the Python MySQL Connector library to connect Python with MySQL. We can manipulate MySQL tables with the help of insert, update, delete, and select statements.

Here will discuss how a user can delete a row or multiple rows from the MySQL table using Python.

Delete Row or Rows from a Table

Before moving on to the deletion of rows from the MySQL table, here we will create a sample table having some data that a user can use for the deletion of data.

Table Structure:

+———+———+——-+——+
| USER_ID | NAME | EMAIL |
+———+———+——-+——+
| 1 | AARAV | [email protected] |
| 2 | NAMAN | [email protected] |
| 3 | SUMIT | [email protected] |
| 4 | KAMLESH | [email protected] |

To connect Python with MySQL, we need to install the Python MySQL Connector library.

Use this command to install the library:

pip install mysql-connector-python

Here is the example code to delete a row with a user id = 1 from the above table.

import mysql.connector

# Establishing connection with MySQL
conn = mysql.connector.connect(user='Username',
                               password='Password',
                               host='hostname',
                               database='database_name')

# Creating Cursor
cursor = conn.cursor()

# Deleting selected row from the table
cursor.execute('''DELETE FROM TABLE_NAME WHERE USER_ID = 1''')
conn.commit()

# Closing Connection
conn.close()

Output:

The above program will delete the row from the table whose user_id = 1.

Here is the code to delete multiple rows from the MySQL table having user_id’s 3 and 4:

import mysql.connector

# Establishing connection with MySQL
conn = mysql.connector.connect(user='Username',
                       password='Password',
                       host='hostname',
                       database='database_name')

# create cursor
cursor = conn.cursor()

#Deleting multiple rows from table 
sql = "DELETE FROM TABLE_NAME WHERE USER_ID IN (3, 4)"
cursor.execute(sql)
conn.commit()

# Closing of Connection
conn.close()

Output:

The above program will delete two rows having user_id 3 and 4.