MySQL Insert

Understand the idea

Connector/Python uses %s placeholders for values regardless of whether the value is numeric or textual. Pass a tuple of values to execute rather than applying Python string interpolation. Commit after successful work, and retrieve the generated identifier through the cursor API in the same connection.

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)", ("Desk lamp", 8))
    connection.commit()
    print("Inserted id:", cursor.lastrowid)
except Exception:
    connection.rollback()
    raise
finally:
    cursor.close()
    connection.close()

Read the result

The output identifies the newly inserted product. Its exact identifier depends on previous inserts in your lab.

Try one change

Insert a second product, then query both records and confirm that their identifiers are distinct.

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.