Server side processing with datatables and flask

tzoukritzou picture tzoukritzou · Feb 25, 2017 · Viewed 10.8k times · Source

I'm trying to implement server side processing with data from sqlite on my Flask app. I am a newbie, so I can't figure out what's wrong. So far I have come to this:

HTML:

<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> 
</table>  

Javascript:

$(document).ready(function() {
    $('#myTable').DataTable( {
        "processing": true,
        "serverSide": true,
        "ajax": "/page_test"
    } );
});

View function:

@app.route('/page_test')
def page_test():
    data = json.dumps(meas[2])
    print data
    return data

meas[2] is my dict:

[dict((c.description[i][0], value) \
        for i, value in enumerate(row)) for row in c.fetchall()]

In "print data" everything is printed fine, like this:

{"MeanCurrent": 0.05933, "Temperature": 15.095, "YawAngle": 0.0, "MeanVoltage": 0.67367, "VoltageDC": 3.18309, "PowerSec": 0.06923, "FurlingAngle": -0.2266828184, "WindSpeed": 1.884, "VapourPressure": 1649.25948, "Humidity": 0.4266, "WindSector": 0, "AirDensity": 1.23051, "BarPressure": 1020.259, "time": "2015-04-22 20:58:28", "RPM": 0.0, "ID": 1357}

This is multiplied by the number of rows.

However, when I run the app and insert the query, the table is displayed with only the <th> tags and there is "Processing..." written on top of the table with no data displayed. At the terminal of my flask app, a huge string is displayed, and this is a small sample:

/page_test?draw=2&columns%5B0%5D%5Bdata%5D=0&columns%5B0%5D%5Bname%5D=&columns%5B0%5D%5Bsearchable%5D=true&columns%5B0%5D%5Borderable%5D=true&columns%5B0%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B0%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B1%5D%5Bdata%5D=1&columns%5B1%5D%5Bname%5D=&columns%5B1%5D%5Bsearchable%5

And here is a screenshot:

screenshot from web app

Every time I click on the th tags, the same string comes again. It seems like I'm missing something important but as this is my first app, I can't figure out what it is. Any suggestion for modification of the code would be appreciated.

Answer

abigperson picture abigperson · Feb 26, 2017

Server-side processing is a setting that requires you to have a database script capable of replicating a lot of the core functionality of DataTables on your own server/database to manage very large sets of data.

All the information that is being passed to your script (e.g. that long string of info) are the inputs you need to use to query the database to return a result for DataTables to render.

If you would prefer DataTables to load the data from your Flask endpoint and then manage all the processing internally make these modifications: remove the serverSide setting and add column configurations so your data ends up in the right place:

Javascript:

$(document).ready(function() {
    $('#myTable').DataTable( {
        "processing": true,
        "ajax": "/page_test",
        // add column definitions to map your json to the table
        "columns": [
            {data: "time"},
            {data: "MeanCurrent"},
            ...
        ]
    } );
});

DataTable Initialization Options: if you click the "columns" button it shows you the various configurations each "column" accepts, whether it is sortable, orderable, custom rendering, etc...

Python:

from flask import jsonify

@app.route('/page_test')
def page_test():
    return jsonify(meas[2])