Simple DataTables flask

Question

I have no experience in web development, and I would like to add data for a table of specified fields with flask, this is my app

from flask import *

from flask import jsonify

app = Flask(__name__)

@app.route('/page_test')                                                                                  
def page_test():
    meas = {"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}
    return jsonify(meas)

if __name__ == "__main__":
    app.run(debug=True)

and this is my html (templates/view.html)

<script type="text/javascript" charset="utf8" src="https://code.jquery.com/jquery-1.12.4.js"></script>
<script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>
<script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/buttons/1.5.1/js/dataTables.buttons.min.js"></script>
<script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/select/1.2.5/js/dataTables.select.min.js"></script>
<script type="text/javascript" charset="utf8" src="https://editor.datatables.net/extensions/Editor/js/dataTables.editor.min.js"></script>

<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>  
<script>
$(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"},
            {data: "VapourPressure"},
            {data: "MeanVoltage"},
            {data: "Temperature"},
            {data: "Humidity"},
            {data: "BarPressure"},
            {data: "RPM"},
            {data: "WindSector"},
            {data: "AirDensity"},
            {data: "VoltageDC"},
            {data: "PowerSec"},
            {data: "FurlingAngle"},
            {data: "YawAngle"}
        ]
    } );
});
</script>

accessing the url http://127.0.0.1:5000/page_test only have the json file is displayed.

How can I make the script read the data and display the table?

Solution 1

I think there are two things you need to do:

1. Datatables expects the data to be an array of objects. You can read more about the expected data structures here. Your meas needs to be a Python list, for example:

   meas = [{"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}]

2. Datatables, by default, expects the data to be in a data object. You have two options:

Option 1: Return the data in a data object like this:

return jsonify({'data': meas})

Option 2: Use the ajax.dataSrc option to tell Datatables where to find the data, for example:

$('#myTable').DataTable( {
    "processing": true,
    "ajax": {url: "/page_test", 
             dataSrc: ""
            },
    // add column definitions to map your json to the table
    .....

Solution 2

http://fb4demo.bitplan.com/datatable has a demo.

It uses https://github.com/WolfgangFahl/pyFlaskBootstrap4 as a library which is built on top of https://github.com/greyli/bootstrap-flask

As a committer of the library i suggest to look at the example which might make your life simpler (at least in the long run after getting aquainted with the base libraries).

E.g. https://github.com/WolfgangFahl/pyFlaskBootstrap4/blob/main/fb4_example/bootstrap_flask/templates/datatable.html

has the template in which the datatable activation is added:

{% block scripts %}
    {{ super() }}
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.23/css/jquery.dataTables.css">
    <script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.23/js/jquery.dataTables.js"></script>
    <script type="text/javascript">
    $(document).ready(function() {
        $('.table').DataTable();
    });
    </script>
{% endblock %}

The python code is shortened to:

 def datatable(self):
        '''
        test data table
        '''
        icons=BootstrapIcon.query.all()
        dictList=[]
        for icon in icons:
            dictList.append(icon.asDict())
        return render_template('datatable.html',listOfDicts=dictList)

You example would need:

    meas = [{"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}
   ]

Just add more records with the same dict keys and you'd have a real table. as a listOfDicts and you'd be set.

Comments

[lofihelsinki]

In what URL is templates/vew.html supposed to display? What happends if you point your server to 127.0.0.1:5000/view.html or 127.0.0.1:5000/view

[user1265067]

Thank you @K Thomgren, it still does not work. If I try render_template('view.html', table= jsonify({'data': meas})) instead, it now attempts to load the table, but gives an error. I tried to give data to ajax as "{{ table | safe }}" also did not work.

[K Thorngren]

What is the error? Looks like you have 15 columns defined in HTML but only 14 in Datatables. Looks like DT is missing the Wind Speed column. This could be the problem. Not sure what this is: "{{ table | safe }}".

[user1265067]

Thanks again @K Thomgren, I fixed the array, but still get the error was: "DataTables warning: table id=myTable - Ajax error. For more information about this error, please see datatables.net/tn/7" and the table|safe comes from github.com/orf/datatables

[user1265067]

Just an update I fixed datatables.net/tn/7 loading other scripts and now I have error number 1 ' Invalid JSON response'

[user1265067]

I solved it creating a route to view my html with render_template and a different route for page test. It worked even without the dataSrc, but your tip helped me to format the array properly. Thank you very much!

[DankMasterDan]

@user1265067 can you post your code. I am having trouble with the same thing