Home > Enterprise >  SQLalchemy filter query only returning first row
SQLalchemy filter query only returning first row

Time:04-22

I'm having an issue with an SQLalchemy query. When i query a specific table it returns 7 items but when I iterate over it, it only finds one item.

Here is the query;

seasons = db.session.query(Seasons).filter(Seasons.tmdb_id == tmdb_id)

I know it returns 7 items because I immediately type the following and it prints the numeber "7";

print(seasons.count())

However when I try to iterate over this seasons object like this expecting to get 7 names, I only get one row with one name.

for item in seasons:
    print(item.name)

Any idea why?

CodePudding user response:

Because the count() function returns a int value. Take a look: https://docs.sqlalchemy.org/en/14/orm/query.html#sqlalchemy.orm.Query.count

SELECT count(1) AS count_1 FROM (
    SELECT <rest of query follows...>
) AS anon_1

The above SQL returns a single row, which is the aggregate value of the count function; the Query.count() method then returns that single integer value.

So, you should to use Query(...).all() to iterate on items.

then use if to check if has data:

seasons = query(...).all()

if seasons: 
    for item in seasons:
        print(item.name)

CodePudding user response:

if you wanna get all rows you Should use .all() like this

seasons = db.session.query(Seasons).filter(Seasons.tmdb_id == tmdb_id).all()

but if you need just first row use this you Should use first like this

seasons = db.session.query(Seasons).filter(Seasons.tmdb_id == tmdb_id).first()

when your using ".all()" returns a list of querysets so you can just check that using "len()" but when you are using "first()" returns a queryset

  • Related