Python Tutorial
Python MySQL - Where
Filter rows with conditions — safely, using parameters.
Filtering with WHERE
The WHERE clause returns only rows that match a condition.
import mysql.connector
conn = mysql.connector.connect(
host="localhost", user="root", password="secret", database="mydatabase")
cursor = conn.cursor()
sql = "SELECT * FROM customers WHERE address = %s"
cursor.execute(sql, ("Highway 21",)) # note the trailing comma
for row in cursor.fetchall():
print(row)The values argument must be a tuple. A single value needs a trailing comma: ("Highway 21",). Without it Python treats the parentheses as grouping, not a tuple.
Comparison and Logical Operators
| Operator | Meaning |
|---|---|
| = , != , < , > , <= , >= | Compare values |
| AND / OR / NOT | Combine conditions |
| BETWEEN a AND b | Range (inclusive) |
| IN (...) | Match any in a list |
| IS NULL / IS NOT NULL | Test for missing values |
cursor.execute(
"SELECT * FROM orders WHERE amount BETWEEN %s AND %s AND status = %s",
(100, 500, "paid"))Pattern Matching with LIKE
% matches any sequence of characters, _ matches exactly one. Pass the pattern as a parameter.
cursor.execute(
"SELECT * FROM customers WHERE name LIKE %s", ("A%",)) # starts with A
cursor.execute(
"SELECT * FROM customers WHERE name LIKE %s", ("%son",)) # ends with 'son'Matching a List with IN
Build one placeholder per value so the query stays parameterized.
ids = [1, 3, 5]
placeholders = ", ".join(["%s"] * len(ids)) # "%s, %s, %s"
sql = f"SELECT * FROM customers WHERE id IN ({placeholders})"
cursor.execute(sql, ids)
print(cursor.fetchall())Only the fixed number of placeholders is built by string formatting; the actual values still go through parameters, so this stays injection-safe.
NULL Is Special
NULL means "unknown". It never equals anything, not even another NULL — always test it with IS NULL.
cursor.execute("SELECT * FROM customers WHERE address IS NULL")Best Practices
- Always pass filter values as parameters, never concatenate them.
- Remember the trailing comma for single-value tuples.
- Use
IS NULL/IS NOT NULLfor missing values. - Index columns you frequently filter on to keep WHERE queries fast.
Try It Yourself
Exercise 1: Select customers whose name starts with "A" using a safe parameter.
Show solution
cursor.execute(
"SELECT * FROM customers WHERE name LIKE %s", ("A%",))Exercise 2: Why can't you use = NULL to find rows with a missing value?
Show solution
NULL never equals anything, even NULL. Use IS NULL / IS NOT NULL instead.
📘 Real-World Deep Dive
Knowing <strong>MySQL Where (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 Where that you'd actually see in a data pipeline or analytics notebook.
Real-Life Example
SELECT id, name FROM users
WHERE email LIKE '%@example.com'
AND id BETWEEN 10 AND 1000
ORDER BY id DESC;Expected Output
(no output)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 Where 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.