Filter data selected from a Table - Advanced

You're really getting the hang of this! SQLAlchemy also allows users to use conjunctions such as and_(), or_(), and not_() to build more complex filtering. For example, we can get a set of records for people in New York who are 21 or 37 years old with the following code:

select([census]).where(
  and_(census.columns.state == 'New York',
       or_(census.columns.age == 21,
          census.columns.age == 37
         )
      )
  )

An equivalent SQL statement would be,for example,

SELECT * FROM census WHERE state = 'New York' AND (age = 21 OR age = 37)

This exercise is part of the course

Introduction to Databases in Python

View Course

Exercise instructions

Import and_ from the sqlalchemy module.
Select all records from the census table.
Append a where clause to filter all the records whose state is 'California', and whose sex is not 'M'.
Execute stmt in the connection and iterate over the ResultProxy to print the age and sex columns from each record.

Hands-on interactive exercise

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

# Import and_
from ____ import ____

# Build a query for the census table: stmt
stmt = select(____)

# Append a where clause to select only non-male records from California using and_
stmt = stmt.where(
    # The state of California with a non-male sex
    ____(census.columns.state == ____,
         census.columns.sex != ____
         )
)

# Loop over the ResultProxy printing the age and sex
for result in ____:
    print(____, ____)

Edit and Run Code