SQLAlchemy query to return only n results?

Question

I have been googling and reading through the SQLAlchemy documentation but haven't found what I am looking for.

I am looking for a function in SQLAlchemy that limits the number of results returned by a query to a certain number, for example: 5? Something like first() or all().

score 287 · Accepted Answer · edited Mar 11 '21 at 13:58

287

for sqlalchemy >= 1.0.13 Use the limit method.

query(Model).filter(something).limit(5).all()

edited Mar 11 '21 at 13:58

Antti Haapala -- Слава Україні

129,958
22
279
321

answered Dec 17 '13 at 20:33

jkmacc

6,125
3
30
27

1

I prefer this method over the others mentioned, is leaner since the extra data isn't passed over the network... – Yaakov Bressler Nov 18 '20 at 18:53
Is `.all()` really necessary ? Because it works without it. – Takamura Jun 17 '22 at 14:40
1

If you want to index into the result like `results[2]`, `.all()` is necessary. If you're just iterating over the results, I think leaving it off returns an iterator, so it's not necessary in that case. – jkmacc Jun 17 '22 at 20:59

score 9 · Answer 2 · edited Oct 25 '16 at 07:48

9

Alternative syntax

query.(Model).filter(something)[:5].all()

edited Oct 25 '16 at 07:48

neer

4,031
6
20
34

answered Oct 25 '16 at 07:44

mcolak

609
1
7
13

4

SQLAlchemy==1.1.15, raise: `AttributeError: 'list' object has no attribute 'all'` – Waket Zheng Apr 17 '19 at 09:24
2

Use this query : query.(Model).filter(something).limit(5) It works fine in SQLAlchemy==1.1.15 – Rana May 20 '19 at 10:51
13

If you do [:5] I think the database would still pull all of the results and then you'd only use the last 5. Definitely better to use limit to prevent the database from returning extra data. – Brian Sizemore Jul 04 '20 at 21:00

Ahmad · Answer 3 · 2022-06-22T05:38:41.003

4

If you need it for pagination you can do like this:

query = db.session.query(Table1, Table2, ...).filter(...)

if page_size is not None:
   query = query.limit(page_size)
if page is not None:
   query = query.offset(page*page_size)
query = query.all()

Or if you query one table and have a model for it you can:

query = (Model.query(...).filter(...))
                   .paginate(page=start, per_page=size))

edited Jun 22 '22 at 05:38

answered Jun 22 '22 at 05:10

Ahmad

8,811
11
76
141

snakecharmerb · Answer 4 · 2022-08-24T14:01:39.843

Since v1.4, SQLAlchemy core's select function provides a fetch method for RDBMS that support FETCH clauses*. FETCH was defined in the SQL 2008 standard to provide a consistent way to request a partial result, as LIMIT/OFFSET is not standard.

Example:

# As with limit queries, it's usually sensible to order
# the results to ensure results are consistent.
q = select(tbl).order_by(tbl.c.id).fetch(10)

# Offset is supported, but it is inefficient for large resultsets.
q_with_offset = select(tbl).order_by(tbl.c.id).offset(10).fetch(10)

# A suitable where clause may be more efficient
q = (select(tbl)
       .where(tbl.c.id > max_id_from_previous_query)
       .order_by(tbl.c.id)
       .fetch(10)
)

The syntax is supported in the ORM layer since v1.4.38. It is only supported for 2.0-style select on models; the legacy session.query syntax does not support it.

q = select(Model).order_by(Model.id).fetch(10)

* Currently Oracle, PostgreSQL and MSSQL.

SQLAlchemy query to return only n results?

4 Answers4

Linked