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)

Cet exercice fait partie du cours

Introduction to Databases in Python

Afficher le cours

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.

Exercice interactif pratique

Essayez cet exercice en complétant cet exemple de 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(____, ____)

Modifier et exécuter le code