Populate table using query parameters from external form

nat5142

Currently I have a very large database, for which I've created a query function in Python's Flask microframework. The function accepts user input query parameters from a form on my webpage. Upon form submission, the form data is sent to one Flask route, where the query parameters are stored for use in a separate route. I do not intend to return any data to jQuery from that Flask route. Instead, I'd like to create a DataTable on the same page as the form after the data is processed and the route returns to the client. I'm implementing ajax for my query due to the magnitude of my database (>100 million records).

I've tried calling ajax from DataTables and sending the request to a different Flask route, which re-establishes the original user-input form parameters, and queries the database accordingly. I plan to return the data in the form of a JSON object from that route to draw the DataTable, but I'm receiving a 500 error every time. I think I'm just misunderstanding the way that DataTables will interact with the sever.

My first route, which accepts submitted form data from the user:

views.py

@app.route('/params', methods=['POST'])
def params():
    params = request.form
    form = dict((k,v) for k,v in params.items() if v)
    session['form'] = form
    recordsFiltered = filter_records(form=form) #a separate function I wrote
                                       #it counts total records in db given query parameters
    session['recordsFiltered'] = recordsFiltered
    return jsonify({'placeholder': 'hello'}) 
                #don't know what to return here if
                #nothing from this route needs to be sent
                #back to ajax

The jQuery file which will draw the DataTable:

submit_query.js

$(document).ready(function() {
    $('.submit_button').on('click', function(event) {
        $.ajax({
            url: '/params',
            data: $('form').serializeArray(),
            type: 'POST',
            success: ????????
                // If there's no data to return from the
                // '/params' route, what goes here?
                // Is this where I call the DataTables instance?
                // I've tried the following:
            success: function() {
                $('#table').DataTable({
                    bServerSide: true,
                    ajax : {
                        'url': '/table_data' //second flask route, shown below
                        'type': 'POST'
                    },
                    columns: [ ... ],
                    'columnDefs': [ ... ],
                })
            },
        })
    });
    // or do I call DataTables here? Will jQuery
    // work sequentially and only display the
    // DataTable after submit if I do it this way?
});

And my second Flask route, which I've tried to call from DataTables in the jQuery script, 'submit_query.js':

views.py

@app.route('/table_data', methods=['POST'])
def table_data():
    form = session.get('form', None)
    recordsFiltered = session.get('recordsFiltered', None)
    ajax = request.json
    offset = ajax['start']
    limit = ajax['length']
    query = #some query that incorporates start and length as OFFSET and LIMIT
    #   #   #   #   #   #   #   #
    # alter query results to make them JSON serializable here
    #   #   #   #   #   #   #   #
     
    query_results = [{"key1":"foo", "key2":"bar"},
            {"key1":"hello", "key2":"goodbye"},
            ... ] #query results in JSON serializable form
    
    recordsTotal = "SELECT count(*) FROM table"
    
    response = {"draw": ajax_request['draw'], "recordsFiltered": recordsFiltered,
            "recordsTotal": recordsTotal, "data": query_results}
    
    return jsonify(response)

Can anybody help me identify where I'm going wrong in my server communication structure here?

kthorngren

Here is my understanding of what your requirements:

1. User input form to choose specific data to display - time range, location, etc
2. Datatables to display the data
3. When the user wants new data then the use the form in step 1 to display new data

If this is true then I recommend turing off server side processing in Datatables and doing the following:

1. Initialize Datatables without any data, no ajax config and no server side config.
2. Display the user form
3. On submit have /params process the SQL Query and return the results in JSON format
4. In the success of the ajax request you can use clear() (assuming the columns are always the same) to clear the table then uses rows.add() to the new rows.

I put together a simple example of this:
http://live.datatables.net/nowuriya/1/edit

The table initializes without any data. There are two buttons which will display either data set 1 or data et 2. Think of this as your form input. There are click events for each button. This will represent your ajax query and the returned JSON array will be populated in the table by rows.add(). This example uses array data. The same can work with object based data, just need to setup the columns and return the JSON with those objects.

With this type of solution you can take advantage of all the client features like searching and sorting. Of course it is only with the data in the Datatable.

receiving a 500 error

You will need to look at your Flask logs to determine where this error is coming from. Might not need to worry about it if you go with the above. But you may get new errors to troubleshoot

Kevin

nat5142

Figured it out after throwing everything at the wall and seeing what stuck. Turns out the entire time I had the wrong call in my route /table_data:

ajax = request.json

should have been

ajax = request.form

because the data that I needed from DataTables' ajax request was Form data. Showed up in the bottom of the network log. Got it working now though!