Filter data selected from a Table - Simple

Having connected to the database, it's now time to practice filtering your queries!

As mentioned in the video, a where() clause is used to filter the data that a statement returns. For example, to select all the records from the census table where the sex is Female (or 'F') we would do the following:

select([census]).where(census.columns.sex == 'F')

In addition to == we can use basically any python comparison operator (such as <=, !=, etc) in the where() clause.

This exercise is part of the course

Introduction to Databases in Python

View Course

Exercise instructions

Select all records from the census table by passing in census as a list to select().
Append a where clause to stmt to return only the records with a state of 'New York'.
Execute the statement stmt using .execute() on connection and retrieve the results using .fetchall().
Iterate over results and print the age, sex and pop2000 columns from each record. For example, you can print out the age of result with result.age.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

# Create a select query: stmt
stmt = ____

# Add a where clause to filter the results to only those for New York : stmt_filtered
stmt = stmt.____

# Execute the query to retrieve all the data returned: results
results = ____

# Loop over the results and print the age, sex, and pop2000
for ___ in ____:
    print(result.age, ____, ____)

Edit and Run Code