Python MySQL Update Table

Are you looking to update data in a MySQL table using Python while keeping your application secure from potential SQL injection vulnerabilities? You’ve come to the right place! In this guide, we’ll walk you through the process of updating a MySQL table in Python and show you how to prevent SQL injection attacks.

Updating a MySQL Table in Python

To update a MySQL table in Python, you’ll need to use a library called “mysql-connector-python.” First, make sure you have it installed. If not, you can install it using pip:

pip install mysql-connector-python

Next, let’s establish a connection to your MySQL database:

import mysql.connector

# Replace with your database credentials
db = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database"
)

cursor = db.cursor()

Now, let’s say we have a table called “students” with columns “id,” “name,” and “age.” To update a student’s age, you can use an SQL UPDATE statement like this:

# Update a student's age
update_query = "UPDATE students SET age = %s WHERE id = %s"
new_age = 21
student_id = 1

cursor.execute(update_query, (new_age, student_id))
db.commit()

print(cursor.rowcount, "record(s) affected")

In this example, we first define the SQL query with placeholders for the new age and the student’s ID. Then, we execute the query with the actual values and commit the changes to the database.

Preventing SQL Injection

SQL injection is a security vulnerability that occurs when untrusted data is included in SQL queries without proper validation. To prevent SQL injection, always use parameterized queries (as shown in the previous example) and avoid constructing SQL queries using string concatenation.

Here’s an example of what not to do:

# Avoid this! It's vulnerable to SQL injection
unsafe_query = "UPDATE students SET age = " + str(new_age) + " WHERE id = " + str(student_id)
cursor.execute(unsafe_query)
db.commit()

By directly concatenating input data into the query, you expose your application to potential SQL injection attacks.