Python MySQL: Using the WHERE Clause to Filter Data

The WHERE clause is one of the most powerful features in SQL. It allows you to filter records based on specific conditions, making it easier to retrieve only the data you need. In this tutorial, we’ll learn how to use the WHERE clause in MySQL queries with Python.

At The Coding College, our mission is to make learning programming easy and enjoyable for developers at all levels. Let’s dive in!

Prerequisites

Before we get started, ensure the following:

  • Python Installed: Download it from python.org.
  • MySQL Server Installed: Get it from MySQL official site.
  • MySQL Connector Installed: Install it via pip:
pip install mysql-connector-python

How to Use the WHERE Clause in Python MySQL

Step 1: Connect to the Database

Establish a connection to your MySQL database:

import mysql.connector

# Connect to MySQL database
db = mysql.connector.connect(
    host="localhost",       # Hostname
    user="your_username",   # MySQL Username
    password="your_password", # MySQL Password
    database="your_database"  # Database Name
)

print("Connected to the database!")

Replace your_username, your_password, and your_database with your MySQL credentials.

Step 2: Create a Cursor

Create a cursor to execute SQL queries:

cursor = db.cursor()

Step 3: Write and Execute a Query With WHERE

Use the WHERE clause to filter data based on a condition. For example, to retrieve users above 25 years of age:

sql = "SELECT * FROM users WHERE age > 25"
cursor.execute(sql)

# Fetch and print results
results = cursor.fetchall()
for row in results:
    print(row)

Step 4: Use Parameters to Prevent SQL Injection

It’s always good practice to use placeholders for dynamic values. This prevents SQL injection attacks:

sql = "SELECT * FROM users WHERE age = %s"
age = 30
cursor.execute(sql, (age,))

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

Step 5: Use Multiple Conditions

You can combine conditions with AND or OR for more complex queries:

# Using AND
sql = "SELECT * FROM users WHERE age > 25 AND city = %s"
city = "New York"
cursor.execute(sql, (city,))

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

# Using OR
sql = "SELECT * FROM users WHERE age > 25 OR city = %s"
cursor.execute(sql, (city,))

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

Full Python Code Example

Here’s a complete example of using the WHERE clause with Python and MySQL:

import mysql.connector

# Connect to MySQL database
db = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database"
)

cursor = db.cursor()

# Example 1: Simple WHERE clause
sql = "SELECT * FROM users WHERE age > 25"
cursor.execute(sql)

print("Users older than 25:")
results = cursor.fetchall()
for row in results:
    print(row)

# Example 2: Using parameters
sql = "SELECT * FROM users WHERE city = %s"
city = "San Francisco"
cursor.execute(sql, (city,))

print("\nUsers in San Francisco:")
results = cursor.fetchall()
for row in results:
    print(row)

# Example 3: Using AND and OR
sql = "SELECT * FROM users WHERE age > 30 AND city = %s"
cursor.execute(sql, (city,))

print("\nUsers older than 30 in San Francisco:")
results = cursor.fetchall()
for row in results:
    print(row)

# Close the connection
db.close()

Exercises

Exercise 1: Fetch Data Using One Condition

Write a Python script to retrieve all products in a products table where the price is greater than 50.

Exercise 2: Combine Multiple Conditions

Retrieve all customers from a customers table who are older than 40 and live in “Los Angeles”.

Exercise 3: Fetch Data With User Input

Write a script that asks the user for a city name and fetches all records from the users table for that city.

Why Choose The Coding College?

At The Coding College, we empower developers to learn Python and SQL integration through hands-on, practical tutorials. Whether you’re a beginner or advanced user, we’re here to support your learning journey.

Conclusion

Using the WHERE clause with Python MySQL lets you filter data effectively, making it easier to retrieve specific information from your database. By following the best practices, such as using placeholders, you can ensure your queries are secure and efficient.

Leave a Comment