MySQL Where

Understand the idea

The stock threshold is supplied as a driver parameter. An index can help some filters, but performance depends on data size and selectivity. Keep the result order explicit so repeated runs do not depend on incidental storage order. A valid query can still represent the wrong business rule, so test boundaries.

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("SELECT name, stock FROM workshop_products WHERE stock >= %s ORDER BY product_id", (5,))
    for row in cursor:
        print(row)
except Exception:
    connection.rollback()
    raise
finally:
    cursor.close()
    connection.close()

Read the result

Only products with stock of at least five are printed. A product at exactly five is included.

Try one change

Test counts below, equal to, and above the boundary using disposable lab rows.

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.