Pagination Python Flask MySQL

Pagination is the process of dividing document into discrete pages. Using Python Flask you can create pagination or multi page result.  In your database create table with name “posts” with columns (id, title, content), as I used in this example. We want to display five(5) posts in a page.

  Using select statement we can get data from database. Below is code that you need to write to a tags where you want to display your posts. In MySQL database the LIMIT clause is used with the SELECT statement to restrict (limit) the number of rows in the result set.

OFFSET, specifies the offset of the first row to return. The offset of the first row is “0” not “1”.

  The LIMIT clause makes it easy for a pagination or multiple page result with MySQL. Returning a large number of records can impact on performance, refer to(https://www.w3schools.com/php/php_mysql_select_limit.asp).

cur=mysql.connection.cursor()
cur.execute("SELECT * FROM posts ORDER BY id DESC LIMIT 0,5 ")
cur.fetchall()
cur.close()

When the query above is executed, it will return the first 5 records in descending order of “id”.

Now, in a route “app” file, for example “app.py” use the code below, place where you want to display a result.

#Posts
@app.route('/posts', defaults={'page':1})
@app.route('/page/<int:page>')
def posts(page):
    limit=6
    offset=page*limit-limit 
    my_cur=mysql.connection.cursor()

    my_cur.execute("SELECT * FROM posts WHERE id")
    total_row=my_cur.rowcount
    total_page=math.ceil(total_row/limit)
    next=page+1
    prev=page-1
    
    cur=mysql.connection.cursor()
    result=cur.execute("SELECT * FROM posts ORDER BY id DESC LIMIT %s OFFSET %s",(limit,offset))
    result=cur.fetchall()
   
        
    if result>0:        
        return render_template('posts.html', result=result, page=total_page, next=next,prev=prev)
    else:
        msg='No Post Found'
        return render_template('posts.html', msg=msg)  

    #Close connection
    cur.close()   

Then, inside your ‘html’ file where posts display (i.e. posts.html), write the following code inside a tag where you want to show a pagination.

<nav class="blog-pagination justify-content-center d-flex">
                            <ul class="pagination">
                                <li class="page-item">
                                    {% if prev >=1 %}
                                    <a href="/page/{{prev}}" class="page-link" aria-label="Previous">
                                        <span aria-hidden="true">
                                            <span class="lnr lnr-chevron-left"></span>
                                        </span>
                                    </a>
                                    {% endif %}
                                </li>
                                {% for page in range(page) %}
                                <li class="page-item"><a href="/page/{{loop.result}}" class="page-link">{{loop.result}}</a></li>
                                {% endfor %}
                               
                                <li class="page-item">
                                    {% if next <=page %}
                                    <a href="/page/{{ next }}" class="page-link" aria-label="Next">
                                        <span aria-hidden="true">
                                            <span class="lnr lnr-chevron-right"></span>
                                        </span>
                                    </a>
                                    {% endif %}
                                </li>
                            </ul>
                        </nav>

Then, run your project to see the results.

* Restarting with stat

 * Debugger is active!

 * Debugger PIN: 143-353-661

 * Running on http://127.0.0.1:5000/ (Press CTRL+C to quit)

NOTE: you need to import math to a project (import math).


Login to comment


Comments