Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

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) 
like image 702
Bobby Battista Avatar asked Dec 15 '15 21:12

Bobby Battista


People also ask

What does First () do in SQLAlchemy?

first() applies a limit of one within the generated SQL, so that only one primary entity row is generated on the server side (note this may consist of multiple result rows if join-loaded collections are present). Calling Query. first() results in an execution of the underlying query.

When should I use SQLAlchemy?

SQLAlchemy is the ORM of choice for working with relational databases in python. The reason why SQLAlchemy is so popular is because it is very simple to implement, helps you develop your code quicker and doesn't require knowledge of SQL to get started.

Should I use SQLAlchemy core or ORM?

If you want to view your data in a more schema-centric view (as used in SQL), use Core. If you have data for which business objects are not needed, use Core. If you view your data as business objects, use ORM. If you are building a quick prototype, use ORM.

Why is SQLAlchemy so slow?

At the ORM level, the speed issues are because creating objects in Python is slow, and the SQLAlchemy ORM applies a large amount of bookkeeping to these objects as it fetches them, which is necessary in order for it to fulfill its usage contract, including unit of work, identity map, eager loading, collections, etc.


2 Answers

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.

like image 51
Andrea Corbellini Avatar answered Sep 24 '22 12:09

Andrea Corbellini


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. 
like image 22
osman Avatar answered Sep 22 '22 12:09

osman