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


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)


8/24/2012 3:14:07 PM

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.

8/24/2012 4:13:57 PM

Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow