Exercise

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)

Instructions

100 XP
  • 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.