DataTables server-side processing - echoing back draw() parameter (Python/Flask)

Question

I'm using DataTables to display data from MySQL. It worked fine, until I was forced to use server-side processing (100k rows). Now, when I load the table in the browser, it works fine until I use some feature of DataTables (search, column sorting..). When I click on e.g. column name, all I got is "Processing..." message.

I noticed, that with every click on the table, the draw is raised by 1 in the XMLHttpRequest, but my 'draw' is still set to 1 in my code.

My definition of draw, recordsTotal, recordsFiltered in python/flask code(shortened):

tick = table.query.all()
rowsCount = table1.query.count()

x = {'draw':1,'recordsTotal':rowsCount,'recordsFiltered':10}
y = dict(data=[i.serialize for i in tick])
z = y.copy()
z.update(x)

@app.route("/api/result")
def result_json():
    return jsonify(z)

@app.route('/data')
def get_data():
    return render_template('data.html')

My JSON:

{
  "data": [
    {
      "first": "Anton",
      "id": 1,
      "last": "Spelec"
    },
    {
      "first": "Rosamunde",
      "id": 2,
      "last": "Pilcher"
    },
    {
      "first": "Vlasta",
      "id": 3,
      "last": "Burian"
    },
    {
      "first": "Anton",
      "id": 4,
      "last": "Bernolak"
    },
    {
      "first": "Willy",
      "id": 5,
      "last": "Wonka"
    }
  ],
  "draw": 1,
  "recordsFiltered": 5,
  "recordsTotal": 5
}

My html page with DataTables inicialisation:

<script>

        $(document).ready(function() {
           $('#table_id').DataTable( {
              "processing": true,
              "serverSide": true,
              "paging": true,
              "pageLength": 10,
              "lengthMenu": [[10, 25, 50, -1], [10, 25, 50, "All"]],
              "ajax": {
               url: 'api/result',
                },
              columns: [
               { "data": "id" },
               { "data": "first" },
               { "data": "last" }
                ]
           } );
        } );

</script>
 <table id="table_id">
                <thead>
                        <tr>
                                <th>id</th>
                                <th>first</th>
                                <th>last</th>
                        </tr>
                </thead>
 </table>

The XHR is here:

Request URL: http://10.10.10.12/api/result?draw=7&columns%5B0%5D%5Bdata%5D=id&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=first&columns%5B1%5D%5Bname%5D=&columns%5B1%5D%5Bsearchable%5D=true&columns%5B1%5D%5Borderable%5D=true&columns%5B1%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B1%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B2%5D%5Bdata%5D=last&columns%5B2%5D%5Bname%5D=&columns%5B2%5D%5Bsearchable%5D=true&columns%5B2%5D%5Borderable%5D=true&columns%5B2%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B2%5D%5Bsearch%5D%5Bregex%5D=false&order%5B0%5D%5Bcolumn%5D=0&order%5B0%5D%5Bdir%5D=asc&start=0&length=10&search%5Bvalue%5D=asdf&search%5Bregex%5D=false&_=1536075500781

DataTables documentation advises to cast this parameter to an integer and send it back. I found similar question about draw parameter and it supposed the same, but unfortunately I'm not able to make it work. Casting the parameter to integer would not be a problem, I think, but I'm lost in what to do with it next or how to push the raised draw parameter to my JSON.

Thank you.

Doobeh Doobeh · Accepted Answer · 2018-09-04T14:26:36

If DataTables is sending a new value for draw to your server-- just read that value and send it back:

@app.route("/api/result")
def result_json():
    return jsonify(z)

Could just become (adjust the code if DataTables sends the values in some other way):

@app.route("/api/result")
def result_json():
    z.update({'draw': request.form.get('draw')})
    return jsonify(z)

I'm not addressing that your code doesn't seem to do anything with filtering or searching, but at least it gives you a starting point to build from.

Update

From the XHR code you pasted-- it looks like DataTables is passing values in via querystrings-- so request.args.get('draw') would be the way to access that draw data-value.

DataTables server-side processing - echoing back draw() parameter (Python/Flask)

2 Answers

Update