26

I am having trouble implementing pagination with Flask-SQLAlchemy or Flask-Pagination, either or. I am unsure how to initialize the pagination, setting pages, determining pages, offest, etc. I am coming from PHP, quite new to Python.

I am querying all the posts in my database

posts = Posts.query.order_by(Posts.time.desc()).all()

I have been looking at the following examples:

I am confused on really what to do, the information I am finding greatly differs between articles. It's left me with confusion and not knowing where to begin. I want to query all rows of the database table, limit the results to 20 and paginate. I'm not seeing this clearly.

Community
  • 1
  • 1

4 Answers4

61

I recommend using Flask-SQLAlchemy's pagination: http://flask-sqlalchemy.pocoo.org/2.1/api/?highlight=pagination#flask.ext.sqlalchemy.Pagination

There's a well-written example here: https://blog.miguelgrinberg.com/post/the-flask-mega-tutorial-part-ix-pagination

Here's the basic idea for the view:

@app.route('/myview/<int:page>',methods=['GET'])
def view(page=1):
    per_page = 10
    posts = Posts.query.order_by(Posts.time.desc()).paginate(page,per_page,error_out=False)
    return render_template('view.html',posts=posts)

And then for the template (I don't know your posts model so I made something up):

<html>
  <head>
    Posts
  </head>
  <body>

{% for post in posts.items %}
<p>
  {{ post.post_name }} post body: <b>{{ post.body }}</b>
</p>
{% endfor %}
{% if posts.has_prev %}<a href="{{ url_for('view', page=posts.prev_num) }}">&lt;&lt; Newer posts</a>{% else %}&lt;&lt; Newer posts{% endif %} | 
{% if posts.has_next %}<a href="{{ url_for('view', page=posts.next_num) }}">Older posts &gt;&gt;</a>{% else %}Older posts &gt;&gt;{% endif %}

  </body>
</html>
mechanical_meat
  • 163,903
  • 24
  • 228
  • 223
  • 1
    Minor change required - @app.route('/myview/', methods=['GET'], defaults={"page": 1}) @app.route('/myview/',methods=['GET']) def view(page): – userx Jun 06 '19 at 11:27
  • How to accept the sort column name and order (asc/desc) as query parameters? My UI view has controls on each column head to permit sorting on that column, ascending or descending. Makes a big difference for pagination results :) – chrisinmtown Oct 29 '21 at 20:24
  • @chrisinmtown: probably worth it's own question! Or you can just look at the many questions already about this! Here's one: https://stackoverflow.com/questions/24892035/how-can-i-get-the-named-parameters-from-a-url-using-flask – mechanical_meat Oct 29 '21 at 20:35
  • Sorry, poor wording. Should have written "How to pass sort column and order (asc/desc) strings from query parameters to the order_by() method?" – chrisinmtown Oct 29 '21 at 21:00
  • @chrisinmtown: oh, I really don't think that method accepts an argument for that. You can have an `if` block to handle. For example, `if args.sort_order == 'desc': # do something here`, and you get the idea. – mechanical_meat Oct 29 '21 at 21:20
  • Thanks but apparently order_by can accept quite a lot of parameters, also see https://stackoverflow.com/questions/4186062/sqlalchemy-order-by-descending altho I'm very concerned about how to ensure the query parameters are properly sanitized – chrisinmtown Oct 29 '21 at 21:39
  • Hahaha, nobody's saying it doesn't accept parameters. – mechanical_meat Oct 29 '21 at 21:48
6

Controller

@app.route('/', methods=['GET'], defaults={"page": 1}) 
@app.route('/<int:page>', methods=['GET'])
def index(page):
    page = page
    per_page = 2
    users = User.query.paginate(page,per_page,error_out=False)
    # print("Result......", users)
    return render_template("index.html", users=users)

in the View

{% for user in users.items %}
    <h1> {{ user.name }} </h1>
{% endfor %}

<nav aria-label="Page navigation example">
                <ul class="pagination">
                    {% if users.has_prev %}
                      <li class="page-item"> <a class="page-link" href="{{ url_for('index', page=users.prev_num) }}">Previous</a></li>
                    {% else %}
                      <li class="page-item"><a class="page-link btn disabled" href="#">Previous</a></li>
                    {% endif %}


                    {% if users.has_next %}
                      <li class="page-item"> <a class="page-link" href="{{ url_for('index', page=users.next_num) }}">Next</a></li>
                    {% else %}
                      <li class="page-item"><a class="page-link btn disabled" href="#">Next</a></li>
                    {% endif %}

                </ul>
              </nav>
user3186184
  • 61
  • 1
  • 1
3

I used @user3186184 code but I got this error:

TypeError: 'Pagination' object is not iterable

But when I add items to the end of these code:

users = User.query.paginate(page,per_page,error_out=False).items

My issue was fixed.

Abbas Jafari
  • 1,492
  • 2
  • 16
  • 28
-1

Here is the simplest answer to add pagination in flask

no_of_posts = 3
page = request.args.get("number")
    if page is None:
        page = 1
    else:
        page = int(page)
    allPosts = Posts.query.filter_by().all()
    length = len(allPosts)
    allPosts = allPosts[(page-1)*no_of_posts: page*no_of_posts]
    if page > 1:
        prev = page -1
    else:
        prev = None
    if page < math.ceil(length/no_of_posts):
        next = page + 1
    else:
        next = None

In the template

{% if prev %}
<a href="?number={{ prev }}">&laquo; Previous</a>
{% endif %}
{% if next %}
<a href="?number={{ next }}">Next &raquo;</a>
{% endif %}
Lakshyaraj Dash
  • 153
  • 1
  • 7
  • While this code snippet may be the solution, [including an explanation](//meta.stackexchange.com/questions/114762/explaining-entirely-‌​code-based-answers) really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. – holydragon Dec 14 '21 at 06:32