Python MySQL Update Table

In Python, updating data in a MySQL table is done using the UPDATE statement. This statement allows you to modify existing records in the table based on a specified condition.

The query is executed using the execute() method of the cursor object, and the changes are saved permanently using the commit() method. Without a condition, all records in the table may be updated, so it is important to use the WHERE clause carefully.

MySQL Update Table Syntax

The UPDATE operation has the following syntax:

Python MySQL Update Operation Example

In this example, we are updating an existing record in the customers table using the UPDATE statement in Python, where we first fetch and display the record before updating it, then modify its values, and finally retrieve the updated record to verify the changes.

Output:

Before Updating the record:
('Divya', 'Kolkata', 8)

After Updating the record:
('Reema', 'Kolkata', 8)

Explanation:

In the above program, we connect Python to the MySQL database using mysql.connector and create a cursor to execute SQL queries. We use the UPDATE statement with placeholders (%s) to modify the record with id 8, and apply the changes permanently using the commit() method.

Prevent SQL Injection

In SQL, it is always a good practice to escape the values of any query, including in UPDATE statements. SQL injections are a common web hacking technique that many hackers use to tamper with and misuse your database. Therefore, the placeholder %s method is used to prevent SQL Injection.

The mysql.connector module uses the placeholder %s to escape values in the update statement:

Output:

Explanation:

In the above program, we use the UPDATE statement with placeholders (%s) to modify data in the table, replacing the old value with a new one. The changes are saved using the commit() method, and rowcount is used to display the number of affected rows.