Flash cards
Review the key moves
What is the main idea behind Python MySQL Delete From By?
Lesson checks
Practice each idea before moving on
Short Mimo-style checks built from this lesson's code, terms, and sequence.
Which statement best captures the main point of this lesson?
Complete the missing token from the example code.
___ mysql.connectorPut the learning moves in the order that makes the concept easiest to apply.
Delete Record
You can delete records from an existing table by using the "DELETE FROM" statement:
Example
Delete any record where the address is "Mountain 21":
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="
yourusername
",
password="
yourpassword
",
database="mydatabase"
)
mycursor =
mydb.cursor()
sql = "DELETE FROM customers WHERE address =
'Mountain 21'"
mycursor.execute(sql)
mydb.commit()
print(mycursor.rowcount, "record(s) deleted")Important!: Notice the statement: mydb.commit() . It is required to make the changes, otherwise no changes are made to the table.
Notice the WHERE clause in the DELETE syntax: The WHERE clause specifies which record(s) that should be deleted. If you omit the WHERE clause, all records will be deleted!
Prevent SQL Injection
It is considered a good practice to escape the values of any query, also in delete statements.
This is to prevent SQL injections, which is a common web hacking technique to destroy or misuse your database.
The mysql.connector module uses the placeholder %s to escape values in the delete statement:
Example
Escape values by using the placeholder %s method:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="
yourusername
",
password="
yourpassword
",
database="mydatabase"
)
mycursor = mydb.cursor()
sql = "DELETE FROM customers WHERE address =
%s"
adr = ("Yellow Garden 2", )
mycursor.execute(sql, adr)
mydb.commit()
print(mycursor.rowcount, "record(s) deleted")