56

I'm designing an API with SQLAlchemy (querying MySQL) and I would like to force all my queries to have page_size (LIMIT) and page_number (OFFSET) parameters.

Is there a clean way of doing this with SQLAlchemy? Perhaps building a factory of some sort to create a custom Query object? Or maybe there is a good way to do this with a mixin class?

I tried the obvious thing and it didn't work because .limit() and .offset() must be called after all filter conditions have been applied:

def q(page=0, page_size=None):
    q = session.query(...)
    if page_size: q = q.limit(page_size)
    if page: q = q.offset(page*page_size)
    return q

When I try using this, I get the exception:

sqlalchemy.exc.InvalidRequestError: Query.filter() being called on a Query which already has LIMIT or OFFSET applied. To modify the row-limited results of a  Query, call from_self() first.  Otherwise, call filter() before limit() or offset() are applied.
Matt
  • 74,352
  • 26
  • 153
  • 180
Rob Crowell
  • 1,447
  • 3
  • 15
  • 25
  • 1
    Please do not edit a solution into your question. Instead, post it as a separate answer below. – Matt Apr 03 '16 at 08:46

3 Answers3

58

Try adding a first, required argument, which must be a group of query filters. Thus,

# q({'id': 5}, 2, 50)
def q(filters, page=0, page_size=None):
    query = session.query(...).filter_by(**filters)
    if page_size:
        query = query.limit(page_size)
    if page: 
        query = query.offset(page*page_size)
    return query

or,

# q(Model.id == 5, 2, 50)
def q(filter, page=0, page_size=None):
    query = session.query(...).filter(filter)
    if page_size:
        query = query.limit(page_size)
    if page: 
        query = query.offset(page*page_size)
    return query
pydsigner
  • 2,779
  • 1
  • 20
  • 33
  • 1
    By breaking it up into 2 pieces (get Query object, apply mandatory filters), we rely on the developer to remember to call q() on all of his queries. Results will still be generated if someone forgets to call q() before calling all(). – Rob Crowell Nov 06 '12 at 20:51
  • Then you must take, rather than a Query, a filter dictionary. But this will limit you somewhat. – pydsigner Nov 06 '12 at 21:05
  • 1
    @RobCrowell Three years later, I'm curious if this solved your problem or if you ended up doing something else (in which case it'd be nice to see you submit an answer about what you came up with)? – pydsigner Oct 22 '15 at 15:06
  • 3
    Shouldn't it be `query = query.offset((page*page_size) - page_size)`? – LondonAppDev Feb 23 '16 at 15:15
  • @MarkWinterbottom as you can see from the `page=0` in the function definition, I'm using 0-indexing. – pydsigner Feb 23 '16 at 18:56
  • In the 2nd example, if there's a `page` but no `page_size`, you will be multiplying by `None` which will raise a `TypeError` – mekarpeles May 12 '18 at 07:44
  • @mekarpeles Good catch, and that might be better handled with looking-before-leaping and throwing `TypeError` with a more relevant message. The alternative, of course, is to default, but `page_size` is a legitimate solo argument, resulting either in inadvisable hidden defaulting: `page * (page_size or 50)` or ugly argument embedding: `def q(filter, page=0, page_size=None, _default_page_size=50)`. – pydsigner May 18 '18 at 02:02
6

Not an option at the time of this question, since version 1.0.0 you can take advantage of Query events to ensure limit and offset methods are always called just before your query object is compiled, after any manipulation is performed by the users of your q function:

from sqlalchemy.event import listen


def q(page=0, page_size=None):
    query = session.query()
    listen(query, 'before_compile', apply_limit(page, page_size), retval=True)
    return query

def apply_limit(page, page_size):
    def wrapped(query):
        if page_size:
            query = query.limit(page_size)
            if page:
                query = query.offset(page * page_size)
        return query
    return wrapped
Daniele
  • 61
  • 1
  • 2
  • How do you do this in the post version 1.4 sqlalchemy, since it was deprecated https://docs.sqlalchemy.org/en/14/orm/events.html#sqlalchemy.orm.QueryEvents.before_compile – rickerp Oct 06 '21 at 18:38
5

You can call query.limit(None). to remove previously applied limit or offset.

pie
  • 49
  • 1
  • 1