When to use SQLAlchemy.get () vs .filter (Foo.ID == primary_key_id) .first ()

Question

Just wondering when I want to use one against the other. How do they differ?

We have our system, such that we can do this:

my_user = User.query().filter(User.ID == 5).first()

or

my_user = User.query().get(5)

Answer 1

These two lines are one and the same. Only exceptions are different. Actually get()implemented on top one(). It would be a difference if yours filter()returned more than the result, but this is really impossible in your case.

By the way, SQL does not have a GET operation, it only has SELECT (with optional LIMIT).

---

sqlalchemy / orm / query.py :

def get(self, ident):
    ...
    return self._get_impl(ident, loading.load_on_ident)

sqlalchemy / orm / loading.py :

def load_on_ident(query, key,
                  refresh_state=None, lockmode=None,
                  only_load_props=None):
    ...
    try:
        return q.one()
    except orm_exc.NoResultFound:
        return None

q.one() q.one_or_none().

first() one_or_none()

def first(self):
    ...
    ret = list(self[0:1])
    if len(ret) > 0:
        return ret[0]
    else:
        return None


def one_or_none(self):
    ...
    ret = list(self)

    l = len(ret)
    if l == 1:
        return ret[0]
    elif l == 0:
        return None
    else:
        raise orm_exc.MultipleResultsFound(
            "Multiple rows were found for one_or_none()")

, first() SELECT LIMIT, one_or_none() SELECT. , , LIMIT, , , .

Answer 2

: get , SQLAlchemy.

sqlalchemy/orm/query.py:

    :meth:`~.Query.get` is special in that it provides direct
    access to the identity map of the owning :class:`.Session`.
    If the given primary key identifier is present
    in the local identity map, the object is returned
    directly from this collection and no SQL is emitted,
    unless the object has been marked fully expired.
    If not present,
    a SELECT is performed in order to locate the object.

, get - (.. SELECT), , :

sqlalchemy/orm/query.py:

    :meth:`~.Query.get` also will perform a check if
    the object is present in the identity map and
    marked as expired - a SELECT
    is emitted to refresh the object as well as to
    ensure that the row is still present.
    If not, :class:`~sqlalchemy.orm.exc.ObjectDeletedError` is raised.

Answer 3

The first ( .filter()) is more general: you can build any conditional expression for any set of columns. The latter is a shortcut for the usual case of primary key searches.

(note: this is the first impression, I did not use SQLAlchemy, although I have many years of programming experience)