Flask, json, render_template, form action, data tables

Question

I want this to happen: A user selects from a form dropdown, the form passes the variables to the Flask server app_route function which calls an sql which is filtered dynamically by those parameters entered, and returns a datatable to the browser.

In Flask, I set up an app_route that provides url_for a json file. The object returned is a json dict which is the result of an sql query filtered by the parameters from a form submitted by the user. The action function of the form posts to this function and returns the json dict url.

Datatable needs the a url for the data. It does not seem to allow me to use a jinja template variable. My conflict is that I need to both redirect/render template of the html page that has the datatable and return a url containing the son dict.

I want to render_template('the_page_with_datatable.html', my_local_json_dict_variable).

I can either render_template('the_page_with_datatable.html') or return(my_local_json_dict_variable) from the function called under the app_route called by the form submit and assign to the url_for location, but not both.

How is this done?

So I can already return a data frame_to_html using normal jinja variable but I particularly want to have the datatables functionality. I don't want to render any other kind of table. I can also render the datatable with static sql where I have use sql response to an api. Issue is the submit form action is returning one url, when I need two - the json url and the render_template url.

HTML/JS

<form class="form-inline" id="my_form" action="get_data" method="POST">
<div class="form-group">


              <select name="year" class="selectpicker form-control">
              {% for yr in years %}
                <option value="{{ yr }}">{{ yr }}</option>
              {% endfor %}
            </select>


        <select name="month" class="selectpicker form-control">
              {% for month in months %}
                <option value="{{ month }}">{{ month }}</option>
              {% endfor %}
            </select>

</div>
    <button type="submit" class="btn btn-default">Go</button>

</form>

<table id="values_table" class="table table-striped table-bordered" style="width:100%">

        <thead>

            <tr>

                <th>Name</th>
                <th>Number</th>
                <th>Date</th>
                <th>values_€</th>


            </tr>

        </thead>


    </table>




<script>

function setupData() {

    $(document).ready(function () {

       $('#values_table').DataTable( {
        dom: 'Bfrtip',


            "ajax": {


                "url": "/get_data",

                "dataType": "json",

                "dataSrc": "data",

                "contentType":"application/json"

            },

            "columns": [

                {"data": "PersonName"},
                {"data": "PersonNumber"},
                {"data": "Date"},
                {"data": "values_€"},

            ]

        });



    });

});
}

$( window ).on( "load", setupData );


</script>





Flask routes

#renders page with select form and datatable
@app.route("/values_select" , methods=['GET','POST'])
def values_select():
    years, months = api().values_select()
    return render_template('values_select.html', years=years, months=months)


#get json data for datatable to parse from url
@app.route("/get_data" , methods=['GET','POST'])
def get_data():
    year = request.form.get('year')
    month = request.form.get('month')
    data = assets_api().values(month, year)
    return jsonify(data=data)

djnz djnz · Accepted Answer · 2020-01-30T09:05:24

It will be best to have a second route that returns json, and utilise the ajax data source of datatables. A basic outline:

Your json route (dropdown as a request arg):

@app.route('/get_data')
def get_json():
    filter_val = request.args.get('filter')

    result = # Your query here (using filter_val, or all rows if none?)

    return jsonify(result)

Your current route will likely remain as-is, but:

def my_page():
    # ...
    return render_template('page.html')

And the js:

$('#table').DataTable( {
    ajax: {
        url: "{{ url_for('get_json') }}?filter=" + $("#dropdown").val(),
        dataSrc: ''
    },
    columns: [ ... ]
} );

This is explained in a lot more (better) detail on the datatables ajax guide. But I hope this is a start.

Edit:

One this is setup, you can handle the re-fetching of data slightly differently. You no longer need the form. You can handle either the click of a button, or or change of the dropdown (below). Calling the table.reload() function will refetch the data. Something like:

$("#the_dropdown").change(function() {
  table.reload();
});

Flask, json, render_template, form action, data tables

1 Answers