W3docs
en
Log in

MySQL Where

Learn how to use the MySQL WHERE clause in Python with parameterized queries, comparison operators, LIKE, IN, BETWEEN, NULL checks, and compound conditions.

The WHERE clause is the primary way to filter rows in a MySQL SELECT, UPDATE, or DELETE statement. This chapter shows how to use it from Python with mysql-connector-python, covering single-condition filters, comparison operators, LIKE, IN, BETWEEN, NULL checks, and compound AND/OR logic — all using parameterized queries to prevent SQL injection.

Prerequisites

Make sure you have the following in place before running the examples:

  • Python 3.x and a running MySQL server.
  • mysql-connector-python installed:
pip install mysql-connector-python

The examples assume this table and some sample rows:

CREATE TABLE IF NOT EXISTS customers (
  id      INT AUTO_INCREMENT PRIMARY KEY,
  name    VARCHAR(255) NOT NULL,
  address VARCHAR(255),
  age     INT
);

INSERT INTO customers (name, address, age) VALUES
  ('Alice',   'Oak Avenue 1',     30),
  ('Bob',     'Pine Street 42',   25),
  ('Charlie', 'Maple Road 7',     35),
  ('Diana',   'Oak Avenue 3',     28),
  ('Eve',     NULL,               22);

Why Use Parameterized Queries

Before any code examples, one rule: never build a WHERE condition by concatenating user-supplied strings directly into your SQL. This pattern is dangerous:

# NEVER do this — SQL injection risk
name = input("Enter name: ")
sql = "SELECT * FROM customers WHERE name = '" + name + "'"

If a user enters ' OR '1'='1, the query returns every row. Instead, always pass values through mysql-connector-python's parameterized interface:

sql = "SELECT * FROM customers WHERE name = %s"
mycursor.execute(sql, (name,))

The connector escapes the value safely before it reaches the database. The placeholder is always %s regardless of the column's data type (integer, string, date, and so on).

Filtering by an Exact Value

The most common use case: retrieve rows where a column equals a specific value.

import mysql.connector
from mysql.connector import Error

try:
    mydb = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword",
        database="mydatabase"
    )
    mycursor = mydb.cursor()

    sql = "SELECT * FROM customers WHERE name = %s"
    val = ("Alice",)

    mycursor.execute(sql, val)
    results = mycursor.fetchall()

    for row in results:
        print(row)

except Error as e:
    print(f"Error: {e}")

finally:
    if mydb.is_connected():
        mycursor.close()
        mydb.close()

Example output:

(1, 'Alice', 'Oak Avenue 1', 30)

Note that val must be a tuple even when there is only one value — hence the trailing comma in ("Alice",).

Comparison Operators

The WHERE clause supports all standard SQL comparison operators:

OperatorMeaningExample condition
=Equalage = 30
<> or !=Not equalage <> 30
>Greater thanage > 25
>=Greater than or equalage >= 28
<Less thanage < 30
<=Less than or equalage <= 30

Example: Rows where age is greater than 28

import mysql.connector
from mysql.connector import Error

try:
    mydb = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword",
        database="mydatabase"
    )
    mycursor = mydb.cursor()

    sql = "SELECT name, age FROM customers WHERE age > %s"
    val = (28,)

    mycursor.execute(sql, val)
    for row in mycursor.fetchall():
        print(row)

except Error as e:
    print(f"Error: {e}")

finally:
    if mydb.is_connected():
        mycursor.close()
        mydb.close()

Example output:

('Alice', 30)
('Charlie', 35)

Wildcard Matching with LIKE

LIKE matches patterns within string columns. Two wildcard characters are available:

  • % — matches zero or more characters.
  • _ — matches exactly one character.
import mysql.connector
from mysql.connector import Error

try:
    mydb = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword",
        database="mydatabase"
    )
    mycursor = mydb.cursor()

    # Find customers whose address starts with "Oak"
    sql = "SELECT name, address FROM customers WHERE address LIKE %s"
    val = ("Oak%",)

    mycursor.execute(sql, val)
    for row in mycursor.fetchall():
        print(row)

except Error as e:
    print(f"Error: {e}")

finally:
    if mydb.is_connected():
        mycursor.close()
        mydb.close()

Example output:

('Alice', 'Oak Avenue 1')
('Diana', 'Oak Avenue 3')

LIKE is case-insensitive on utf8mb4 columns by default. Use LIKE BINARY if you need case-sensitive matching.

Matching Multiple Values with IN

IN tests whether a column value appears in a list. It is equivalent to chaining multiple OR conditions but far more readable.

import mysql.connector
from mysql.connector import Error

try:
    mydb = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword",
        database="mydatabase"
    )
    mycursor = mydb.cursor()

    names = ("Alice", "Charlie", "Eve")
    # Build one %s placeholder per value
    placeholders = ", ".join(["%s"] * len(names))
    sql = f"SELECT name, age FROM customers WHERE name IN ({placeholders})"

    mycursor.execute(sql, names)
    for row in mycursor.fetchall():
        print(row)

except Error as e:
    print(f"Error: {e}")

finally:
    if mydb.is_connected():
        mycursor.close()
        mydb.close()

Example output:

('Alice', 30)
('Charlie', 35)
('Eve', 22)

Because the number of IN values may vary at runtime, the pattern above builds the placeholder string dynamically (", ".join(["%s"] * len(names))). This keeps parameterization intact regardless of list length.

Range Filtering with BETWEEN

BETWEEN selects rows where a column value falls within an inclusive range:

import mysql.connector
from mysql.connector import Error

try:
    mydb = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword",
        database="mydatabase"
    )
    mycursor = mydb.cursor()

    sql = "SELECT name, age FROM customers WHERE age BETWEEN %s AND %s"
    val = (25, 30)

    mycursor.execute(sql, val)
    for row in mycursor.fetchall():
        print(row)

except Error as e:
    print(f"Error: {e}")

finally:
    if mydb.is_connected():
        mycursor.close()
        mydb.close()

Example output:

('Alice', 30)
('Bob', 25)
('Diana', 28)

BETWEEN 25 AND 30 includes both endpoint values (25 and 30). It works with dates and strings as well as numbers.

Checking for NULL Values

A NULL value means the field has no data. You cannot test for NULL with = — you must use IS NULL or IS NOT NULL.

import mysql.connector
from mysql.connector import Error

try:
    mydb = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword",
        database="mydatabase"
    )
    mycursor = mydb.cursor()

    # Find customers with no address recorded
    sql = "SELECT name FROM customers WHERE address IS NULL"
    mycursor.execute(sql)

    for row in mycursor.fetchall():
        print(row)

except Error as e:
    print(f"Error: {e}")

finally:
    if mydb.is_connected():
        mycursor.close()
        mydb.close()

Example output:

('Eve',)

IS NULL and IS NOT NULL take no parameters, so no second argument is passed to execute().

Compound Conditions with AND and OR

Combine multiple conditions in one WHERE clause using AND (all conditions must be true) and OR (at least one condition must be true).

AND example

import mysql.connector
from mysql.connector import Error

try:
    mydb = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword",
        database="mydatabase"
    )
    mycursor = mydb.cursor()

    # Customers on "Oak Avenue" who are older than 28
    sql = "SELECT name, address, age FROM customers WHERE address LIKE %s AND age > %s"
    val = ("Oak%", 28)

    mycursor.execute(sql, val)
    for row in mycursor.fetchall():
        print(row)

except Error as e:
    print(f"Error: {e}")

finally:
    if mydb.is_connected():
        mycursor.close()
        mydb.close()

Example output:

('Alice', 'Oak Avenue 1', 30)

Diana lives on Oak Avenue but is 28, so she does not satisfy age > 28.

OR example

import mysql.connector
from mysql.connector import Error

try:
    mydb = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword",
        database="mydatabase"
    )
    mycursor = mydb.cursor()

    # Customers named Alice OR younger than 25
    sql = "SELECT name, age FROM customers WHERE name = %s OR age < %s"
    val = ("Alice", 25)

    mycursor.execute(sql, val)
    for row in mycursor.fetchall():
        print(row)

except Error as e:
    print(f"Error: {e}")

finally:
    if mydb.is_connected():
        mycursor.close()
        mydb.close()

Example output:

('Alice', 30)
('Eve', 22)

Use parentheses to control precedence when mixing AND and OR: (a OR b) AND c behaves differently from a OR (b AND c).

Using WHERE with UPDATE and DELETE

The WHERE clause is equally critical in UPDATE and DELETE statements. Without it, the statement affects every row in the table.

import mysql.connector
from mysql.connector import Error

try:
    mydb = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword",
        database="mydatabase"
    )
    mycursor = mydb.cursor()

    # Update only Alice's address
    sql = "UPDATE customers SET address = %s WHERE name = %s"
    val = ("New Street 10", "Alice")
    mycursor.execute(sql, val)
    mydb.commit()
    print(mycursor.rowcount, "row(s) updated")

except Error as e:
    print(f"Error: {e}")
    mydb.rollback()

finally:
    if mydb.is_connected():
        mycursor.close()
        mydb.close()

Example output:

1 row(s) updated

Always double-check your WHERE condition with a SELECT first before running an UPDATE or DELETE. A missing or wrong condition on a production table can be difficult to reverse. See MySQL Update and MySQL Delete for more details.

Fetching One Row vs All Rows

After execute(), choose how many rows to retrieve:

MethodReturnsUse when
fetchone()First matching row (or None)You expect at most one result, e.g. lookup by primary key
fetchmany(n)Up to n rowsPagination or limited previews
fetchall()All matching rows as a listSmall result sets where loading all rows at once is fine
mycursor.execute("SELECT * FROM customers WHERE age > %s", (25,))

# Fetch only the first result
first = mycursor.fetchone()
print(first)  # (1, 'Alice', 'Oak Avenue 1', 30)

For large result sets, prefer fetchmany() in a loop or use a server-side cursor (MySQLCursorBuffered) to avoid pulling all rows into memory at once.

Common Mistakes to Avoid

Using = to check for NULL. WHERE address = NULL never returns any rows; always use IS NULL.

Forgetting the trailing comma in a single-value tuple. Writing val = ("Alice") creates a string, not a tuple. Write val = ("Alice",).

String-formatting values into SQL. F-strings and % formatting bypass parameterization. Pass values as the second argument to execute().

Omitting WHERE on UPDATE or DELETE. Without a WHERE clause, every row in the table is affected.

Using Python None where SQL NULL is needed. mysql-connector-python maps Python None to SQL NULL automatically, so mycursor.execute("UPDATE customers SET address = %s WHERE id = %s", (None, 1)) sets address to NULL correctly.

What to Do Next

Was this page helpful?