Python MySQL DELETE FROM

Description

If you know how to use the INSERT command,you should also know the Python MySQL DELETE FROM statement. The opposite of the INSERT statement, it is used to delete records or rows from a table in a database.

syntax

DELETE FROM tablename

Let’s go through some examples to demonstrate it’s safe and proper use. We’ll be using the database shown below throughout this article.

CodersLegacy Python MySQL Database for DELETE statement — Database “Students”

Example

Remember to import the mysql.connector and create a connection to your database as shown below before attempting anything. Follow up by creating a cursor object which we’ll be using to manipulate to the database.

import mysql.connector

db = mysql.connector.connect(
  host = "localhost",
  user = "root",
  password = "*********",
  database = "mydatabase"
)

cursor = db.cursor()
cursor.execute("DELETE FROM students WHERE id = 6")
db.commit()

This code runs the DELETE FROM statement along with a WHERE clause. It’s very important to have a WHERE clause included else all the rows in Table will be deleted. The code above deletes only the row which has the id number of 6.

Remember to run the db.commit() command for the changes to actually place.

Preventing SQL injection

While you’re working on the localhost, this isn’t a problem. However, when working with databases connected to the internet or on web servers, this is a glaring issue. SQL injection is when a hacker injects SQL code into your database using a vulnerability. Luckily, there is an easy fix for this.

Remember the %s placeholder character? Using this prevents SQL injection.

import mysql.connector

db = mysql.connector.connect(
  host = "localhost",
  user = "root",
  password = "******",
  database = "mydatabase"  )

cursor = db.cursor() 

sql = "SELECT * FROM student WHERE gender = %s"
gender = ("Male")

cursor.execute(sql, gender)

results = cursor.fetchall()
print(results)

This marks the end of our Python MySQL DELETE Article. Let us know if you have any suggestions or corrections in this Article to make. Contributions to help grow CodersLegacy are more than welcome.

Use this link to head back to main Python MySQL page: link