Python MySQL Delete From By

Python, combined with MySQL, is a powerful duo for database management. When it comes to deleting records, it’s crucial to not only perform this action correctly but also to safeguard your database from SQL Injection. Here, we’ll explore how to delete records in Python using MySQL and implement measures to prevent SQL Injection.

1. Delete Record

Deleting a record in a MySQL database using Python requires a few key steps. Firstly, establish a connection to your database:

import mysql.connector

db = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    passwd="yourpassword",
    database="yourdatabase"
)
cursor = db.cursor()

To delete a record, use the DELETE FROM statement. Suppose we have a table named customers, and we want to delete a record where the id is 10:

sql = "DELETE FROM customers WHERE id = 10"
cursor.execute(sql)

db.commit()
print(cursor.rowcount, "record(s) deleted")

This code will delete the specified record. Remember to commit the changes using db.commit().

2. Prevent SQL Injection

SQL Injection is a serious security vulnerability. It occurs when an attacker manipulates a SQL query by injecting malicious code. To prevent this, use parameterized queries.

Here’s how to delete a record safely by preventing SQL Injection:

sql = "DELETE FROM customers WHERE id = %s"
val = (10,)

cursor.execute(sql, val)
db.commit()
print(cursor.rowcount, "record(s) safely deleted")

In this approach, %s is a placeholder for the values. By passing val as a parameter to execute(), we ensure that the value is treated as a parameter, not part of the SQL statement. This method effectively safeguards against SQL Injection.

Conclusion

In Python’s interaction with MySQL, deleting records is a straightforward process, but it must be handled with care, especially in terms of security. Always use parameterized queries to prevent SQL Injection, ensuring that your database remains secure and efficient.

As you continue to explore Python and MySQL, remember that these practices not only enhance your database management skills but also contribute significantly to the overall security of your applications.