Python MySQL Delete From By

To delete records from a MySQL table using Python with a WHERE clause, you can follow these steps. The DELETE statement is used to remove existing records in a table.

1. Install MySQL Connector

If you haven’t installed the MySQL connector yet, use this command:

pip install mysql-connector-python

2. Connect to the MySQL Database

You need to connect to the database and use the DELETE statement with a WHERE clause to specify which rows to delete.

Example Python Script to Delete Records

import mysql.connector

# Connect to the MySQL database
mydb = mysql.connector.connect(
  host="localhost",         # Change this to your MySQL host if needed
  user="yourusername",       # Change this to your MySQL username
  password="yourpassword",   # Change this to your MySQL password
  database="mynewdatabase"   # Change this to your database name
)

# Create a cursor object
mycursor = mydb.cursor()

# SQL query to delete records from the table using the WHERE clause
sql = "DELETE FROM customers WHERE address = %s"
val = ("123 Elm Street",)

# Execute the delete query
mycursor.execute(sql, val)

# Commit the transaction to save the changes
mydb.commit()

# Output the number of rows affected
print(mycursor.rowcount, "record(s) deleted.")

Explanation:

• DELETE FROM customers WHERE address = %s: The DELETE statement removes rows from the customers table where the address matches "123 Elm Street". The %s is a placeholder for the actual value.
• mycursor.execute(sql, val): Executes the query. The val tuple contains the actual value to be inserted into the WHERE condition.
• mydb.commit(): Commits the changes to the database.
• mycursor.rowcount: Returns the number of rows affected by the DELETE query.

3. Deleting Multiple Records

You can delete multiple records by using a condition that matches more than one row. For example, deleting all customers from “Maple Avenue”:

sql = "DELETE FROM customers WHERE address LIKE %s"
val = ("%Maple%",)

mycursor.execute(sql, val)

mydb.commit()

print(mycursor.rowcount, "record(s) deleted.")

This will delete all rows where the address contains “Maple.”

4. Full Example: Delete Data from MySQL Table

import mysql.connector

# Connect to the MySQL database
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mynewdatabase"
)

# Create a cursor object
mycursor = mydb.cursor()

# SQL query to delete a specific customer
sql = "DELETE FROM customers WHERE name = %s"
val = ("John Doe",)

# Execute the delete query
mycursor.execute(sql, val)

# Commit the transaction
mydb.commit()

# Output the number of rows deleted
print(mycursor.rowcount, "record(s) deleted.")

# Close the connection
mydb.close()

5. Combining DELETE with Multiple Conditions

You can also delete records based on multiple conditions, using the AND or OR operators.

Example Using AND

sql = "DELETE FROM customers WHERE name = %s AND address = %s"
val = ("John Doe", "123 Elm Street")

mycursor.execute(sql, val)

mydb.commit()

print(mycursor.rowcount, "record(s) deleted.")

Example Using OR

sql = "DELETE FROM customers WHERE name = %s OR address = %s"
val = ("Jane Doe", "456 Maple Avenue")

mycursor.execute(sql, val)

mydb.commit()

print(mycursor.rowcount, "record(s) deleted.")

6. Deleting All Records

If you want to delete all records from a table, use the DELETE statement without a WHERE clause:

mycursor.execute("DELETE FROM customers")

mydb.commit()

print(mycursor.rowcount, "record(s) deleted.")

Be careful when using this query, as it will remove all data from the customers table.

7. Closing the Connection

Always close the connection after you’re done:

mydb.close()

Summary:

• DELETE FROM is used to remove records from a table.
• Use the WHERE clause to specify which records to delete.
• mydb.commit() commits the changes to the database.
• Always be careful when deleting data, especially when using DELETE without a WHERE clause, as it can remove all records from the table.