MySQL UPDATE in Python

The UPDATE statement in SQL is used to modify the existing records in a table. You can use it to update one or more columns in the table. In Python, you can use the mysql-connector-python library to interact with MySQL and execute UPDATE queries.

1. Install MySQL Connector

If you haven't installed the MySQL connector, you can install it using:

pip install mysql-connector-python

2. Connect to MySQL Database

Establish a connection to your MySQL database.

import mysql.connector# Connect to MySQL Databaseconn = mysql.connector.connect(    host="localhost",    user="root",    password="password",    database="mydatabase"  # Change to your database name)# Create a cursor objectcursor = conn.cursor()print("Connected to MySQL database successfully!")

3. Create Sample Table and Insert Data

Let’s create a sample table employees and insert some sample data to perform an UPDATE.

# Create employees tablecursor.execute("""CREATE TABLE IF NOT EXISTS employees (    id INT AUTO_INCREMENT PRIMARY KEY,    name VARCHAR(100),    age INT,    department VARCHAR(50))""")# Insert sample data into employees tablecursor.execute("""INSERT INTO employees (name, age, department) VALUES ('Alice', 30, 'HR'), ('Bob', 25, 'IT'), ('Charlie', 28, 'Finance'),('David', 40, 'HR'),('Eve', 35, 'IT')""")conn.commit()

4. UPDATE Data

Now let’s use the UPDATE statement to modify data in the table.

4.1 Update a Single Record

To update a specific employee’s age:

# SQL query to update a single recordupdate_query = "UPDATE employees SET age = 32 WHERE name = 'Alice'"cursor.execute(update_query)# Commit the transactionconn.commit()print("Record updated successfully!")

This will update Alice's age to 32.

4.2 Update Multiple Records

You can also update multiple records at once. For example, to change the department of employees who are in the HR department:

# SQL query to update multiple recordsupdate_multiple_query = "UPDATE employees SET department = 'Human Resources' WHERE department = 'HR'"cursor.execute(update_multiple_query)# Commit the transactionconn.commit()print("Multiple records updated successfully!")

This will change the department name from HR to Human Resources for all employees in the HR department.

5. Check the Updated Records

You can verify the update by running a SELECT query.

# SQL query to check the updated dataselect_query = "SELECT * FROM employees"cursor.execute(select_query)# Fetch and print the resultsresults = cursor.fetchall()print("Updated employee records:")for row in results:    print(row)

6. Closing the Connection

Always close the cursor and connection after performing operations.

cursor.close()conn.close()print("MySQL connection closed.")

Final Complete Code

import mysql.connector# Connect to MySQL Databaseconn = mysql.connector.connect(    host="localhost",    user="root",    password="password",    database="mydatabase")cursor = conn.cursor()# Create employees tablecursor.execute("""CREATE TABLE IF NOT EXISTS employees (    id INT AUTO_INCREMENT PRIMARY KEY,    name VARCHAR(100),    age INT,    department VARCHAR(50))""")# Insert sample data into employees tablecursor.execute("""INSERT INTO employees (name, age, department) VALUES ('Alice', 30, 'HR'), ('Bob', 25, 'IT'), ('Charlie', 28, 'Finance'),('David', 40, 'HR'),('Eve', 35, 'IT')""")conn.commit()# UPDATE a single record (change Alice's age)update_query = "UPDATE employees SET age = 32 WHERE name = 'Alice'"cursor.execute(update_query)conn.commit()print("Alice's age updated successfully!")# UPDATE multiple records (change HR department to Human Resources)update_multiple_query = "UPDATE employees SET department = 'Human Resources' WHERE department = 'HR'"cursor.execute(update_multiple_query)conn.commit()print("HR department updated to Human Resources for all employees!")# Check the updated recordsselect_query = "SELECT * FROM employees"cursor.execute(select_query)results = cursor.fetchall()print("\nUpdated employee records:")for row in results:    print(row)# Close connectioncursor.close()conn.close()

Summary of UPDATE Statement

• UPDATE table_name SET column_name = value WHERE condition; — Updates one or more columns in a row.

• You can use conditions to specify which rows should be updated (WHERE clause).

• If no WHERE clause is provided, all records will be updated.

This is how you can use the UPDATE statement in MySQL with Python! ?