Python MySQL WHERE Clause – Filter Data Like a Pro!

When working with databases, it's common to fetch only specific records that match certain conditions. That’s where the WHERE clause comes in handy. In this blog, we’ll explore how to use the WHERE clause with Python and MySQL to retrieve filtered data from your database.

What is the WHERE Clause?

The WHERE clause is used in SQL to filter records that meet a specific condition. Without it, a SELECT query returns all rows from the table.

For example:

SELECT * FROM users WHERE age > 18;

This returns only users whose age is more than 18.

Setup: Python + MySQL

Before using WHERE, make sure you've:

• Installed mysql-connector-python
• Connected to your database

pip install mysql-connector-python

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="yourdbname"
)

cursor = mydb.cursor()

Example 1: Basic WHERE Clause

Let’s say you have a table called students and you want to get students from a specific city.

sql = "SELECT * FROM students WHERE city = %s"
val = ("Mumbai",)

cursor.execute(sql, val)

result = cursor.fetchall()

for row in result:
    print(row)

Explanation:

• %s is a placeholder for secure parameter substitution (prevents SQL injection).
• val is a tuple of values that match the placeholders.

Example 2: WHERE with Multiple Conditions

sql = "SELECT * FROM students WHERE city = %s AND marks > %s"
val = ("Delhi", 80)

cursor.execute(sql, val)

result = cursor.fetchall()

for row in result:
    print(row)

You can use:

• AND – both conditions must be true.
• OR – either condition can be true.
• LIKE – for pattern matching.
• IN – to match multiple values.

Using LIKE in WHERE Clause

sql = "SELECT * FROM students WHERE name LIKE %s"
val = ("%John%",)

cursor.execute(sql, val)

result = cursor.fetchall()

This retrieves students whose names contain "John".

Best Practices

1. Always use parameterized queries – Prevents SQL injection.
2. Test with different data – Ensure queries behave as expected.
3. Use LIMIT – To avoid fetching thousands of rows if not needed.

Real-World Use Case

On an education portal like onlinelearner.in, you can use the WHERE clause to:

• Get all users who enrolled in a specific course
• Fetch students with high quiz scores
• Show test results filtered by subject or date

Example:

sql = "SELECT * FROM test_results WHERE user_id = %s AND score >= %s"
val = (123, 80)