88

I have the following query:

profiles = session.query(profile.name).filter(and_(profile.email == email, profile.password == password_hash))

How do I check if there is a row and how do I just return the first (should only be one if there is a match)?

Lukas Graf
  • 30,317
  • 8
  • 77
  • 92
Asken
  • 7,679
  • 10
  • 45
  • 77

4 Answers4

155

Use query.one() to get one, and exactly one result. In all other cases it will raise an exception you can handle:

from sqlalchemy.orm.exc import NoResultFound
from sqlalchemy.orm.exc import MultipleResultsFound

try:
    user = session.query(User).one()
except MultipleResultsFound, e:
    print e
    # Deal with it
except NoResultFound, e:
    print e
    # Deal with that as well

There's also query.first(), which will give you just the first result of possibly many, without raising those exceptions. But since you want to deal with the case of there being no result or more than you thought, query.one() is exactly what you should use.

Kapucko
  • 343
  • 3
  • 11
Lukas Graf
  • 30,317
  • 8
  • 77
  • 92
  • thanks! there was another small problem though. I'd added `profile.name` which only gave me a tuple... – Asken Aug 07 '13 at 18:15
  • 2
    Yes, when you ask specifically for a column of a mapped class with `query(Class.attr)`, SQLAlchemy will return a `sqlalchemy.util._collections.NamedTuple` instead of DB objects. – Lukas Graf Aug 07 '13 at 18:23
  • Be aware that first() applies a LIMIT, which depending on the complexity of your query, may turn part of your larger query into a subselect that everything else is JOINed against. – skyler Nov 01 '13 at 18:12
  • 3
    Is it possible to import the exceptions from `flask-sqlalchemy` (given `db = SQLAlchemy(app)`) or must I import it directly as you did? – Tjorriemorrie Dec 02 '15 at 14:00
  • 2
    @Tjorriemorrie you do need to import them from `sqlalchemy`. The `flask-sqlalchemy` module is just an integration package for Flask, it does not subclass or wrap any of `sqlalchemy`'s exceptions. – Lukas Graf Dec 02 '15 at 14:06
65

You can use the first() function on the Query object. This will return the first result, or None if there are no results.

result = session.query(profile.name).filter(...).first()

if not result:
    print 'No result found'

Alternatively you can use one(), which will give you the only item, but raise exceptions for a query with zero or multiple results.

from sqlalchemy.orm.exc import NoResultFound, MultipleResultsFound
try:
    result = session.query(profile.name).filter(...).one()
    print result
except NoResultFound:
    print 'No result was found'
except MultipleResultsFound:
    print 'Multiple results were found'
Mark Hildreth
  • 42,023
  • 11
  • 120
  • 109
  • Just noticed a typo in the second `except` - Multi**p**leResultsFound. Care to update it? – Hussain Nov 04 '15 at 10:10
  • @Hussain: that except is correct, in that `one()` is expecting exactly one result, and creates exceptions to cover both eventualities (none and many). As per below answer, `one_or_none()` would limit output to one (or none), and thus make this exception redundant. – CodeMantle Feb 14 '20 at 17:37
16

Assuming you have a model User, you can get the first result with:

User.query.first()

If the table is empty, it will return None.

Aldo Canepa
  • 1,791
  • 2
  • 16
  • 16
9

Use one_or_none(). Return at most one result or raise an exception.

Returns None if the query selects no rows.

Shoham
  • 7,014
  • 8
  • 40
  • 40