Python MySQL Update Table

To update records in a MySQL table using Python, you can use the UPDATE statement. The UPDATE statement allows you to modify existing records in a table.

1. Install MySQL Connector

If you haven’t installed the MySQL connector yet, use this command:

pip install mysql-connector-python

2. Connect to the MySQL Database

You need to connect to the database and use the UPDATE statement to modify records.

Example Python Script to Update a Record

import mysql.connector

# Connect to the MySQL database
mydb = mysql.connector.connect(
  host="localhost",         # Change this to your MySQL host if needed
  user="yourusername",       # Change this to your MySQL username
  password="yourpassword",   # Change this to your MySQL password
  database="mynewdatabase"   # Change this to your database name
)

# Create a cursor object
mycursor = mydb.cursor()

# SQL query to update a specific record
sql = "UPDATE customers SET address = %s WHERE name = %s"
val = ("456 Maple Avenue", "John Doe")

# Execute the query
mycursor.execute(sql, val)

# Commit the transaction to save the changes
mydb.commit()

# Output the number of rows affected
print(mycursor.rowcount, "record(s) affected.")

Explanation:

• UPDATE customers SET address = %s WHERE name = %s: The UPDATE statement modifies the address of the customer whose name is “John Doe”.
• mycursor.execute(sql, val): Executes the query. The val tuple contains the new address and the condition value for the WHERE clause.
• mydb.commit(): Commits the changes to the database.
• mycursor.rowcount: Returns the number of rows affected by the UPDATE query.

3. Updating Multiple Columns

You can update multiple columns by adding them in the SET clause:

sql = "UPDATE customers SET address = %s, phone = %s WHERE name = %s"
val = ("456 Maple Avenue", "555-1234", "John Doe")

mycursor.execute(sql, val)

mydb.commit()

print(mycursor.rowcount, "record(s) affected.")

This query will update both the address and phone of “John Doe”.

4. Full Example: Update Data in MySQL Table

import mysql.connector

# Connect to the MySQL database
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mynewdatabase"
)

# Create a cursor object
mycursor = mydb.cursor()

# SQL query to update a specific record
sql = "UPDATE customers SET address = %s WHERE name = %s"
val = ("456 Maple Avenue", "John Doe")

# Execute the update query
mycursor.execute(sql, val)

# Commit the transaction to save the changes
mydb.commit()

# Output the number of rows affected
print(mycursor.rowcount, "record(s) affected.")

# Close the connection
mydb.close()

5. Updating Records with Multiple Conditions

You can also use the AND or OR operators in the WHERE clause to update records based on multiple conditions.

Example Using AND

sql = "UPDATE customers SET address = %s WHERE name = %s AND phone = %s"
val = ("789 Oak Street", "Jane Doe", "555-4321")

mycursor.execute(sql, val)

mydb.commit()

print(mycursor.rowcount, "record(s) affected.")

This will update the record where both the name is “Jane Doe” and the phone is “555-4321”.

6. Closing the Connection

Always close the connection after you’re done:

mydb.close()

Summary:

• UPDATE is used to modify existing records in a table.
• Use the WHERE clause to specify which record(s) to update.
• mydb.commit() is necessary to save the changes to the database.
• Be careful with the WHERE clause, as omitting it will update all records in the table.