Implement server side processing for datatables in python with sqlite

Question

I have a Flask app and at some point a construct a complex query and display the results with datatables. This works fine, but I have a really large amount of rows so it takes a long time to render it. I have found out that I should implement side server processing for datatables but I can't figure out how. So far, I am sending the list of data from python into the template like this:

return render_template('show_entries.html', entries=meas[0])

where "meas[0]" contains the data from sqlite retrieved from the query. In "show_entries.html", I am rendering the table like this:

<table id="myTable" class="table table-striped" style="width:100%" >  
        <thead>  
          <tr>
        <th>Time</th>
        <th>Mean Current</th>
        <th>Vapour Pressure</th>
        <th>Mean Voltage</th>
        <th>Temperature</th>
        <th>Humidity</th>
        <th>Bar Pressure</th>
        <th>RPM</th>
        <th>Wind Sector</th>
        <th>Wind Speed</th>
        <th>Air Density</th>
        <th>DC Voltage</th>
        <th>Power Sector</th>
        <th>Furling Angle</th>
        <th>Yaw Angle</th>
      </tr>
    </thead>
    <tbody>
        {% for row in  entries  %} 
      <tr>
        <td>{{ row[1] }}</td>
        <td>{{ row[2] }}</td>
        <td>{{ row[3] }}</td>
        <td>{{ row[4] }}</td>
        <td>{{ row[5] }}</td>
        <td>{{ row[6] }}</td>
        <td>{{ row[7] }}</td>
        <td>{{ row[8] }}</td>
        <td>{{ row[9] }}</td>
        <td>{{ row[10] }}</td>
        <td>{{ row[11] }}</td>
        <td>{{ row[12] }}</td>
        <td>{{ row[13] }}</td>
        <td>{{ row[14] }}</td>
        <td>{{ row[15] }}</td>
      </tr>
      {% endfor %}
        </tbody>  
      </table>

and the javascript for datatables:

<script>
$(document).ready(function(){
    $('#myTable').dataTable();
});
</script>

As I have said, it displays the data correctly but takes a long time so I am asking if someone has made it work server side in a similar situation as I've tried some of the examples I found in the datatables documentation but I'm nowhere near it.

From a UI and performance perspective, its generally a bad idea to have a huge table on a page. Paginate it or do something else. Too much information on the page just makes people overwhelmed and takes too long to load. — reptilicus

Sergey Shubin Sergey Shubin · Accepted Answer · 2017-02-20T15:09:20

When you use DataTables your data fully preloads before JS code wraps it in table with headers, pagination, filter etc. This is where it takes a long time to load — before executing client-side script. So you cannot speed up it with some client-side feature.

I would advice to switch to server-side processing of table data instead of using client-side: it is more preferable if your application works with a lot of rows (more than several hundreds). There are flask plugins capable of generating tables with pagination, filters and search. Take a look at Flask-Admin, it can generate such views fully on server-side.

Implement server side processing for datatables in python with sqlite

1 Answers