Python - MySQL Update Record

Python MySQL Update Record: A Step-by-Step Guide

In this tutorial, we will learn how to update an existing record in a MySQL database using Python. We will use the mysql-connector-python module to connect to the MySQL database and update the record.

Now let's move to the Python code:

import mysql.connector
# Connect to the MySQL database
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mydatabase"
)
# Get a cursor to execute SQL queries
mycursor = mydb.cursor()
# Define the SQL query to update the record
sql = "UPDATE customers SET name = 'John Doe', email = '[email protected]' WHERE id = 1"
# Execute the SQL query
mycursor.execute(sql)
# Commit the changes to the database
mydb.commit()
print(mycursor.rowcount, "record(s) updated.")

Let's go through the code step-by-step:

  • Import the mysql.connector module to connect to the MySQL database.
  • Use the mysql.connector.connect() method to connect to the database. Replace yourusername, yourpassword, and mydatabase with your own database credentials.
  • Create a cursor object using the cursor() method. The cursor will be used to execute SQL queries.
  • Define the SQL query to update the record. In this case, we are updating the name and email fields of the record with id = 1.
  • Execute the SQL query using the execute() method on the cursor object.
  • Commit the changes to the database using the commit() method.
  • Print the number of rows updated using the rowcount attribute of the cursor object.

That's it! You have successfully updated a record in a MySQL database using Python.

In the UPDATE statement, you can specify the columns and their values that you want to update, and the condition to select the record(s) to update.

It is also possible to update multiple records at once using the WHERE clause with appropriate conditions.

In conclusion, updating a record in a MySQL database using Python is a simple process. You can use the mysql-connector-python module to connect to the database, and the execute() method on the cursor object to execute SQL queries.