Update a Column for All Rows in Python MySQL

Python MySQL - Update a column for all rows

To update single column for all the rows in MySQL table 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. Specify the column name and the new value for the column in the UPDATE query. The UPDATE query updates the column value for all the rows in table.

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 all the rows in fruits table.

Python Program

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

Column updated for all rows.

Table data after UPDATE operation

Summary

In this tutorial of Python Examples, we learned how to update a column for all the rows in MySQL table, from a Python program.