Python – MySQL – Update Data

Introduction

Updating data in a MySQL database using Python is a very important and useful aspect of programming. It allows you to change the values of the data stored in the database. This is a very powerful tool in database management and can change the outcome of many applications.

Prerequisites

Before proceeding further, you should have:

• Basic knowledge of MySQL and Python.
• An active MySQL database.
• Python3 installed on your system.
• MySQL Connector Python installed on your system. Install it using the following command:

  pip install mysql-connector-python

Update data in MySQL using Python

The following example demonstrates how to update data in a MySQL database using Python:

import mysql.connector

# Connect to the database
mydb = mysql.connector.connect(
  host="localhost",
  user="username",
  password="password",
  database="databasename"
)

# Get the cursor
mycursor = mydb.cursor()

# Update the data
sql = "UPDATE customers SET address = 'New Address' WHERE name = 'John Doe'"
mycursor.execute(sql)

# Commit the changes
mydb.commit()

# Print the number of updated rows
print(mycursor.rowcount, "rows updated")

This code connects to a MySQL database, updates the address column of the customers table where the name is John Doe, commits the changes, and prints the number of updated rows.

Output

1 rows updated

Update multiple rows

The following example shows how to update multiple rows in a MySQL database using Python:

import mysql.connector

# Connect to the database
mydb = mysql.connector.connect(
  host="localhost",
  user="username",
  password="password",
  database="databasename"
)

# Get the cursor
mycursor = mydb.cursor()

# Update the data
sql = "UPDATE customers SET address = %s WHERE name = %s"
val = ("New Address", "John Doe")
mycursor.execute(sql, val)

# Commit the changes
mydb.commit()

# Print the number of updated rows
print(mycursor.rowcount, "rows updated")

This code connects to a MySQL database, updates the address column of the customers table where the name is John Doe, commits the changes, and prints the number of updated rows.

Output

1 rows updated

Update all rows

The following example shows how to update all rows in a MySQL database using Python:

import mysql.connector

# Connect to the database
mydb = mysql.connector.connect(
  host="localhost",
  user="username",
  password="password",
  database="databasename"
)

# Get the cursor
mycursor = mydb.cursor()

# Update the data
sql = "UPDATE customers SET address = 'New Address'"
mycursor.execute(sql)

# Commit the changes
mydb.commit()

# Print the number of updated rows
print(mycursor.rowcount, "rows updated")

This code connects to a MySQL database, updates the address column of all rows in the customers table, commits the changes, and prints the number of updated rows.

Output

3 rows updated

Conclusion

In this article, you learned how to update data in a MySQL database using Python. You also learned how to update multiple rows and update all rows in a MySQL database. Updating data is an important aspect of database management and is extremely useful in real-world applications.