SQL Alchemy querying and filtering

After learning how to map a class and create a session, the next step is querying and filtering data from a table. In SQLAlchemy 2.0, the recommended approach is to use the select() construct together with Session.execute() or Session.scalars().

The Query object

In SQLAlchemy, a Query object represents a database SELECT statement. Instead of writing raw SQL queries, SQLAlchemy lets you build queries using Python code. The ORM then generates the SQL statement behind the scenes.

For example, a Query object knows which columns exist in your mapped class and will automatically generate a SELECT statement to retrieve them.

Let’s revisit our mapped class from the previous topic:

from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import declarative_base

Base = declarative_base()

class Animals(Base):
    __tablename__ = "animals"

    id = Column(Integer, primary_key=True)
    petname = Column(String(30))
    age = Column(Integer)
    weight = Column(Integer)

This class tells SQLAlchemy to map the Python class Animals to a database table named animals. Each attribute corresponds to a column.

Creating a session

Before we can query data, we need a database connection and a session. The Session object is our main entry point for talking to the database.

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine("sqlite:///:memory:", echo=True)  
Base.metadata.create_all(engine)  

Session = sessionmaker(bind=engine, future=True)
session = Session()

We also add some sample data:

session.add_all([
    Animals(petname="Billy", age=1, weight=8),
    Animals(petname="Susan", age=4, weight=12)
])
session.commit()

Now our animals table has two rows.

Retrieving all rows

The simplest way to retrieve data is with the select() function to create the required query. In order to execute this query, it can be passed to scalars() or execute() method of the session object. scalars() returns an object with the first value of each row. If you want only the first value of the first row, then you can use scalar() instead.

from sqlalchemy import select

query = select(Animals)
rows = session.scalars(query).all()

Here, all() returns a list with a row object for each row. The generated SQL will look like this:

SELECT animals.id, animals.petname, animals.age, animals.weight
FROM animals

You can then loop through the results using a simple for loop:

for row in rows:
    print(f"Pet name: {row.petname}, age: {row.age}, weight: {row.weight}")

Each row is an instance of the Animals class, so you can access attributes like .petname or .age.

Selecting certain columns

Sometimes, you don’t need all columns — just specific ones. You can pass the attributes you want into select(). Then use execute() on the session object which returns a Result object, which functions as an iterable that returns Row objects with a similar interface to a named tuple.

query = select(Animals.petname, Animals.age)
results = session.execute(query).all()

This generates:

SELECT animals.petname, animals.age FROM animals

The result will be a list of tuples:

for petname, age in results:
    print(petname, age)

Output:

Billy 1
Susan 4

Here, each row is returned as a tuple containing only the requested columns.

If you want to know how many rows match your query, you can use func.count():

from sqlalchemy import func

query = select(func.count()).select_from(Animals)
count = session.scalar(query)
print(count)

Here, SQLAlchemy generates a SELECT COUNT(*) FROM animals query and the above snippet will print 2 which signifies the number of rows in the table.

Filtering rows

To filter data, add a .where() clause to the statement.

query = select(Animals).where(Animals.petname == "Billy")
result = session.scalars(query)

for animal in result:
    print(animal.petname, animal.age, animal.weight)

Generated SQL:

SELECT animals.id, animals.petname, animals.age, animals.weight
FROM animals
WHERE animals.petname = ?

The ? placeholder is bound to the value "Billy".

Output:

Billy 1 8

Using multiple conditions

You can pass multiple conditions to .where() or use boolean operators:

query = select(Animals.age, Animals.weight).where(
    Animals.age > 2,
    Animals.weight >= 8
)

result = session.execute(query)

for age, weight in result:
    print(f"Pet age: {age}, Pet weight: {weight}")

Only Susan matches the conditions, so the output is:

Pet age: 4, Pet weight: 12

Conclusion

You learned how to use the select() construct to generate an SQL SELECT statement, so you don't have to worry about building complex SQL queries. That there are several ways to do this.

Now you know how to retrieve all values from the table and access the returned objects and specify certain attributes. You can also provide table filtering that allows you to select the data that falls under certain specifications. Now, how about putting it all into practice with some exercises?