Pagination

This recipe is a useful trick to minimize database access in case of pagination, e.g., when you need to display a list of rows from a database but you want to distribute the rows over multiple pages.

Start by creating a primes application that stores the first 1000 prime numbers in a database.

Here is the model db.py:

  1. db = DAL('sqlite://primes.db')
  2. db.define_table('prime', Field('value', 'integer'))
  3. def isprime(p):
  4.     for i in range(2, p):
  5.         if p%i==0: return False
  6.     return True
  7. if len(db().select(db.prime.id))==0:
  8.    p=2
  9.    for i in range(1000):
  10.        while not isprime(p): p+=1
  11.        db.prime.insert(value=p)
  12.        p+=1

Now create an action list_items in the “default.py” controller that reads like this:

  1. def list_items():
  2.     if len(request.args): page=int(request.args[0])
  3.     else: page=0
  4.     items_per_page=20
  5.     limitby=(page*items_per_page, (page+1)*items_per_page+1)
  6.     rows=db().select(db.prime.ALL, limitby=limitby)
  7.     return dict(rows=rows, page=page, items_per_page=items_per_page)

Notice that this code selects one more item than is needed, 20+1. The extra element tells the view whether there is a next page.

Here is the “default/list_items.html” view:

  1. {{extend 'layout.html'}}
  3. {{for i, row in enumerate(rows):}}
  4. {{if i==items_per_page: break}}
  5. {{=row.value}}<br />
  6. {{pass}}
  8. {{if page:}}
  9. <a href="{{=URL(args=[page-1])}}">previous</a>
  10. {{pass}}
  12. {{if len(rows)>items_per_page:}}
  13. <a href="{{=URL(args=[page+1])}}">next</a>
  14. {{pass}}

In this way we have obtained pagination with one single select per action, and that one select only selects one row more than we need.