How can I limit the number of returned results to only the 1000 latest entries with Flask / SQLAlchemy?

caching, flask, flask-sqlalchemy, python, sqlalchemy

Solution

If you slice a query in SQLAlchemy, it automatically limits the database result set fetched:

limitedQuery = q[:50]

If you were to get a count first, you can easily loop over chunked responses:

count = q.count()
for chunkstart in xrange(0, count, 50):
    CachedChunk += 1
    chunkend = min(chunkstart + 50, count)
    cache.set(CachedChunk, q[chunstart:chunkend])

Note that this does result in multiple queries to the database. Alternatively you can use a the `itertools.izip_longest()` function to produce groups of 50 items:

from itertools import izip_longest

for chunkOf50Results in izip(*[q.yield_per(50)]*50):
     CachedChunk += 1
     cache.set(CachedChunk, chunkOf50Results)

I used `.yield_per(50)` to limit row pre-fetching to the batchsize so you don't pre-fetch more than you need per batch.

The `izip_longest(*[iterable]*n)` trick gives you groups of size `n` out of a base iterator:

>>> import itertools
>>> list(itertools.izip_longest(*[iter(range(7))]*3))
[(0, 1, 2), (3, 4, 5), (6, None, None)]

Note that the last batch is padded with `None` values to fill out to the batch size.

Problem

I'm looking for something similar to how reddit/hackernews specifically (this seems a common approach by a lot of major sites) handle their 'new' listing. It appears to work like so: - when a new link is submitted, a certain number of the latest entries is grabbed - those queries are divided up by a PER_PAGE # and cached as cachekey = newestPage1,2,3,4 - clicking the next/previous buttons loads the next/prev cachekey My problem is: its difficult to find SQLalchemy/flask-sqlalchemy code for getting a query of only a fixed # of the latest entries. how do I say: ``` q = PostDB.query(order_by('creation_time').desc()).limit(1000) for chunkOf50Results in q: cache.set(CachedChunk+=1, chunkOf50Results) ``` ?

Original source