MySQL Update

Understand the idea

The example atomically adds one stock unit to the first product by identifier. A separate application read followed by writing a calculated old value can lose concurrent changes. For a real request, use the actual validated product identifier and document whether zero matched rows means missing, unchanged, or a failed business condition.

Where to try it

Use a local Python environment with mysql-connector-python and your own separate MySQL lab server. Read the lab setup below before running. This example is not connected to this website’s database and cannot run in the restricted online Python tool.

A worked example

import os
import mysql.connector
connection = mysql.connector.connect(
    host=os.environ.get("DB_HOST", "127.0.0.1"),
    user=os.environ["DB_USER"],
    password=os.environ["DB_PASSWORD"],
    database=os.environ["DB_NAME"],
)
cursor = connection.cursor()
try:
    cursor.execute("UPDATE workshop_products SET stock = stock + %s WHERE product_id = %s", (1, 1))
    connection.commit()
    print("Affected rows:", cursor.rowcount)
except Exception:
    connection.rollback()
    raise
finally:
    cursor.close()
    connection.close()

Read the result

The row with identifier 1 gains one unit if it exists. The printed count helps check whether the target was present.

Try one change

Change the target to a missing identifier and decide how the caller should report the zero-row outcome.

Local lab setup

Use a local Python virtual environment with mysql-connector-python installed and a separate MySQL lab server. Run the create-database and create-table lessons first, then insert a few lab products. Set DB_HOST, DB_USER, DB_PASSWORD, and DB_NAME in the environment of the process that runs the script. These snippets are complete driver examples; they have not been run against your database.