How can you update certain values in a table in MySQL using Python?

The data in the table may be outdated and we may require to change the data after some time. Suppose, we have a table of Students and one of the students have changed their address. We require to change the address of the student in the database to avoid any problems in future due to wrong data.

The “UPDATE” statement in MySQL is used to update some value in the table.The SET clause is used to set the new value in the column. The WHERE clause is used to identify where in the table do we need to update the data or value.

Syntax

UPDATE table_name SET column=new_value WHERE column=old_value

Steps invloved to update data in a table using MySQL in python

• import MySQL connector

• establish connection with the connector using connect()

• create the cursor object using cursor() method

• create a query using the appropriate mysql statements

• execute the SQL query using execute() method

• commit the changes made using commit() method

• close the connection

Suppose we have a table named “Student” as follows −

+----------+---------+-----------+------------+
|    Name  | Class   |    City   |    Marks   |
+----------+---------+-----------+------------+
|    Karan |    4    | Amritsar  |    95      |
|    Sahil |    6    | Amritsar  |    93      |
|    Kriti |    3    | Batala    |    88      |
|   Khushi |    9    | Delhi     |    90      |
|    Kirat |    5    | Delhi     |    85      |
+----------+---------+-----------+------------+

Example

Suppose, we have the above table of students and we want to update the city of Kriti from Batala to Kolkata.

import mysql.connector
db=mysql.connector.connect(host="your host", user="your username", password="your
password",database="database_name")

cursor=db.cursor()

query="UPDATE Students SET City='Kolkata' WHERE Name='Kriti'"
cursor.execute(query)
db.commit()

query="SELECT * FROM Students"
cursor.execute(query)

for row in cursor:
   print(row)
db.close()

The above code updates the city name of Kriti.

Output

(‘Karan’, 4 ,’Amritsar’ , 95)
(‘Sahil’ , 6 , ‘Amritsar’ ,93)
(‘Kriti’ , 3 , ‘Kolkata’ ,88)
(‘Amit’ , 9 , ‘Delhi’ , 90)
(‘Priya’ , 5 , ‘Delhi’ ,85)

NOTE

The db.commit() in the above code is important. It is used to commit the changes made to the table. Without using commit(), no changes will be made in the table.