Update data in Table

Welcome to a tutorial on Python MySQL –Update Table data. Here you learn Update MySQL table data in Python, by using the UPDATE SQL query and the WHERE clause.

Python MySQL UPDATE: Syntax

The UPDATE SQL query is used to update records in the MySQL table. Check for the syntax.

UPDATE table_name SET column_name = new_value WHERE condition

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

Python MySQL UPDATE Table Data: Example

Now, we will update the record in the student's table from our previous tutorial on Python MySQL to create a table, by changing the name of the student whose rollno is 2. Check below:

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost",
    user = "yourusername",
    passwd = "yourpassword",
    database = "mydatabase1"
)
cursor = db.cursor()
## defining the Query
query = "UPDATE students SET name = 'Ada Chang' WHERE rollno = 2"
## 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 confirm if the data update was successful, we can retrieve the student's table data as shown below:

import mysql.connector as mysql

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

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)

Output:

('Mike', 'Computer Science', 'London, 1)
('Ada Chang', 'Computer Science', 'Hong Kong', 2)
('Michael', 'Computer Science', 'USA', 3)