MySQL Delete in Python

In Python, you can delete records or even an entire table from a MySQL database using the mysql-connector-python library.

1. Install MySQL Connector

If you haven’t installed the MySQL connector, install it using:

pip install mysql-connector-python

2. Connect to MySQL Database

Before deleting data, establish a connection to your MySQL database.

import mysql.connector# Connect to MySQL Databaseconn = mysql.connector.connect(    host="localhost",    user="root",    password="password",    database="mydatabase"  # Change to your database name)# Create a cursor objectcursor = conn.cursor()print("Connected to MySQL database successfully!")

3. Delete a Specific Record

To delete a specific record, use the DELETE FROM SQL statement.

# SQL query to delete a specific recorddelete_query = "DELETE FROM employees WHERE id = %s"values = (3,)  # Change ID to the record you want to deletecursor.execute(delete_query, values)conn.commit()  # Commit changes to the databaseprint(cursor.rowcount, "record(s) deleted.")

Explanation:

• The DELETE FROM employees WHERE id = %s deletes an employee with ID 3.

• cursor.execute(delete_query, values) executes the deletion.

• conn.commit() saves the changes to the database.

• cursor.rowcount shows how many records were deleted.

4. Delete Multiple Records

To delete multiple records based on a condition:

delete_query = "DELETE FROM employees WHERE department = %s"values = ("Sales",)  # Delete all employees in the 'Sales' departmentcursor.execute(delete_query, values)conn.commit()print(cursor.rowcount, "record(s) deleted.")

5. Delete All Records from a Table

To delete all records but keep the table structure:

cursor.execute("DELETE FROM employees")conn.commit()print("All records deleted from 'employees' table.")

? Warning: This will remove all records from the table but keep the table itself.

6. Delete a Table

If you want to delete the entire table:

cursor.execute("DROP TABLE employees")print("Table 'employees' deleted successfully.")

? Warning: This will delete the entire table permanently.

7. Closing the Connection

After performing deletions, always close the connection.

cursor.close()conn.close()print("MySQL connection closed.")

Final Complete Code

import mysql.connector# Connect to MySQL Databaseconn = mysql.connector.connect(    host="localhost",    user="root",    password="password",    database="mydatabase")cursor = conn.cursor()# Delete a specific recorddelete_query = "DELETE FROM employees WHERE id = %s"values = (3,)  # Change ID to the record you want to deletecursor.execute(delete_query, values)conn.commit()print(cursor.rowcount, "record(s) deleted.")# Close Connectioncursor.close()conn.close()

Summary

This is how you can delete records and tables in MySQL using Python! ?