Server-side processing - DataTables warning: table id=example - Ajax error. (Debug code included.)

Server-side processing - DataTables warning: table id=example - Ajax error. (Debug code included.)

culterculter Posts: 102Questions: 24Answers: 0

Hi, I'm trying to implement server-side processing, because my table with 20k rows is really slow (loading time 30s.) I'm using Python/Flask. I added the javascript code with correct id and path to serverside.php, which is in the same directory and to the same directory I uploaded ssp.class.php. My table is the same as in the template here:
https://datatables.net/examples/data_sources/server_side

I did'nt use the "ajax" part, because I don't know where it belongs :neutral:

When I run my application, only the "Show entries", "Search panel", Column names and footer column names are displayed. Immediately I get this error:

DataTables warning: table id=example - Ajax error. For more information about this error, please see http://datatables.net/tn/7

and everytime I click on the column names, I get the same error again.

In the console I found this error:

jquery-3.3.1.min.js:2 GET http://xxx.xxx.xxx.xx/serverside.php?draw=2&columns%5B0%5D%5Bdata%5D=0&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=1&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=2&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&columns%5B3%5D%5Bdata%5D=3&columns%5B3%5D%5Bname%5D=&columns%5B3%5D%5Bsearchable%5D=true&columns%5B3%5D%5Borderable%5D=true&columns%5B3%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B3%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B4%5D%5Bdata%5D=4&columns%5B4%5D%5Bname%5D=&columns%5B4%5D%5Bsearchable%5D=true&columns%5B4%5D%5Borderable%5D=true&columns%5B4%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B4%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B5%5D%5Bdata%5D=5&columns%5B5%5D%5Bname%5D=&columns%5B5%5D%5Bsearchable%5D=true&columns%5B5%5D%5Borderable%5D=true&columns%5B5%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B5%5D%5Bsearch%5D%5Bregex%5D=false&order%5B0%5D%5Bcolumn%5D=2&order%5B0%5D%5Bdir%5D=asc&start=0&length=10&search%5Bvalue%5D=&search%5Bregex%5D=false&_=1534839876816 404 (Not Found)

Debug code: awuzaz

Thank you for any advice.

Answers

  • culterculter Posts: 102Questions: 24Answers: 0

    I tried to add this code to javascript part, but the error is still the same.

    `
    "ajax": {
    "url": "serverside.php",
    "data": {

    "draw": 1,
    "recordsTotal": 57,
    "recordsFiltered": 57,
    "data": [
    [
    "Airi",
    "Satou",
    "Accountant",
    "Tokyo",
    "28th Nov 08",
    "$162,700"
    ],
    [
    "Angelica",
    "Ramos",
    "Chief Executive Officer (CEO)",
    "London",
    "9th Oct 09",
    "$1,200,000"
    ],
    [
    "Ashton",
    "Cox",
    "Junior Technical Author",
    "San Francisco",
    "12th Jan 09",
    "$86,000"
    ],
    [
    "Bradley",
    "Greer",
    "Software Engineer",
    "London",
    "13th Oct 12",
    "$132,000"
    ],
    [
    "Brenden",
    "Wagner",
    "Software Engineer",
    "San Francisco",
    "7th Jun 11",
    "$206,850"
    ],
    [
    "Brielle",
    "Williamson",
    "Integration Specialist",
    "New York",
    "2nd Dec 12",
    "$372,000"
    ],
    [
    "Bruno",
    "Nash",
    "Software Engineer",
    "London",
    "3rd May 11",
    "$163,500"
    ],
    [
    "Caesar",
    "Vance",
    "Pre-Sales Support",
    "New York",
    "12th Dec 11",
    "$106,450"
    ],
    [
    "Cara",
    "Stevens",
    "Sales Assistant",
    "New York",
    "6th Dec 11",
    "$145,600"
    ],
    [
    "Cedric",
    "Kelly",
    "Senior Javascript Developer",
    "Edinburgh",
    "29th Mar 12",
    "$433,060"
    ]
    ]
    }
    }`

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    404 (Not Found)

    This means the server script (serverside.php) is not found on the server. You need to validate the path to this script on your web server.

    Kevin

  • culterculter Posts: 102Questions: 24Answers: 0

    Thank you, Kevin. Yes, the 404 was suspicious, I modified the path several times, but now when I added new route to my python script, it's working, I think. And I've got another error:

    DataTables warning: table id=example - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1

    I tried to debug it as the link says and the XHR response is:

    <?php
    
    /*
     * DataTables example server-side processing script.
     *
     * Please note that this script is intentionally extremely simply to show how
     * server-side processing can be implemented, and probably shouldn't be used as
     * the basis for a large complex system. It is suitable for simple use cases as
     * for learning.
     *
     * See http://datatables.net/usage/server-side for full details on the server-
     * side processing requirements of DataTables.
     *
     * @license MIT - http://datatables.net/license_mit
     */
    
    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     * Easy set variables
     */
    
    // DB table to use
    $table = 'WV_TICK';
    
    // Table's primary key
    $primaryKey = 'ID';
    
    // Array of database columns which should be read and sent back to DataTables.
    // The `db` parameter represents the column name in the database, while the `dt`
    // parameter represents the DataTables column identifier. In this case simple
    // indexes
    $columns = array(
        array( 'db' => 'TICK', 'dt' => 0 ),
        array( 'db' => 'ID',  'dt' => 1 ),
        array( 'db' => 'STADT',   'dt' => 2 ),
        array( 'db' => 'FAILURE',     'dt' => 3 ),
    );
    
    // SQL server connection information
    $sql_details = array(
        'user' => 'admin',
        'pass' => 'xxxxxxxx',
        'db'   => 'TICK',
        'host' => '10.10.10.11'
    );
    
    
    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     * If you just want to use the basic configuration for DataTables with PHP
     * server-side, there is no need to edit below this line.
     */
    
    require( 'ssp.class.php' );
    
    echo json_encode(
        SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
    );
    

    Do I have to create new question with this, or is it ok to post it here?

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    Your server code is Python?

    Sounds like the Python script is just returning the PHP script. If you are using Python then the Python script should perform the DB query and return the result. You wouldn't use the PHP scripts since Python won't execute them.

    Kevin

  • culterculter Posts: 102Questions: 24Answers: 0

    Thanks. In my current solution, I have the DB query in my python script and it push the data from mysql to html page with for loop in python. I was thinking that I need another script for server-side processing.

    How to change the javascript part to accept the data from python script? Is here some working example for python? Thank you.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    DataTables doesn't really "care" (in so much as software ever cares!) if the data comes from Python, PHP, Perl, ColdFusion or anything else. As long as it is JSON, then it will read it just fine.

    If you are using Python on the backend, then ditch the PHP stuff and implement server-side processing in just Python. I'm afraid we haven't got a published example of a Python server-side processing script, but if you search on this page for "Python" you'll find a few others who have done it before that might be useful.

    Allan

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    I use json.dumps() in my Python scripts to return JSON data to the ajax request from Datatables. I use client side processing with my Python scripts. I don't have Server Side Processing example but like Allan said you can find the on the web. I would recommend using client side processing unless your data size is too large.

    Kevin

  • culterculter Posts: 102Questions: 24Answers: 0

    Thank you both. I used client side processing for several hundred rows, but since we have 30k rows, it's useless. In the near future we expect 130k rows. I just found this:

    https://github.com/SergioLlana/datatables-flask-serverside

    Hope it will help.

    Have a nice day!

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    Good find, should work. Their example Javascript code uses legacy Datatables options. They are backward compatible but you should try to use the current form. The conversion guide will help:
    https://datatables.net/upgrade/1.10-convert

    Here is what they have:

    $(document).ready(function () {
      $('#table_id').DataTable({
        bProcessing: true,
        bServerSide: true,
        sPaginationType: "full_numbers",
        lengthMenu: [[10, 25, 50, 100], [10, 25, 50, 100]],
        bjQueryUI: true,
        sAjaxSource: '<API_ENDPOINT>',
        columns: [
          {"data": "Column A"},
          {"data": "Column B"},
          {"data": "Column C"},
          {"data": "Column D"}
        ]
      });
    });
    

    The current would look more like this:

    $(document).ready(function () {
      $('#table_id').DataTable({
        processing: true,
        serverSide: true,
        paginationType: "full_numbers",
        lengthMenu: [[10, 25, 50, 100], [10, 25, 50, 100]],
        ajax: '<API_ENDPOINT>',
        columns: [
          {"data": "Column A"},
          {"data": "Column B"},
          {"data": "Column C"},
          {"data": "Column D"}
        ]
      });
    });
    

    Removed the bjQueryUI: true, because its deprecated and not needed unless you are using that styling library.

    I've not used Flask but will try to help if you have Python based questions.

    Kevin

  • culterculter Posts: 102Questions: 24Answers: 0

    Thank you, Kevin. I was working on this and trying the whole day. I tried to adapt Sergio's solution to my project, I added and modified all the files mentioned in the tutorial and, of course, I have problem. The critical part is

    ajax: '<API_ENDPOINT>',

    I simply don't know how to modify this line to make it work.

    This is my python file where I connect to database, map the column names and push the data to html:

    #db connection via SQLAlchemy
    app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://admin:ABCLC@10.10.99.11/tickets'
    db = SQLAlchemy(app)
    
    class ASE_TICKETS(db.Model):
        __teblename__ = 'ASE_TICKETS'
        id = db.Column('TICKETNO', db.Integer, primary_key=True)
        objectid = db.Column('ID', db.Integer)
        city = db.Column('CITIES', db.String)
        date = db.Column('DATE', db.DateTime)
        severity = db.Column('CRITICAL', db.String(2))
        status = db.Column('STATUS', db.Integer)
        shortdesc = db.Column('DESC', db.String(20))
    
    
    @app.route('/home')
    def home_page():
        return render_template('login.html')
    
    @app.route('/')
    def index():
        return redirect(url_for('home_page'))
    
    @app.route('/static')
    def static_page():
        return render_template('static.html')
    
    
    @app.route('/login', methods=['GET', 'POST'])
    def login_page():
        if request.method == 'POST':
            username = request.form['username']
            password = request.form['password']
            app.logger.info('Login attempt by {} ...'.format(username))
            if username != CREDENTIALS.get('username', None) or \
                            password != CREDENTIALS.get('password', None):
                # Auth failed
                app.logger.info('Authentication failed')
                return render_template('login.html', error='Invalid Credentials'), 401
            else:
                # Auth is successful
                app.logger.info('Authentication successful')
                # save the hashed credentials in a session data structure
                creds = (username+password).encode('utf-8')
                session['credentials'] = hashlib.md5(creds).hexdigest()
                return redirect(url_for('private_page'))
        return render_template('login.html', error=None)
    
    @app.route('/private', methods=['GET'])
    def private_page():
        # first check if we have a session: if so, then render a super-secret template
        try:
            session.pop('credentials')
            tickets = ASE_TICKETS.query.all()
            return render_template('private.html', tickets=tickets)
        except KeyError:
            return redirect(url_for('login_page'))
    

    Do you have some advice how to get it to work? Thank you in advance.

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    You will setup a route that can be used for the ajax call. Taking this one for example:
    @app.route('/static')

    Your ajax url would be the route (/static). For example:
    ajax: "/static",

    That function will then query the DB and return the data in JSON format.

    Kevin

  • culterculter Posts: 102Questions: 24Answers: 0

    Thanks Kevin. I added this route to my .py file:

    @app.route('/tickety')
    def tickety_page():
        tickets = ASE_TICKETS.query.all()
        return render_template('private.html', tickets=tickets)
    

    and modified the html file as you suggested with

    ajax: "/tickety",

    When I run the app, I get

    DataTables warning: table id=table_id - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1

    In the flask web server i can see:

    10.10.99.1 - - [23/Aug/2018 09:28:07] "GET /tickety?draw=1&columns%5B0%5D%5Bdata%5D=TICKETNO&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=OBJECTID&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=CITY&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&columns%5B3%5D%5Bdata%5D=DATE&columns%5B3%5D%5Bname%5D=&columns%5B3%5D%5Bsearchable%5D=true&columns%5B3%5D%5Borderable%5D=true&columns%5B3%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B3%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B4%5D%5Bdata%5D=SEVERITY&columns%5B4%5D%5Bname%5D=&columns%5B4%5D%5Bsearchable%5D=true&columns%5B4%5D%5Borderable%5D=true&columns%5B4%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B4%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B5%5D%5Bdata%5D=STATUS&columns%5B5%5D%5Bname%5D=&columns%5B5%5D%5Bsearchable%5D=true&columns%5B5%5D%5Borderable%5D=true&columns%5B5%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B5%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B6%5D%5Bdata%5D=SHORTDESC&columns%5B6%5D%5Bname%5D=&columns%5B6%5D%5Bsearchable%5D=true&columns%5B6%5D%5Borderable%5D=true&columns%5B6%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B6%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=&search%5Bregex%5D=false&_=1535009287418 HTTP/1.1" 200 -

  • culterculter Posts: 102Questions: 24Answers: 0

    I checked the response as the link to datatables.net suggested and it's html code, which is probably wrong:

    <!DOCTYPE html>
    <html>
       <head>
          <title>TV</title>
          <!-- BS -->
             <link href="/static/css/bootstrap.min.css" rel="stylesheet">
    
    <script src="/static/js/jquery-3.3.1.min.js"></script>
    
          <!-- DataTables -->
    <link rel="stylesheet" type="text/css" href="/static/css/jquery.dataTables.css">
    <script src="/static/js/jquery.dataTables.js"></script>
    
    <link rel="stylesheet" type="text/css" href="/static/css/base.css">
    
    <script>
    $(document).ready(function () {
      $('#table_id').DataTable({
        processing: true,
        serverSide: true,
        paginationType: "full_numbers",
        lengthMenu: [[10, 25, 50, 100], [10, 25, 50, 100]],
        ajax: "/tickety",
        columns: [
          {"data": "TICKETNO"},
          {"data": "OBJECTID"},
          {"data": "CITY"},
          {"data": "DATE"},
          {"data": "SEVERITY"},
          {"data": "STATUS"},
          {"data": "SHORTDESC"}
    
        ]
      });
    });
    </script> ...
    

    I'm afraid I don't fully understand, if the JSON file needs to be prepared or it is generated automatically.

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736
    edited August 2018

    With my Python scripts I use one function (route) to return the HTML web page then another to return the data from the ajax request.

    I think you will need something like this. Lets say the ticket table is on this page:
    https://localhost/tickety

    You would have this route to return the HTML:

    @app.route('/tickety')
    def tickety_page():
        return render_template('private.html')
    

    Then in your Datatable config you have the following ajax option:
    ajax: '/get_tickets',

    In Flask you would have something like this to return the JSON data using jsonify:

    @app.route('/get_tickets')
    def get_tickets():
        tickets = ASE_TICKETS.query.all()
        return jsonify(tickets)
    

    Again I've not used Flask so making some assumptions of how it works. The above should work if you are using client side processing. If your test environment has a small amount of data to start with maybe try getting the above working with Datatables first. Remove the serverSide: true from your config.

    Sounds like you ultimately need server side processing enabled. You probably will need to incorporate something like the models.py from the SergioLlana/datatables-flask-serverside library to fetch the data using server side processing mode.

    Kevin

  • culterculter Posts: 102Questions: 24Answers: 0

    Thanks again, Kevin. It looks better and better. I imported jsonify from flask, modified the .py file and the javascript code. Now I get error 500 and message "ASE_TICKETS 464677> is not JSON serializable". (See below)
    The 464677 is the first TICKETNO value from mysql! I hope that it's some kind of progress :)

    File "/usr/lib/python3.5/site-packages/flask/json/init.py", line 81, in default
    return json.JSONEncoder.default(self, o)
    File "/usr/lib/python3.5/json/encoder.py", line 179, in default
    raise TypeError(repr(o) + " is not JSON serializable")
    TypeError: <ASE_TICKETS 464677> is not JSON serializable
    10.10.99.1 - - [23/Aug/2018 14:33:22] "GET /get_tickets?draw=2&columns%5B0%5D%5Bdata%5D=TICKETNO&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=OBJECTID&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=CITY&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&columns%5B3%5D%5Bdata%5D=DATE&columns%5B3%5D%5Bname%5D=&columns%5B3%5D%5Bsearchable%5D=true&columns%5B3%5D%5Borderable%5D=true&columns%5B3%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B3%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B4%5D%5Bdata%5D=SEVERITY&columns%5B4%5D%5Bname%5D=&columns%5B4%5D%5Bsearchable%5D=true&columns%5B4%5D%5Borderable%5D=true&columns%5B4%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B4%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B5%5D%5Bdata%5D=STATUS&columns%5B5%5D%5Bname%5D=&columns%5B5%5D%5Bsearchable%5D=true&columns%5B5%5D%5Borderable%5D=true&columns%5B5%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B5%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B6%5D%5Bdata%5D=SHORTDESC&columns%5B6%5D%5Bname%5D=&columns%5B6%5D%5Bsearchable%5D=true&columns%5B6%5D%5Borderable%5D=true&columns%5B6%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B6%5D%5Bsearch%5D%5Bregex%5D=false&order%5B0%5D%5Bcolumn%5D=1&order%5B0%5D%5Bdir%5D=asc&start=0&length=10&search%5Bvalue%5D=&search%5Bregex%5D=false&
    =1535027539583 HTTP/1.1" 500 -

    When I get rid of 'serverSide: true,', the error message is exactly the same.

    I definitely need the server-side processing. I tried to implement Sergio's models.py, but I'm not sure it was done the right way. The instructions are very brief. I also tried to contact Sergio, but he didn't responded to my message yet. :(

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    "ASE_TICKETS 464677> is not JSON serializable"

    What type of object does ASE_TICKETS.query.all() return?

    Flask's jsonify probably supports things like string, arrays and dictionaries. Based on you defining columns.data you will need a list of dictionaries with each dictionary containing row row of data. You will want to read this doc for more info on the data structures:
    https://datatables.net/manual/data/

    I tried to implement Sergio's models.py, but I'm not sure it was done the right way. The instructions are very brief.

    Yes they are. Thats why I suggested turning off serverSide processing to start with and learn about getting your data into Datatables. Then tackle the server side part.

    Kevin

  • culterculter Posts: 102Questions: 24Answers: 0

    Hi Kevin, the type is <class 'list'>.

    As I mentioned earlier, I have client-side solution, that is working perfectly, except the speed. So you think that when I turn off server-side processing, it will be easier to set-up and then I just uncomment the line "serverSide: true," and it will be working? I have read in the manual you've posted that it is not possible to dynamically change from client-side to server-side and vice versa.

    I have read about the data types. If I understand it right, I need just the initialisation javascript code, not the code with the data, because in my case, the data are in the database?

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    If I understand it right, I need just the initialisation javascript code, not the code with the data, because in my case, the data are in the database?

    That is correct. You need to configure the DataTable, but it doesn't need the raw data. That will be retrieved via the Ajax calls.

    That's about all I can offer here though as the issue sounds like a Python JSON encoding error. Perhaps it can't handle the type of data you are asking it to. Might be worth debugging the data object just before you try to JSON encode it.

    Allan

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    I didn't look at what you posted close enough. <ASE_TICKETS 464677> is a Python object, which is not serializable. You can probably do something like this to get the list of tickets:

    @app.route('/get_tickets')
    def get_tickets():
        tickets = List(ASE_TICKETS.query.all())
        return jsonify(tickets)
    

    If that doesn't work then I would look on Stackoverflow or other forum to see how the use the .query().all() methods with jsonify().

    Yes, you can start with serverSide turned off which all of the searching and sorting is performed client side. Once you turn it one then all those operations are to be performed by your server scripts. So its not necessarily as simple as just turning on serverSide processing and having it work. I only suggested it to reduce the troubleshooting needed to get the basic table working.

    Kevin

  • culterculter Posts: 102Questions: 24Answers: 0

    Thank you. I think I have little progress here. I start from the beginning with smaller table and I managed to work the jsonify with following code.

    from flask import Flask, render_template, request, jsonify
    from flask_sqlalchemy import SQLAlchemy
    
    app = Flask(__name__)
    app.config['SECRET_KEY'] = 'secretkey'
    app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://admin:pass@10.10.99.11/test1'
    db = SQLAlchemy(app)
    
    class table1(db.Model):
       __tablename__ = 'table1'
       id = db.Column('id', db.Integer, primary_key=True)
       first = db.Column('firstname', db.String(2))
       last = db.Column('lastname', db.String(2))
    
       def __init__(self, first, last):
          self.first = first
          self.last = last
          pass
    
       @property
       def serialize(self):
          return {
             'id': self.id,
             'first': self.first,
             'last': self.last
          }
    
    data=[i.serialize for i in tick]
    
    @app.route('/data')
    def get_data():
    #   return jsonify(data=[i.serialize for i in tick])
       return jsonify(data)
    #   return render_template('data.html', data=data)
    

    The HTML code is:

    <body>
    
    <script>
            $(document).ready(function() {
               $('#table_id').DataTable( {
                  "processing": true,
                  "serverSide": true,
                  "ajax": "/data"
               } );
            } );
    </script>
            <table id="table_id">
                    <thead>
                            <tr>
                                    <th>id</th>
                                    <th>first</th>
                                    <th>last</th>
                            </tr>
                    </thead>
            </table>
    
    </body>
    

    Now the serializable error is gone and in the browser I get

    {"myData":[{"first":"Anton","id":1,"last":"Spelec"},{"first":"Rosamunde","id":2,"last":"Pilcher"},{"first":"Vlasta","id":3,"last":"Burian"}]}

    I tried the JSON validator and it said that it's JSON valid, but in your example the AJAX code starts with

    {
      "draw": 1,
      "recordsTotal": 57,
      "recordsFiltered": 57,
      "data": [
        [
    ...
    

    Is it necessary to add these "draw", "recordsTotal" and "recordsFiltered" at the beginning?

    In my working client-side solution I had this row to send the data to html and then display them with for loop

    return render_template('data.html', data=data)

    but now I don't know how to push the data to the table. When I comment this part of js code

    // "processing": true,
    // "serverSide": true,
    // "ajax": "/data"

    I get empty DataTables table with column names. I tried "ajax": "/data" as Kevin suggested and push the data the old way (like it is in the last commented row in .py code), but without success.

    Thank you for any advice.

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    Is it necessary to add these "draw", "recordsTotal" and "recordsFiltered" at the beginning?

    The order is not important. Those values are used only for server side processing. You will need to provide those, when you enable server side processing, for the table info to display properly.

    From what I can see from your code you will just want to use return jsonify(data) to return the JSON string. Actually it looks like data is a list of dictionaries. If thats the case then you probably want to do something like this return jsonify( {'data': data} ) to return the list in the data object which is the default location Datatables looks for the data.

    Kevin

  • culterculter Posts: 102Questions: 24Answers: 0

    Thank you for your patience. I've got it working. At least part of it. :) Now I have serverside option set to true and data are displayed in the table correctly. The problem is, when I use some of the features of DataTables, e.g. column sorting or search all I get is "Processing...". No error message on server or browser.

    I think that it is incidental to the draw(), because when I click on column row, new request is made with draw=2 and my response is still the same: draw=1. I have set the values for draw, recordsTotal and recordsFiltered even though it has no logic to me, because the number of rows will change.

    Do I have to set these values dynamically somehow? And do I need to query the database for number of rows to set the value for recordsTotal?

    If you have some link to documentation, please post it here.I have read the pages regarding server-side processing and draw(), but still don't know how to manage it. Thank you.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Yes, draw should be the same as what the client-side sends to the server (cast as an integer for security). While recordsTotal and recordsFiltered should be calculated on each request since they might change - e.g. the database might have new records.

    See the documentation here for full details on those parameters.

    Allan

This discussion has been closed.