CODEWITHSUNDEEP
pythonsqlalchemyflaskflask-sqlalchemy
Asken
Asken

Reputation: 8041

Getting first row from sqlalchemy

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)?

Upvotes: 96

Views: 186922

Answers (4)

Aldo Canepa
Aldo Canepa

Reputation: 1999

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.

Upvotes: 15

Lukas Graf
Lukas Graf

Reputation: 32580

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.

Upvotes: 166

Shoham
Shoham

Reputation: 7294

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

Returns None if the query selects no rows.

Upvotes: 10

Mark Hildreth
Mark Hildreth

Reputation: 43071

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'

Upvotes: 68

Related Questions