sqlalchemy The ORM Accessing query results

Example

Once you have a query, you can do more with it than just iterating the results in a for loop.

Setup:

from datetime import date

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(Text, nullable=False)
    birthday = Column(Date)

# Find users who are older than a cutoff.
query = session.query(User).filter(User.birthday < date(1995, 3, 3))

To return the results as a list, use all():

reslist = query.all() # all results loaded in memory
nrows = len(reslist)

You can get a count using count():

nrows = query.count()

To get only the first result, use first(). This is most useful in combination with order_by().

oldest_user = query.order_by(User.birthday).first()

For queries that should return only one row, use one():

bob = session.query(User).filter(User.name == 'Bob').one()

This raises an exception if the query returns multiple rows or if it returns none. If the row might not exist yet, use one_or_none():

bob = session.query(User).filter(User.name == 'Bob').one_or_none()
if bob is None:
    create_bob()

This will still raise an exception if multiple rows have the name 'Bob'.