← Back to Python Course | Chapter 15: Python & MySQL | Lesson 6 of 12

Python MySQL Select With a Filter

A WHERE clause added to a SELECT statement filters rows down to only those matching a given condition -- exactly like an if statement filters which branch of code runs, but evaluated by the database server itself, before any data is sent back to Python.

In this page:

  1. Basic WHERE Filtering
  2. Pattern Matching with LIKE
  3. Combining Conditions with AND / OR
  4. Filtering on NULL Values
  5. Why Parameterized Queries Matter

Basic WHERE Filtering

cursor.execute("SELECT * FROM customers WHERE address = %s", ("Park Lane 38",)) returns only the rows where the address column exactly matches the given value, instead of every row in the table.

Note: Always pass the comparison value through the parameterized tuple argument, never embedded directly in the SQL string, even for values that seem harmless.

Warning: Forgetting the trailing comma in a single-value tuple like ("value",) turns it into a plain string instead of a tuple, which raises a confusing error.

Example: Basic WHERE Filtering

python 
from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor()
cursor.execute("SELECT * FROM customers WHERE address = %s", ("Park Lane 38",))
print("Filtered by exact address match")

Pattern Matching with LIKE

LIKE combined with the % wildcard character enables partial text matching inside a WHERE clause -- 'Ap%' matches anything starting with Ap, '%way%' matches anything containing way anywhere, useful for search-style features.

Note: Use LIKE with leading and trailing % (like "%term%") to implement a basic case-insensitive substring search feature.

Warning: A LIKE pattern with a leading % (like "%way") cannot use a standard index efficiently, which can slow down searches significantly on very large tables.

Example: Pattern Matching with LIKE

python 
from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor()
cursor.execute("SELECT * FROM customers WHERE address LIKE %s", ("%way%",))
print("Matches any address containing 'way'")

Combining Conditions with AND / OR

Multiple conditions can be combined in a single WHERE clause using AND (all conditions must be true) or OR (at least one must be true), exactly mirroring how Python's own and/or operators combine boolean expressions.

Note: Use parentheses to group combined AND/OR conditions explicitly when mixing both in the same WHERE clause, to avoid ambiguity about which conditions group together.

Warning: MySQL evaluates AND with higher precedence than OR by default, just like Python -- an ungrouped mix of both can filter differently than intended.

Example: Combining Conditions with AND / OR

python 
from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor()
cursor.execute(
    "SELECT * FROM customers WHERE city = %s AND age > %s",
    ("Austin", 18),
)
print("Both conditions must be true")

Filtering on NULL Values

IS NULL and IS NOT NULL are the only correct ways to filter rows based on a column having (or not having) no value at all -- a plain = comparison against NULL never returns true in SQL, even when comparing NULL to NULL itself.

Note: Remember that in SQL, NULL represents "unknown," not "empty" -- this is why standard equality comparisons against it never work as expected.

Warning: WHERE column = NULL is a silent logical bug, not a syntax error -- it simply always returns zero rows, which can be confusing to debug.

Example: Filtering on NULL Values

python 
from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor()
cursor.execute("SELECT * FROM customers WHERE address IS NULL")
print("= NULL never matches; IS NULL is required")

Why Parameterized Queries Matter

Building a WHERE clause by directly inserting user input into an SQL string -- with an f-string or % formatting -- allows an attacker to inject their own SQL logic through that input, a vulnerability called SQL injection; parameterized %s placeholders prevent this entirely by keeping data and SQL structure strictly separate.

Note: Treat every single value that comes from outside your own code (user input, an API response, a file) as untrusted, and always pass it through a parameterized placeholder, never string concatenation.

Warning: Even values that seem obviously safe, like a numeric ID from a URL, should still go through a parameterized placeholder -- consistency prevents the one exception that becomes a real vulnerability.

Example: Why Parameterized Queries Matter

python 
from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor()
user_input = "Park Lane 38"
# Safe: value is passed separately, never concatenated into the SQL string
cursor.execute("SELECT * FROM customers WHERE address = %s", (user_input,))
print("Protected against SQL injection")
Common Mistakes
  1. Building the WHERE condition's value directly into the SQL string with an f-string instead of a %s placeholder, opening an SQL injection vulnerability -- the single most important thing to get right here.
  2. Using = to compare against NULL, which never matches in SQL -- IS NULL or IS NOT NULL must be used instead.
  3. Forgetting that string comparisons in a WHERE clause are typically case-insensitive by default in MySQL (depending on the column's collation), which can surprise developers used to case-sensitive comparisons in Python.
Chapter Summary
  • A WHERE clause filters which rows a SELECT statement returns, based on a condition.
  • Always pass WHERE clause values as a separate parameterized tuple with %s placeholders, never string-formatted directly into the SQL.
  • LIKE with % wildcards enables partial, pattern-based text matching inside a WHERE clause.
Browser Support

WHERE clause syntax is standard SQL and works identically across all mysql-connector-python versions.

🔒

Chapter Quiz — Complete all 12 topics to unlock

0/12 topics done

Complete these topics first:

← Previous Lesson Python MySQL Select From Table Next Lesson → Python MySQL Order By

Login to run this code

C/C++/Java/PHP execution requires a free account. Your code is saved — you'll land right back in the editor after logging in.