Python MySQL UPDATE – Modify Rows in a Table

Modifying existing records is one of the most common database operations. Whether you are correcting a typo, updating a customer's address, or marking an order as shipped, the SQL UPDATE statement is the tool for the job. This chapter shows you how to run UPDATE statements from Python using mysql-connector-python, covering single-row updates, multi-column updates, bulk updates with executemany(), the context-manager pattern, and the common mistakes that catch beginners off guard.

Prerequisites

You need the following before running the examples:

Python 3.8 or later installed
mysql-connector-python installed (pip install mysql-connector-python)
A running MySQL server (local or remote)
A database and table to work with (see MySQL Get Started and MySQL Create Table)

The examples below assume you have a database called mydatabase that contains a customers table created and populated in the MySQL Insert chapter.

The UPDATE Statement

The SQL UPDATE statement changes the values of one or more columns in rows that match a condition:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Always include a WHERE clause. Without it, every row in the table is updated. MySQL applies the change silently — there is no confirmation prompt.

Connecting to MySQL

Before executing any statement you must open a connection and create a cursor. The cursor sends SQL to the server and retrieves results.

import mysql.connector
from mysql.connector import Error

mydb = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="mydatabase"
)

mycursor = mydb.cursor()

Updating a Single Row

The safest way to target one row is a parameterized query. Pass values as a Python tuple — the connector escapes them before sending the query to MySQL, which prevents SQL injection.

import mysql.connector
from mysql.connector import Error

try:
    mydb = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword",
        database="mydatabase"
    )
    mycursor = mydb.cursor()

    sql = "UPDATE customers SET address = %s WHERE address = %s"
    val = ("Park Lane 38", "Highway 37")

    mycursor.execute(sql, val)
    mydb.commit()                       # write the change to disk

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

except Error as e:
    print(f"Error: {e}")
    mydb.rollback()                     # undo any partial changes on failure

finally:
    if mydb.is_connected():
        mycursor.close()
        mydb.close()

cursor.rowcount tells you how many rows were actually changed. A value of 0 means the WHERE condition matched nothing — the query ran without error, but nothing was updated.

Why commit() is required

mysql-connector-python opens connections with autocommit disabled by default. Until you call mydb.commit(), the update exists only inside your current transaction and is invisible to other connections. If the script crashes before committing, the change is automatically rolled back. Calling mydb.rollback() explicitly in the except block makes the intent clear.

Updating Multiple Columns at Once

Separate column assignments with commas inside the SET clause to change several fields in a single statement:

import mysql.connector
from mysql.connector import Error

try:
    mydb = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword",
        database="mydatabase"
    )
    mycursor = mydb.cursor()

    sql = "UPDATE customers SET name = %s, address = %s WHERE id = %s"
    val = ("John Smith", "Main Street 10", 1)

    mycursor.execute(sql, val)
    mydb.commit()

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

except Error as e:
    print(f"Error: {e}")
    mydb.rollback()

finally:
    if mydb.is_connected():
        mycursor.close()
        mydb.close()

Using the primary key (id) in the WHERE clause is the most reliable way to target exactly one row.

Updating Multiple Rows in One Statement

If you want to apply the same change to all rows that match a condition, broaden the WHERE clause. One execute() call is enough:

import mysql.connector
from mysql.connector import Error

try:
    mydb = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword",
        database="mydatabase"
    )
    mycursor = mydb.cursor()

    # Change the city to "Oslo" for every customer on "Park Lane"
    sql = "UPDATE customers SET address = %s WHERE address LIKE %s"
    val = ("Oslo 1", "%Park Lane%")

    mycursor.execute(sql, val)
    mydb.commit()

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

except Error as e:
    print(f"Error: {e}")
    mydb.rollback()

finally:
    if mydb.is_connected():
        mycursor.close()
        mydb.close()

Updating a List of Rows with executemany()

When you have a list of rows to update with different values for each, use executemany(). It runs the same parameterized statement once per item in a single round-trip, keeping everything inside one transaction:

import mysql.connector
from mysql.connector import Error

# Each tuple: (new_address, customer_id)
updates = [
    ("Sunset Blvd 1",  3),
    ("Baker Street 2", 7),
    ("Abbey Road 3",   11),
]

try:
    mydb = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword",
        database="mydatabase"
    )
    mycursor = mydb.cursor()

    sql = "UPDATE customers SET address = %s WHERE id = %s"
    mycursor.executemany(sql, updates)
    mydb.commit()

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

except Error as e:
    print(f"Error: {e}")
    mydb.rollback()

finally:
    if mydb.is_connected():
        mycursor.close()
        mydb.close()

Because executemany() wraps all updates in a single transaction, either all succeed or none do — you never end up with a partially updated dataset.

Using a Context Manager (Recommended Pattern)

Opening the connection inside a with statement ensures it is always closed, even if an exception is raised mid-way. This is the cleanest pattern for production code:

import mysql.connector
from mysql.connector import Error

db_config = {
    "host": "localhost",
    "user": "yourusername",
    "password": "yourpassword",
    "database": "mydatabase",
}

try:
    with mysql.connector.connect(**db_config) as mydb:
        with mydb.cursor() as mycursor:
            sql = "UPDATE customers SET address = %s WHERE name = %s"
            mycursor.execute(sql, ("New Road 5", "Alice"))
            mydb.commit()
            print(mycursor.rowcount, "record(s) affected")

except Error as e:
    print(f"Error: {e}")

The with block closes both the cursor and the connection when the block exits, regardless of whether an exception was raised.

Previewing Changes Before Updating (Dry Run)

Before running an update that affects many rows, run a SELECT with the same WHERE clause first. This dry run shows you exactly which rows will be changed:

import mysql.connector
from mysql.connector import Error

try:
    mydb = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword",
        database="mydatabase"
    )
    mycursor = mydb.cursor()

    condition = "%Highway%"

    # Dry run: see which rows would be affected
    mycursor.execute(
        "SELECT id, name, address FROM customers WHERE address LIKE %s",
        (condition,)
    )
    rows = mycursor.fetchall()
    print(f"{len(rows)} row(s) would be updated:")
    for row in rows:
        print(row)

    # Proceed only if rows were found
    if rows:
        mycursor.execute(
            "UPDATE customers SET address = %s WHERE address LIKE %s",
            ("New Highway 1", condition)
        )
        mydb.commit()
        print(f"{mycursor.rowcount} row(s) updated")

except Error as e:
    print(f"Error: {e}")
    mydb.rollback()

finally:
    if mydb.is_connected():
        mycursor.close()
        mydb.close()

Common Mistakes

Forgetting the WHERE clause

# DANGER: updates every row in the table
mycursor.execute("UPDATE customers SET address = %s", ("Wrong Street 1",))
mydb.commit()

Always verify that your WHERE clause is present and correct before calling commit().

Passing a string instead of a tuple

# Wrong — iterates over characters of the string
mycursor.execute("UPDATE customers SET address = %s WHERE name = %s", "AliceMain Street")

# Correct — wrap values in a tuple
mycursor.execute("UPDATE customers SET address = %s WHERE name = %s", ("Main Street", "Alice"))

The connector expects a sequence (tuple or list) as the second argument. Passing a bare string causes it to iterate over individual characters, leading to a confusing ProgrammingError.

Forgetting to commit()

mycursor.execute("UPDATE customers SET address = %s WHERE name = %s", ("Main Street", "Alice"))
# Missing mydb.commit() — the update is silently rolled back when the connection closes

Call mydb.commit() after every write operation, or set autocommit=True on the connection if you intentionally want each statement committed immediately.

Using string formatting instead of parameterized queries

# UNSAFE — vulnerable to SQL injection
name = input("Enter name: ")
sql = f"UPDATE customers SET address = 'New Road' WHERE name = '{name}'"
mycursor.execute(sql)

# Safe — always use %s placeholders
sql = "UPDATE customers SET address = %s WHERE name = %s"
mycursor.execute(sql, ("New Road", name))

Never build SQL strings with f-strings or + concatenation using user-supplied data.

Best Practices

Use parameterized queries (%s placeholders) at all times. This is the single most important rule — it prevents SQL injection.
Always include a WHERE clause and double-check it before committing. A missing WHERE updates every row in the table.
Commit or rollback explicitly. Relying on the connection closing to roll back is confusing. Make the outcome explicit in your except block.
Check rowcount after the update to confirm the expected number of rows were changed.
Use executemany() for batch updates rather than looping over execute(). It is faster and keeps all updates in one transaction.
Add indexes on WHERE columns. An update that scans the full table is slow on large datasets. Index the columns you filter by.
Use transactions for multi-step updates. If you update related rows across several tables (for example, updating an order and its line items together), wrap all the statements in one transaction so you never end up with inconsistent data.

MySQL Get Started — install the connector and create your first connection
MySQL Create Table — create the tables you will be updating
MySQL Insert — add rows before you practice changing them
MySQL Where — master the WHERE clause used in every UPDATE
MySQL Select — read rows after updating them to verify the result
MySQL Delete — remove rows instead of modifying them
MySQL Order By — sort results when previewing rows before an update