JQuery + Ajax in Flask not working

lowercase00

Hello all.

I'm trying to populate my first table using Ajax, so excuse my lack of knowledge.
I have the following HTML:

<div class="x_content">
                <table id="exemplo" name="exemplo" class="display">
                    <thead>
                        <tr>
                            <th>Data</th>
                            <th>Cred</th>
                            <th>Deb</th>
                            <th>Dsc</th>
                            <th>Valor</th>
                        </tr>
                    </thead>
                </table>
            </div>

And right bellow it, I have the following script:

<script>
$(document).ready(function() {
    $('#exemplo').DataTable( {
        "ajax": "files/data.json",
        "columns": [
            { "data": "data" },
            { "data": "cred" },
            { "data": "deb" },
            { "data": "dsc" },
            { "data": "valor" }
        ]
    } );
} );
</script>

My JSON file looks something like this:

[
    {
        "data": null,
        "dsc": null,
        "valor": null,
        "deb": null,
        "cred": null,
        "ID": 1
    },
    {
        "data": 20171019,
        "dsc": "Descricao do evento sem caracteres especiais.",
        "valor": 320.0,
        "deb": "Nao Classificado",
        "cred": "Itau",
        "ID": 2
    }]

I'm loading all the required scrips, but I just can't seem to get something on the table.
Does anybody have any idea of what can be happening?

My HTML is on app/templates/ledger.html and the file is in files/data.json.

Thanks a lot

kthorngren

Looks like you will need to use the ajax.dataSrc option to tell Datatables where to find the data since its not in the default data object. I will need the second example in the doc, something like this:

  "ajax": {
    "url": "files/data.json",
    "dataSrc": ""
  }

Kevin

lowercase00

Thanks a lot for you response.
I tried both empty and with "tableData" and still got nothing...

Maybe my JSON file is wrong (I validated it, though), maybe my script is wrong? Possible to be missing any script to load? Maybe a misplaced comma?

Thanks a lot again.

<script>
$(document).ready(function() {
    $('#exemplo').dataTable( {
        "ajax": {
            "url": "files/data.json",
            "dataSrc": ""
        }   
    });
});
</script>

kthorngren

Your JSON data looks ok and matches with your Datatable config. Do you get an alert or error messages in your browser's console?

To prove your basic config works I built this test case based on your config:
http://live.datatables.net/vinahuhu/1/edit

It doesn't use your ajax URL but loads the data directly.

Maybe you can follow the steps at this tech note to verify the data is being returned by the server:
https://datatables.net/manual/tech-notes/1

Can you provide a link to your page for troubleshooting?

If not maybe you can capture debugger output:
https://datatables.net/manual/tech-notes/10

Kevin

lowercase00

Hey, thanks a lot for your attention once more.
My site is local, so I wouldn't know how to provide a functioning link.

Indeed, with the live.datatables.net link it seems to work, but not within my code (triple checked it, and it seem ok (apart from the Ajax part).

I then went to Chrome Debugger, and found that the data is not being to pulled, and got a few errors:

With "columns" I got and "unexpected string".
When I commented "columns" out, I got "$ not defined in (document).ready.
(images uploaded to imgur in this link

Also debugged with the DataTable code and got the following debug code: "ujiyat".

I think that's all the info I've got at the moment... anything else that could help?

Again, thanks a lot, very helpful.
Cheers

---

EDIT: moved both scripts to the top of my base.html page and now I got a 404 on the JSON, which makes me think it's now a problem related to the path, still don't know how to refer to my files/data.json, I'll try a few things now.

lowercase00

Unfortunately no success...

Tried putting the data.json file in the same place as the HTML, tried adding src="file" to my script tag (which is strange, since I load my scripts with src="static" and it works fine, and static is in the same place as the files folder). Moved the JSON quite a bit around my project and nothing.

I realized that the 404 is related to a data.json?_=1511223833422.
Which I confess I don't know what it is and didn't find anything googling yet.

I'll keep reading to see if I can find anything.

Thanks a lot!

kthorngren

data.json?_=1511223833422

That has do do with disabling cache for the ajax request:
http://api.jquery.com/jquery.ajax/

cache (default: true, false for dataType 'script' and 'jsonp')
Type: Boolean
If set to false, it will force requested pages not to be cached by the browser. Note: Setting cache to false will only work correctly with HEAD and GET requests. It works by appending "_={timestamp}" to the GET parameters. The parameter is not needed for other types of requests, except in IE8 when a POST is made to a URL that has already been requested by a GET.

Maybe you need to use POST. You can change this using the type option (4th example):
https://datatables.net/reference/option/ajax#Examples

Or you may need to use something like **kwargs to accept the ?_=1511223833422 as a parameter in your Flask function called by the ajax URL.

Kevin

lowercase00

By using "type" : "POST" the time stamp disapears.

But even if a use **kwargs as a parameter in my function I still get the 404.

Tried a few variations but nothing.

I copied my data.json file to every single folder of my app and still nothing, which makes me think that is not simply a path problem, maybe there's something else that I'm missing...

kthorngren

I'm not familiar with Flask but found this discussion that may help:
https://datatables.net/forums/discussion/43471

Kevin

lowercase00

Thanks.
I took a look but I'm not sure how to apply that.
I don't really have anything happening on the Flask side, my route only renders the HTML, accepting both GET and POST, so not much to change here...

@app.route('/ledger', methods=['GET', 'POST'])
def ledger():
    return render_template("ledger.html")

Both my dataTable code and my Javascript are on the ledger.html, so I don't know what external influences I might be having.

I have another Python script that generates the JSON and saves it to the "files" folder, and then this Javascript that should populate DataTables for visualization purposes only.

Anyway, thanks a lot for your attention, I'll keep trying over here. In case I find something interesting or identify the problem I'll come back to share my experience.

Cheers

kthorngren

My guess is you will need another Flask function to return the JSON. Maybe something like this:

@app.route('/get_table_data', methods=['POST'])
def get_table_data():
   result = process_request()  #process request and save in result
     
    return jsonify(result) #return JSON string

Then your Datatables config:

    $('#exemplo').dataTable( {
        "ajax": {
            "url": "/get_table_data",
            "dataSrc": ""
        }  
    });

Again, not familiar with Flask but I suspect you will need a Flask function to return the data as shown in their example:
http://flask.pocoo.org/docs/0.12/patterns/jquery/#json-view-functions

Kevin

lowercase00

It may be... but I confess I have no idea on how to do it.

From what I understood on their website the jsonify creates the JSON object, which I thought I wouldn't need to do (since I already have data.json).

Just a context:

I have a HTML form, the user inputs data, and I have a process() Python function that inserts the data into a MariaDB table.

Then, I have a function that retrieves the whole DB and exports it to a JSON file - basically, the idea is to have a DataTable to show a specific table of my database.

So in that code (processing + returning JSON result) it would all happen in the same block, I tried to separate the blocks. But might be worth to process (input) data to MySQL and export JSON in the same function.

Now what's making me confused, is that I set up a local server using Apache with the exact same code (HTML + Javascript only, no Python involved) and directory structure, the funny thing is that now the server finds the file data.json, creates the rows, but they all come up empty (but it does recognize my 12 entries on the table).

And instead of http://datatables.net/td/7 it gives me http://datatables.net/td/4 - requested unknown parameter (0) for row(0), column(0).

lowercase00

The code that processes the form and puts into the database.

from app import app
from flask import render_template, request
import mysql.connector as mariadb
from flask import Flask, render_template, request, redirect, url_for
import csv

cnx = mariadb.connect(user='root', password='', database='test')
cursor = cnx.cursor()

@app.route('/process', methods=['GET', 'POST'])
def process():
    add_reg = ("INSERT INTO base "
           "(data, cred, deb, dsc, valor) "
           "VALUES (%s, %s, %s, %s, %s)")

    data = request.form['data']
    cred = request.form['cred']
    deb = request.form['deb']
    dsc = request.form['dsc']
    valor = request.form['valor']
    
    registro = (data, cred, deb, dsc, valor)

    cursor.execute(add_reg, registro)
    cnx.commit()

    ult_reg = cursor.lastrowid

    cursor.close()
    cnx.close()

    return redirect(url_for('ledger'))

The code that exports the database to a JSON:

import mysql.connector as mariadb
import json
import collections
import pymysql
import itertools
import io

cnx = mariadb.connect(user='root', password='', database='test')
cursor = cnx.cursor()

cursor.execute("SELECT ID, data, cred, deb, dsc, valor FROM base")
rows = cursor.fetchall()

def dictfetchall(cursor):
    desc = cursor.description
    return [dict(itertools.izip([col[0] for col in desc], row)) 
        for row in rows]

results = dictfetchall(cursor)
with open('files/data.json', 'wb+') as fp:
    json.dump(results, fp)

I'm just a beginner, so even though the scripts are workings, there's a 90% chance that I'm going through the long way...

EDIT:
After reading my own post, I'm thinking that maybe I could populate the DataTable via server side processing instead of exporting JSON and importing via Ajax... It might be a good idea...

kthorngren

Maybe this to get the data:

@app.route('/get_table_data', methods=['POST'])
def get_table_data():
    cnx = mariadb.connect(user='root', password='', database='test')
    cursor = cnx.cursor()
 
    cursor.execute("SELECT ID, data, cred, deb, dsc, valor FROM base")
    rows = cursor.fetchall()

    desc = cursor.description
    cnx.close()

    result = [dict(itertools.izip([col[0] for col in desc], row))
        for row in rows]
      
return jsonify(result) #return JSON string

I don't know for a fact but I suspect that Flask won't process AJAX requests and simply return static files.

Kevin

pipigendut

you forget put this :
bProcessing: true, bServerSide: true,