Update Column in Table based on Condition in Python MySQL

Python MySQL – Update column in table based on condition

To update a column in table based on a condition in Python program,

1. Create a connection to the MySQL database with user credentials and database name, using connect() function.
2. Get cursor object to the database using cursor() function.
3. Call execute() function on the cursor object, and pass the UPDATE table query with WHERE clause. The WHERE clause specifies the condition based on which the update to column value should happen. The update happens only to those rows where the given condition is met.

Example

Consider that there is a schema named mydatabase in MySQL. The credentials to access this database are, user: root and password: admin1234, and there is a table named fruits in mydatabase.

In the following program, we update the quantity column to 0 for the rows in fruits table where country='Mexico'.

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="admin1234",
  database="mydatabase"
)

mycursor = mydb.cursor()

try:
  mycursor.execute("UPDATE fruits SET quantity=0")
  print('Column updated for all rows.')
except:
  print('An exception occurred during update.')

mydb.commit()

Output

Update successful.

Table data after UPDATE operation

Summary

In this tutorial of Python Examples, we learned how to update a column in MySQL table based on a condition, from a Python program.