Jquery DataTables class implementation in Flask with MySQL. There was an example of using DataTables with MongoDB + Flask, but nothing for using MySQL + Flask. Be sure you install Flask-MySQL extension first!

DataTables.py

from MySQLdb import cursors
from flask import request

class DataTablesServer(object):
 
    def __init__( self, request, columns, index, table, cursor):
        self.columns = columns
        self.index = index
        self.table = table
        # values specified by the datatable for filtering, sorting, paging
        self.request_values = request.values
         
        # pass MysqlDB cursor
        self.dbh = cursor
 
        # results from the db
        self.resultData = None
         
        # total in the table after filtering
        self.cadinalityFiltered = 0
 
        # total in the table unfiltered
        self.cadinality = 0
 
        self.run_queries()
 
 
    def output_result(self):
        # return output
        output = {}
        output['sEcho'] = str(int(self.request_values['sEcho']))
        output['iTotalRecords'] = str(self.cardinality)
        output['iTotalDisplayRecords'] = str(self.cadinalityFiltered)
        aaData_rows = []
 
        for row in self.resultData:
            aaData_row = []
            for i in range( len(self.columns) ):
                aaData_row.append(str(row[ self.columns[i] ]).replace('"','\\"'))
             
            # add additional rows here that are not represented in the database
            # aaData_row.append(('''<input id='%s' type='checkbox'></input>''' % (str(row[ self.index ]))).replace('\\', ''))
 
            aaData_rows.append(aaData_row)
 
        output['aaData'] = aaData_rows 
        return output
 
    def run_queries(self):
        dataCursor = self.dbh.cursor(cursors.DictCursor) # replace the standard cursor with a dictionary cursor only for this query
        dataCursor.execute( """
            SELECT SQL_CALC_FOUND_ROWS %(columns)s
            FROM   %(table)s %(where)s %(order)s %(limit)s""" % dict(
                columns=', '.join(self.columns), table=self.table, where=self.filtering(), order=self.ordering(),
                limit=self.paging()
            ) )
        self.resultData = dataCursor.fetchall()

        cadinalityFilteredCursor = self.dbh.cursor()
        cadinalityFilteredCursor.execute( """
            SELECT FOUND_ROWS()
        """ )
        self.cadinalityFiltered = cadinalityFilteredCursor.fetchone()[0]

        cadinalityCursor = self.dbh.cursor()
        cadinalityCursor.execute( """SELECT COUNT(%s) FROM %s""" % (self.index, self.table))
        self.cardinality = cadinalityCursor.fetchone()[0]

 
    def filtering(self):         
        # build your filter spec

        filter = ""
        if ( self.request_values.has_key('sSearch') ) and ( self.request_values['sSearch'] != "" ):
            filter = "WHERE "
            for i in range( len(self.columns) ):
                filter += "%s LIKE '%%%s%%' OR " % (self.columns[i], self.request_values['sSearch'])
            filter = filter[:-3]
        return filter
        
        # individual column filtering if needed
        
        #and_filter_individual_columns = []
        #for i in range(len(columns)):
        #    if (request_values.has_key('sSearch_%d' % i) and request_values['sSearch_%d' % i] != ''):
        #        individual_column_filter = {}
        #        individual_column_filter[columns[i]] = {'$regex': request_values['sSearch_%d' % i], '$options': 'i'}
        #        and_filter_individual_columns.append(individual_column_filter)
 
        #if and_filter_individual_columns:
        #    filter['$and'] = and_filter_individual_columns
        return filter

    def ordering( self ):
        order = ""
        if ( self.request_values['iSortCol_0'] != "" ) and ( self.request_values['iSortingCols'] > 0 ):
            order = "ORDER BY  "
            for i in range( int(self.request_values['iSortingCols']) ):
                order += "%s %s, " % (self.columns[ int(self.request_values['iSortCol_'+str(i)]) ], \
                    self.request_values['sSortDir_'+str(i)])
        return order[:-2]
 
    def paging(self):
        limit = ""
        if ( self.request_values['iDisplayStart'] != "" ) and ( self.request_values['iDisplayLength'] != -1 ):
            limit = "LIMIT %s, %s" % (self.request_values['iDisplayStart'], self.request_values['iDisplayLength'] )
        return limit

FlaskApp.py

# create an app.route for your javascript
@app.route("/retrieve_server_data")
def get_server_data():
  columns = [ 'col1', 'col2', 'col3']
	index_column = "index_col"
	table = "table_name"
	cursor = mysql.get_db() # include a reference to your app mysqldb instance
	results = DataTablesServer(request, columns, index_column, table, cursor).output_result()

	# return the results as json # import json
	return json.dumps(results)

Hey @teashaw, I followed your work and tried to implement the datatables in my project.

I am using Flask + MySQLdb,
I have created the file Datatables.py as it is and implemented the FlaskApp.py codes in my starter python file,
then the html code is this;

{% extends 'layout.html' %}

{% block body %}

Add Products

---

{{ table }} {% for pesticides_view in pesticides %} {% endfor %} List of Permitted Pesticides

S/No	Brand Name	Common Name	Registrant	TPRI Reg.	Batch	Permit	Swahili Usage	English Usage	Prod	Expires	Status	Category
	{{pesticides_view.brand_name}}	{{pesticides_view.common_name}}	{{pesticides_view.registrant}}	{{pesticides_view.tpri_reg}}	{{pesticides_view.batch_no}}	{{pesticides_view.permit_no}}	{{pesticides_view.swahili_usage}}	{{pesticides_view.english_usage}}	{{pesticides_view.manuf_date}}	{{pesticides_view.expiry_date}}	{{pesticides_view.reg_status}}	{{pesticides_view.p_category}}

<script type="text/javascript" charset="utf-8" src="https://cdn.datatables.net/v/dt/dt-1.10.16/datatables.min.js"></script> <script type="text/javascript" charset="utf-8" src="https://cdn.datatables.net/colvis/1.1.2/js/dataTables.colVis.min.js"></script> <script type="text/javascript" charset="utf-8" src="https://cdn.datatables.net/tabletools/2.2.1/js/dataTables.tableTools.min.js"></script> <script type="text/javascript" charset="utf-8"> function addCommas(nStr) { nStr += ''; x = nStr.split('.'); x1 = x[0]; x2 = x.length > 1 ? '.' + x[1] : ''; var rgx = /(\d+)(\d{3})/; while (rgx.test(x1)) { x1 = x1.replace(rgx, '$1' + ',' + '$2'); } return x1 + x2; }; $(document).ready(function() { //hide loader $("#loading").hide(); $("title").html("Pesticides Lookup"); var oTable = $('#dataTable').dataTable({ "bProcessing": true, "bServerSide": true, "sAjaxSource": "{{ url_for('get_server_data') }}", "bDeferRender": true, "sDom": '<"H"fril>t<"F"iT>', "bJQueryUI": true, "oTableTools": { "sSwfPath": "{{ url_for('static', filename='swf/copy_csv_xls_pdf.swf') }}", "aButtons": [ { "sExtends": "copy", "sButtonText": "Copy to clipboard" }, { "sExtends": "csv", "sButtonText": "Save to Excel" }, { "sExtends": "pdf", "sButtonText": "Save as PDF" } ] }, //"oColVis": { // "activate": "mouseover", // "aiExclude": [ 0,2 ], // "bRestore": true //}, "aoColumnDefs": [ { "bVisible": false, "aTargets": [ 0 ] }, { "bSortable": false, "aTargets": [5, 6 ] } ], //"bStateSave": true, "bSortClasses": false, "sPaginationType": "full_numbers", "aaSorting": [[1,'asc'],[2,'asc']], "iDisplayLength": 50, "aLengthMenu": [[10, 25, 50, 100, 500, -1], [10, 25, 50, 100, 500, "All"]], "oLanguage": { "sSearch": "Search for:" }, //$aColumns = array(''); "fnRowCallback": function( nRow, aData, iDisplayIndex ) { $('td:eq(0)', nRow).html( aData[11]); $('td:eq(1)', nRow).html( aData[1]); $('td:eq(2)', nRow).html( aData[2]); $('td:eq(3)', nRow).html( aData[3]); $('td:eq(4)', nRow).html( aData[4]); $('td:eq(5)', nRow).html( aData[5]); $('td:eq(6)', nRow).html( aData[6]); $('td:eq(7)', nRow).html( aData[7]); $('td:eq(8)', nRow).html( aData[8]); $('td:eq(9)', nRow).html( aData[9]); $('td:eq(10)', nRow).html( aData[10]); $('td:eq(11)', nRow).html( aData[11]); return nRow; }, "fnInitComplete": function() { var oSettings = oTable.fnSettings(); $(".tableCount").html("There are "+addCommas(oSettings.fnRecordsTotal())+" Pesticides Details."); } }); }); </script>

{% endblock %}

But the datatables does not show its features in my work.

Please help, I am new in Flask programming