Pagination with Flask-SQLAlchemy

It becomes tedious when a lot of posts are displayed on the same page all at once. It will be better to show a specific number of posts on a particular page and then display links below the page, directing to the other posts. Luckily flask sqlalchemy makes that very simple for us.

If you check the home route,(or what route you want to paginate) you will notice that sql alchemy is getting all the posts with the “posts.query.all()” method, instead you need to replace it with paginate method. “Posts.query.paginate()”.

So to print out the default 20 items that the paginate method displays, you can loop through the posts. see below

posts = Post.query.paginate()
for post in posts.items:
    print(post)

This will display 20 items/posts on the page and the rest will be on the next page.

By default, the paginate method has a page argument that has been set to 1 by default. Meaning in order to get the items on page 2 and so on, you’ll have to manually pass the page argument to the method and set it to the page you want to view. See below

posts = Post.query.paginate(page=2)
for post in posts.items:
    print(post)

posts.per_page #this gives you 20(how many posts can fit on a page)
posts.page #current page your'e on

If 20 is too much, you can rerun the query and specify a per page of your choice

posts = Post.query.paginate(per_page=2)
for post in posts.items:
    print(post)

You can basically input any digit for the page and per page inside the method

posts = Post.query.paginate(per_page=5, page=2)
for post in posts.items:
    print(post)

the above code will print 5 posts per page on the second page

If you want to see the total number of posts on all of the pages, you can use the “total” attribute

posts.total #this will print the total number of posts.

Now in your home route, instead of querying all the posts with “Post.query.all()”, you can use the paginate method to do the pagination with

Post.query.paginate(per_page=5)

Right now, this code will show 5 posts on the first page since we’re not specifying the page number. In order to get other pages you can do that by passing query parameters in the url. So to grab the page number you want, just grab that page from a query parameter in the url. see below…

page = request.args.get('page', 1, type=int) #1 is the default page. #type_int throws an error when we pass in any other data type.

Now in in the paginate method, you can do the following. See below

posts = Post.query.paginate(per_page=5, page=page)

Now you can make a change in your home.html template and change the loop displaying the posts form “for post in posts”, to “for post in posts.items”

Now that this is done, you have now implemented pagination in your app. Note that there are no links to go to the next page for now, but if you edit the url and pass in the proper page number as a parameter you will see the posts on that page. “https://localhost:5000/?page=5”. Per this example, this should go all the way to page 5.

Adding Pages to Link to Other Pages

Open the python interpreter and import your Posts from the models

posts = Posts.query.paginate(page=6, per_page=2)

for page in posts.iter_pages():
    print(page)

This page prints out a set of numbers like (1, 2, None, 4, 5, 6, 7, 8, 9, 10, None, 12, 13)

You may have noticed that when you are on a website with a lot of pages, they tend to show you what pages you’re currently on and then a few pages before and after withan ellipsis or sumn like that. That is what the None values represent in this case. It will make a lot more sense when we get to the practical application of it and render it in our template. But basically, those numbers are going to be links to other pages that we can navigate to.

In your template you can write a for loop that loops through the pages in the posts, and if the page num is not equal to the “None” value, you display the link, else you just replace it with an ellipsis. See below:

{% for page_num in posts.iter_pages() %}
    {% if page_num%}
      <a href="{{url_for('home', page=page_num)}}">{{page_num}}</a>
    {%else%}
    ...
    {%endif%}

{% endfor %}

Since this is a blog application, you can also add a useful feature where clicking on the username sends you to a new page where all those users posts will be displayed. Figure that out yourself, or refer to the code from the flask blog app.