DataTables - server-side processing in Python/Flask - Not working connection to server-side script

Question

I'm struggling with this for several days. I'm trying to implement Sergio Llanna's server-side solution to my existing project.

Sergio's DataTables server-side processing

and this is my implementation:

My DataTables server-side processing

The difference is, that my solution grabs the data from MySQL database instead of local file. I think that I'm pretty close to make it work, data from database are displayed in the table correctly. When I turn off serverSide option, sorting and searching is working fine, but when I turn it on, everytime I click on the column, search or anything else I get just "Processing..." Flask debug mode is on. There is no error in the browser's development tools or flask development server.

My JSON XHR (response):

{
  "data": [
    {
      "cola": "Hello", 
      "colb": "How is it going", 
      "colc": 1, 
      "cold": 4
    }, 
    {
      "cola": "Sample text", 
      "colb": "Another sample", 
      "colc": 2, 
      "cold": 9
    }, 
    {
      "cola": "Kurnik hosi", 
      "colb": "Guten tag", 
      "colc": 3, 
      "cold": 3
    }, 
    {
      "cola": "Achiles", 
      "colb": "Patus", 
      "colc": 4, 
      "cold": 1
    }, 
    {
      "cola": "Kim", 
      "colb": "Kiduk", 
      "colc": 5, 
      "cold": 8
    }, 
    {
      "cola": "Pastina", 
      "colb": "Zavada", 
      "colc": 6, 
      "cold": 9
    }, 
    {
      "cola": "Dolna", 
      "colb": "Marikova", 
      "colc": 7, 
      "cold": 9
    }
  ], 
  "draw": 1, 
  "recordsFiltered": 10, 
  "recordsTotal": 7
}

I think the problem is with the request sent from browser, but this is my first Python/Flask project, so I'm not sure. Thank you for any advice.

Used technologies: OS Debian 9, DB MariaDB 10, Flask-SQLAlchemy

Joost Joost · Accepted Answer · 2018-09-10T10:45:34

It was a bit unclear to me what you exactly changed compared to the original files, so I went ahead and tried adding SQLAlchemy support to Sergio's original project.

__init__.py changes:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

flask_app = Flask(__name__)
db = SQLAlchemy(flask_app)

from app.mod_tables.models import TableBuilder, SomeTable, add_some_random_db_entries
db.create_all()
add_some_random_db_entries()
table_builder = TableBuilder()

models.py changes:

from app.mod_tables.serverside.serverside_table import ServerSideTable
from app.mod_tables.serverside import table_schemas
from app import db

DATA_SAMPLE = [
    {'A': 'Hello!', 'B': 'How is it going?', 'C': 3, 'D': 4},
    {'A': 'These are sample texts', 'B': 0, 'C': 5, 'D': 6},
    {'A': 'Mmmm', 'B': 'I do not know what to say', 'C': 7, 'D': 16},
    {'A': 'Is it enough?', 'B': 'Okay', 'C': 8, 'D': 9},
    {'A': 'Just one more', 'B': '...', 'C': 10, 'D': 11},
    {'A': 'Thanks!', 'B': 'Goodbye.', 'C': 12, 'D': 13}
]

class SomeTable(db.Model):
    __tablename__ = 'some_table'
    cola = db.Column('A', db.String(2))
    colb = db.Column('B', db.String(2))
    colc = db.Column('C', db.Integer, primary_key=True)
    cold = db.Column('D', db.Integer)

    def __init__(self, cola, colb, colc, cold):
        self.cola = cola
        self.colb = colb
        self.colc = colc
        self.cold = cold

    @property
    def serialize(self):
        return {
             'A': self.cola,
             'B': self.colb,
             'C': self.colc,
             'D': self.cold
        }


def add_some_random_db_entries():
    letters = 'arstarstarstarstaars'
    for i in range(10):
        item = SomeTable(letters[i], letters[i + 1: i + 3], i, i + 1)
        db.session.add(item)
    db.session.commit()


def make_data_sample_from_db():
    newlist = []
    for row in SomeTable.query.all():
        newlist.append(row.serialize)
    return newlist


class TableBuilder(object):

    def collect_data_clientside(self):
        return {'data': DATA_SAMPLE}

    def collect_data_serverside(self, request):
        columns = table_schemas.SERVERSIDE_TABLE_COLUMNS
        data = make_data_sample_from_db()
        return ServerSideTable(request, data, columns).output_result()

You should be able to just add the database URL, and it should work the same from there.

However, this method of getting filtered results is quite inefficient, especially with large databases. It is only good for small databases, because you have to query the entire table, and the filter it using dictionaries.

I would probably check out flask-admin. It has a lot of the filtering build into the package itself, using SQLAlchemy's filtering support, which means that it is a lot quicker than this approach.

Here's the modified repo on github.

DataTables - server-side processing in Python/Flask - Not working connection to server-side script

1 Answers