MySQL Delete

Understand the idea

The script inserts a disposable row, records its generated key, then deletes only that key. A parameter keeps the value separate from SQL structure. Commit after the required operations succeed. The common wrapper rolls back row changes on exceptions, but production deletion also needs business retention and authorization rules.

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("INSERT INTO workshop_products (name, stock) VALUES (%s, %s)", ("Disposable", 0))
    temporary_id = cursor.lastrowid
    cursor.execute("DELETE FROM workshop_products WHERE product_id = %s", (temporary_id,))
    print("Deleted rows:", cursor.rowcount)
    connection.commit()
except Exception:
    connection.rollback()
    raise
finally:
    cursor.close()
    connection.close()

Read the result

One row should be deleted, leaving previously stored lab products alone.

Try one change

Explain why deleting by the generated identifier is safer than deleting every row named Disposable.

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.