Python Tutorial
Python MySQL - Update
Modify existing rows safely with a targeted WHERE clause.
UPDATE ... SET ... WHERE
UPDATE changes column values in rows matching the condition. Use parameters and commit.
import mysql.connector
conn = mysql.connector.connect(
host="localhost", user="root", password="secret", database="mydatabase")
cursor = conn.cursor()
sql = "UPDATE customers SET address = %s WHERE address = %s"
cursor.execute(sql, ("Valley 345", "Highway 21"))
conn.commit()
print(cursor.rowcount, "record(s) affected")Like DELETE, an UPDATE without a WHERE changes every row. Always scope it.
Update Multiple Columns
Set several columns in one statement; the parameter order matches the %s order (SET columns first, then WHERE values).
cursor.execute(
"UPDATE customers SET name = %s, address = %s WHERE id = %s",
("John Smith", "New Road 1", 5))
conn.commit()Update Using Existing Values
You can reference a column's current value — useful for counters and adjustments.
cursor.execute(
"UPDATE products SET stock = stock - %s WHERE id = %s", (1, 42))
cursor.execute(
"UPDATE products SET price = price * 1.10 WHERE category = %s", ("books",))
conn.commit()Confirm the Effect
rowcount reports affected rows. Note MySQL counts a row as affected only if a value actually changed.
cursor.execute("UPDATE customers SET name = %s WHERE id = %s", ("Ann", 3))
conn.commit()
print("Rows changed:", cursor.rowcount) # 0 if the name was already 'Ann'Upsert: Insert or Update
To insert a row or update it if the key already exists, use ON DUPLICATE KEY UPDATE (requires a unique/primary key).
cursor.execute(
"INSERT INTO settings (user_id, theme) VALUES (%s, %s) "
"ON DUPLICATE KEY UPDATE theme = VALUES(theme)",
(7, "dark"))
conn.commit()Best Practices
- Never run an
UPDATEwithout aWHEREunless you mean to touch every row. - Preview with a matching
SELECTfirst on important tables. - Wrap related updates in a transaction and
rollback()on error. - Use
ON DUPLICATE KEY UPDATEfor clean insert-or-update logic.
Try It Yourself
Exercise 1: Raise every book's price by 10% in one statement.
Show solution
cursor.execute(
"UPDATE products SET price = price * 1.10 WHERE category = %s",
("books",))
conn.commit()Exercise 2: What happens if you run an UPDATE without a WHERE clause?
Show solution
Every row in the table is updated. Always scope updates with WHERE unless that is truly intended.
📘 Real-World Deep Dive
Knowing <strong>MySQL Update (MySQL)</strong> well is what turns MySQL from a curiosity into a daily tool — you'll reach for it in nearly every real project.
Real-Life Scenario
An end-to-end usage of MySQL Update that you'd actually see in a data pipeline or analytics notebook.
Real-Life Example
import mysql.connector
conn = mysql.connector.connect(database="shop")
cur = conn.cursor()
cur.execute("UPDATE users SET name = %s WHERE id = %s", ("Ada Lovelace", 1))
conn.commit(); print(cur.rowcount, "updated")Expected Output
(see source)Common mistakes
- Reading with the default cursor returns tuples — switch to
DictCursorfor named access. - Strings without parameter binding produce SQL-injection holes — never use f-strings in queries.
- Connecting through a long-lived daemon pool: make sure
pool_reset_connectionis on to avoid session state leakage. - Treating MySQL Update as a black box without reading the docs — the API has subtle defaults that bite when you scale.
🚀 Performance & Best Practices
- Use prepared statements (
cursor.executemany) for bulk inserts. - Fetch with
cursor.fetchmany(1000)in streams instead offetchall(). - Use
pymysql's server-side cursors for very large result sets. - When working with MySQL, prefer vectorised / batched operations over Python loops.
🧪 Try It Yourself
- Reproduce the snippet on a representative slice of your own data.
- Profile the snippet with
cProfileortimeitand find the single biggest improvement. - Generalise the snippet into a small, reusable function you can drop into future projects.