pythonsqlalchemyflask-sqlalchemy

What is the difference between .one() and .first()


What is the difference between one and first methods in SQLAlchemy


Solution

  • Query.one() requires that there is only one result in the result set; it is an error if the database returns 0 or 2 or more results and an exception will be raised.

    Query.first() returns the first of a potentially larger result set (adding LIMIT 1 to the query), or None if there were no results. No exception will be raised.

    From the documentation for Query.one():

    Return exactly one result or raise an exception.

    and from Query.first():

    Return the first result of this Query or None if the result doesn’t contain any row.

    (emphasis mine).

    In terms of a Python list, one() would be:

    def one(lst):
        if not lst:
            raise NoResultFound
        if len(lst) > 1:
            raise MultipleResultsFound
        return lst[0]
    

    while first() would be:

    def first(lst):
        return lst[0] if lst else None
    

    There is also a Query.one_or_none() method, which raises an exception only if there are multiple results for the query. Otherwise it'll return the single result, or None if there were no results.

    In list terms, that'd be the equivalent of:

    def one_or_none(lst):
        if not lst:
            return None
        if len(lst) > 1:
            raise MultipleResultsFound
        return lst[0]
    

    Note that the Query API is now considered a legacy API; as of SQLAlchemy 2.0 you'd use the standard session.execute() or session.scalars() methods, which return a Result object, which then offers the same set of .one() / .first() and .one_or_none() methods.

    The implementation no longer adds a LIMIT 1 to the query, and instead relies on the underlying .fetchone() method of the cursor or connection to avoid fetching whole result sets. How many results are fetched from the database depends on the exact database driver and if this is an async or sync session.