DataTables server-side processing using Python (Flask/Postgresql)

Question

Firstly, here is what I'm using : Python 3, Flask, PostgreSQL, a bootstrap theme.

What I want to do ?

I have a table with near 34000 values. The problem, the page is loading very slowly cause of the number of values. How can I improve performance using Server-Side processing (or other) with Python and Flask ?

Code :

Here is a part of my main.py :

@login_required
def home():
    connect() # Connect to the PG database
    connect.cur.execute("""SELECT * FROM test""")
    test_execute = connect.cur.fetchall()

    count_equipement()

    return render_template('index.html',
    value=test_execute, 
    value2=count_equipement.nb_equipement,
    value3=check_ok.nb_ok,
    value4=check_ko.nb_ko)

test_execute fetch all the values of my table. In my index.html here is how I show the data :

<table class="table table-bordered" id="dataTable" width="100%" cellspacing="0">
  <thead>
    <tr>
      <th>First</th>
      <th>Second</th>
      <th>Third</th>
      <th>Fourth</th>
      <th>Fifth</th>
    </tr>
  </thead>
  <tfoot>
    <tr>
      <th>First</th>
      <th>Second</th>
      <th>Third</th>
      <th>Fourth</th>
      <th>Fifth</th>
    </tr>
  </tfoot>
  <tbody>
    {% for row in value %}
    <tr>
      <td>{{row[0]}}</td>
      <td><a href="{{ url_for('site', site_id=row[1]) }}">{{row[1]}}</a></td>
      <td>{{row[2]}}</td>
      <td>{{row[3]}}</td>
      <td>{{row[4]}}</td>                    
    </tr>
    {% endfor %}
  </tbody>
</table>

In my bootstrap theme, there is a .js to paginate correctly the table. Here is the result with 8 values :

How can I do to do a server-side processing ? I've already check this link but I don't think I can apply this in my case...

Answer 1

Here is the answer of my question. (comments are in french)

@app.route('/stack', )
@app.route('/stack/<int:page>', defaults={'page': 1})
@login_required
def stack(page):
    """
        retourne la page 1 par défaut, puis change selon le numéro /4 par ex
    """
    connect()
    connect.cur.execute("""SELECT COUNT(*) FROM mydatabase""")
    count = connect.cur.fetchall()
    count = count[0][0]

    check.count()
    PER_PAGE = 10

    offset = ((int(page)-1) * PER_PAGE)
    connect.cur.execute("""SELECT *
                        FROM mydatabase
                        ORDER BY table1 OFFSET %s LIMIT %s""" % (offset, PER_PAGE))

    solutions = connect.cur.fetchall()

    # Display a 409 not found page for an out of bounds request
    if not solutions and page != 1:
        return(render_template('404.html', errmsg="Requested page out of bounds"), 404)
    pagination = Pagination(page, PER_PAGE, count)
    return(render_template('index.html',
                           r=request,
                           value=solutions,
                           pagination=pagination))


def url_for_other_page(page):
    """
    récupère l'url
    """
    args = request.view_args.copy()
    args['page'] = page
    return url_for(request.endpoint, **args)
app.jinja_env.globals['url_for_other_page'] = url_for_other_page