SQLAlchemy query to return only n results?

user_433737 0
Viewed 101010

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().

sqlalchemy
5 Answers

Alternative syntax

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

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

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.

user_53209060

In my case it works like

def get_members():
    m = Member.query[:30]
    return m
user_128165620
Related