Python MySQL - Update Table data

In this tutorial, we will learn how to Update MySQL table data in Python where we will be using the UPDATE SQL query and the WHERE clause.

The UPDATE SQL query is used to update any record in MySQL table.

Python MySQL UPDATE: Syntax

Below we have the basic syntax of the UPDATE statement:

UPDATE table_name SET column_name = new_value WHERE condition

The above syntax is used to update any specific row in the MySQL table. And to specify which specific row of data we want to update, we use the WHERE clause to provide the condition to be matched while looking for the right row of data to update.

Python MySQL UPDATE Table Data: Example

Let us update the record in the students table (from the Python MySQL create table tutorial) by changing the name of the student whose rollno is 3. The code is given below:

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost",
    user = "yourusername",
    passwd = "yourpassword",
    database = "studytonight"
)
cursor = db.cursor()
## defining the Query
query = "UPDATE students SET name = 'Navin' WHERE rollno = 3"
## executing the query
cursor.execute(query)
## final step is to commit to indicate the database 
## that we have changed the table data
db.commit()

To check if the data is updated successfully we can retrieve the students table data using the given below code:

import mysql.connector as mysql

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

cursor = db.cursor()
## defining the Query
query = "SELECT * FROM students"
## getting records from the table
cursor.execute(query)
## fetching all records from the 'cursor' object
records = cursor.fetchall()
## Showing the data
for record in records:
    print(record)

The output of the above code is:

('Ramesh', 'CSE', '149 Indirapuram', 1) ('Peter', 'ME', 'Noida', 2) ('Navin', 'CE', 'New Delhi', 3)

Here is the snapshot of the actual output:

In the SELECT query too, we can use the WHERE clause to retrieve only the data of the row with rollno 3 or any other condition.