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

Question

Just curious about when I would want to use one vs the other. How are they different?

We have our system set up such that we can do this:

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

or

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

Answer 1

Those two lines are the same thing. Only exceptions raised differ. In fact, get() is implemented on top of one(). There would be a difference if your filter() returned more than a result, but this is indeed not possible 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() in turn calls q.one_or_none().

Now compare first() with 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()") 

Therefore, first() executes a SELECT with a LIMIT, one_or_none() executes an unlimited SELECT. But, as we already said, either with or without LIMIT the result of the query cannot change, therefore the two are equivalent.

Answer 2

One important addition to Andrea Corbellini's answer: get can provide a performance boost by retrieving the object from memory if it already exists in the SQLAlchemy session.

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. 

Additionally, get will do a database I/O (i.e. SELECT statement) to refresh the object, if it has expired within the session:

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.