Python MySQL - Where Clause

WHERE clause is used to filter records in a MySQL table based on a specified condition. When working with MySQL in Python, WHERE clause is commonly used with SELECT, UPDATE, and DELETE statements to retrieve or modify only the required rows.

Syntax

The general syntax of the SQL WHERE clause is:

SELECT column_name(s)
FROM table_name
WHERE condition;

You can use comparison operators such as: =, != or <>, >, <, >=, <=, AND, OR, IN, LIKE, BETWEEN

Sample Table

The following Student table is used in the examples below:

Example 1: Select Records Using WHERE

The example below retrieves students whose marks are greater than or equal to 80.

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="college"
)

cursor = conn.cursor()
query = "SELECT * FROM Student WHERE Marks >= 80"
cursor.execute(query)

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

conn.close()

Output

(1, 'John', 'CSE', 85)
(3, 'Michael', 'CSE', 91)
(5, 'Daniel', 'IT', 88)

Example 2: Filter Records Using Multiple Conditions

The example below retrieves students who belong to the CSE department and have marks greater than 80.

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="college"
)

cursor = conn.cursor()

query = """
SELECT Name, Marks
FROM Student
WHERE Course = 'CSE' AND Marks > 80
"""

cursor.execute(query)
for row in cursor.fetchall():
    print(row)

conn.close()

Output

('John', 85)
('Michael', 91)

Example 3: Using WHERE with User Input (Parameterized Query)

Instead of hardcoding values, you can safely pass user input using placeholders.

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="college"
)

cursor = conn.cursor()
course = "IT"
query = "SELECT * FROM Student WHERE Course = %s"
cursor.execute(query, (course,))

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

conn.close()

Output

(2, 'Emma', 'IT', 72)
(5, 'Daniel', 'IT', 88)