In real-world applications, retrieving all rows from a database is rarely useful. The WHERE clause lets you filter records based on conditions. In this tutorial, you'll learn how to use WHERE in PostgreSQL queries via Python using the psycopg2 library.

Table of Contents

1. What You’ll Learn

2. Prerequisites

3. Setting Up the Environment

4. Using WHERE to Filter Data

5. Common WHERE Conditions

6. Full Example with Multiple Filters

7. Tips and Common Pitfalls

1. What You’ll Learn

• How to use the WHERE clause in SQL queries with Python

• How to pass dynamic values safely using parameters

• How to combine multiple conditions

• Best practices for query security and performance

✅ 2. Prerequisites

You’ll need:

• Python installed

• PostgreSQL installed and running

• A table (e.g., students) created and populated

• psycopg2 installed:

pip install psycopg2-binary

3. Setting Up the Environment

Assume a table named students:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    grade VARCHAR(10)
);

And some sample data:

INSERT INTO students (name, age, grade)
VALUES 
('Alice', 14, '8th'),
('Bob', 13, '7th'),
('Carol', 15, '9th'),
('David', 14, '8th');

4. Using WHERE to Filter Data

Example: Select students with age 14

import psycopg2

conn = psycopg2.connect(
    dbname="school",
    user="postgres",
    password="your_password",
    host="localhost",
    port="5432"
)

cur = conn.cursor()

cur.execute("SELECT * FROM students WHERE age = %s", (14,))
rows = cur.fetchall()

for row in rows:
    print(row)

cur.close()
conn.close()

✅ The %s placeholder is a parameter — it protects against SQL injection.

⚙️ 5. Common WHERE Conditions

Using Multiple Conditions

cur.execute("""
    SELECT * FROM students 
    WHERE age = %s AND grade = %s
""", (14, '8th'))

6. Full Example with Multiple Filters

import psycopg2

try:
    conn = psycopg2.connect(
        dbname="school",
        user="postgres",
        password="your_password",
        host="localhost",
        port="5432"
    )
    cur = conn.cursor()

    # Filter students with age 14 and grade '8th'
    cur.execute("""
        SELECT * FROM students
        WHERE age = %s AND grade = %s
    """, (14, '8th'))

    rows = cur.fetchall()

    print("Filtered Students:")
    for row in rows:
        print(f"ID: {row[0]}, Name: {row[1]}, Age: {row[2]}, Grade: {row[3]}")

    cur.close()
    conn.close()

except psycopg2.Error as e:
    print("Database error:", e)

7. Tips and Common Pitfalls

Conclusion

The WHERE clause is essential for precise data retrieval in SQL. With Python and psycopg2, it's easy to construct secure, efficient queries. Whether filtering by age, grade, or more complex criteria, WHERE gives you the flexibility and power to handle your data effectively.