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)
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.
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.
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.
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With