How to UPDATE data in a MySQL database using Python

A guide on how to update data in a MySQL database using Python with the mysql-connector-python library.

In this article, you will learn how to use Python to connect to a MySQL database and perform an UPDATE query to modify data within a table.

import mysql.connector

# Connect to the MySQL database
conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="test_db"
)

# Create a cursor object
cursor = conn.cursor()

# The UPDATE query to update data
sql = "UPDATE students SET name = %s WHERE id = %s"
values = ("Nguyen Van A", 1)

# Execute the query
cursor.execute(sql, values)

# Commit the changes to the database
conn.commit()

print(cursor.rowcount, "record(s) updated")

# Close the connection
cursor.close()
conn.close()

Detailed explanation:

1. import mysql.connector: Imports the mysql.connector library for MySQL connections.
2. conn = mysql.connector.connect(...): Connects to the MySQL database using connection details such as host, user, password, and database.
3. cursor = conn.cursor(): Creates a cursor object to execute SQL queries.
4. sql = "UPDATE students SET name = %s WHERE id = %s": Constructs the UPDATE query with the necessary parameters.
5. values = ("Nguyen Van A", 1): Defines the values to be used in the query.
6. cursor.execute(sql, values): Executes the query with the specified values.
7. conn.commit(): Saves the changes to the database.
8. print(cursor.rowcount, "record(s) updated"): Prints the number of records updated.
9. cursor.close() and conn.close(): Closes the cursor and the connection to the database.

System Requirements:

• Python 3.x
• Library: mysql-connector-python

How to install the libraries needed to run the Python code above:

Use pip to install the library:

pip install mysql-connector-python

Tips:

• Always double-check your SQL query to avoid unintended data modifications.
• It's a good idea to test your UPDATE queries in a development environment before applying them to a production database.