This chapter focuses solely on retrieving records from a database using SQLAlchemy. You’ll learn how to fetch single or multiple records efficiently using various querying methods.

Fetching a Single Record

To retrieve a single record, use query() and apply filters. For instance, to find a product by its name:

product = session.query(Product).filter(Product.name == "Laptop").first()

Here, .filter(Product.name == "Laptop") selects products with the name "Laptop," and .first() returns the first matching record or None.

Fetching Multiple Records

To retrieve multiple records, combine filter() with .all(). For example, to list all products:

products = session.query(Product).all()

To filter products, e.g., those priced above $1000, modify the query:

expensive_products = session.query(Product).filter(Product.price > 1000).all()

Using get() for Primary Key Lookups

For efficient retrieval by primary key, use get(). For example, fetching a product with ID 1:

product = session.query(Product).get(1)

Limiting Results

To limit the number of records retrieved, apply .limit(). For example, to fetch the first 3 products:

limited_products = session.query(Product).limit(3).all()

By mastering these techniques, you’ll efficiently retrieve the data you need, whether it’s a single record, multiple filtered results, or a limited subset.