Python MySQL UPDATE: Single, Parameterized & Bulk Updates

Interactive Google Colab Practice Notebook

Practice updating single records, parameterized queries, bulk updates using executemany(), and tracking affected rows via rowcount directly inside Google Colab.

View on GitHub

We will use SQL UPDATE to modify existing records in our database student table using mysql.connector.

We defined my_cursor and my_connect in our database connection guide. Remember that data-modifying statements require an explicit commit() to permanently save changes unless autocommit is enabled on your connection.

my_cursor = my_connect.cursor()
my_cursor.execute("UPDATE student SET class='Five' Where id=25")
my_connect.commit()
print("Rows updated =", my_cursor.rowcount)

Output:

Rows updated = 1

We used rowcount to verify the number of records affected by the update query.

Handling Errors During Updates

If an error occurs (such as an invalid column name), MySQL returns an error message. We can catch and display database errors using try-except exception blocks.

my_cursor = my_connect.cursor()
try:
    my_cursor.execute("UPDATE student SET class1='Five' Where id=25")
    my_connect.commit()
    print("Rows updated =", my_cursor.rowcount)
except mysql.connector.Error as my_error:
    print(my_error)
my_connect.close()

Output:

1054 (42S22): Unknown column 'class1' in 'field list'

Using Parameterized Queries for Security

Placeholders (%s) should be used inside the SQL string while passing variables as a separate tuple. This standard practice prevents SQL injection attacks when handling untrusted user input.

my_cursor = my_connect.cursor()
try: 
    query = "UPDATE student SET class=%s Where id=%s"
    my_data = ('Five', 25)
    my_cursor.execute(query, my_data)
    my_connect.commit()
    print("Rows updated =", my_cursor.rowcount)
except mysql.connector.Error as my_error:
    print(my_error)
my_connect.close()

Output:

Rows updated = 1

Updating Multiple Records Using executemany()

When updating multiple records in bulk, passing a list of tuples to executemany() is significantly faster and cleaner than running single queries in a loop.

my_cursor = my_connect.cursor()
try:
    query = "UPDATE student SET class=%s Where id=%s"
    my_data = [('Three', 25), ('Four', 26), ('Seven', 27)]
    my_cursor.executemany(query, my_data)
    my_connect.commit()
    print("Rows updated =", my_cursor.rowcount)
except mysql.connector.Error as my_error:
    print(my_error)
my_connect.close()

Output:

Rows updated = 3

Frequently Asked Questions & Common Errors

MySQL only counts a row as affected if its values actually change. If you update a record with the exact same values it already holds, rowcount will return 0.

Yes, unless autocommit is enabled on your connection object, you must explicitly call my_connect.commit() to permanently save data modification changes to the database.

executemany() takes an SQL query template and an iterable sequence (like a list of tuples) and executes the statement iteratively across all parameter sets in a single optimized batch transaction.

Always use parameterized queries with %s placeholders and pass user inputs separately as tuples or dictionaries to cursor.execute(), avoiding string concatenation entirely.

Related Tutorials & Next Steps

Python MySQL Insert Record Guide
Learn how to insert records using standard MySQL Connector and executemany().
Python MySQL cursor.rowcount
Understand how row counts behave across SELECT statements, updates, and deletes.
MySQL Delete Records Guide
Learn how to remove unwanted records from database tables safely.
Student Table SQL Dump File
Download the complete student.sql database dump file featuring sample records with the gender column schema.

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect: