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)

Table.html

{% block styles %}
	{{ super() }}
    <link href="{{ url_for('static', filename='css/datatables_table_jui.css') }}" rel="stylesheet" type="text/css" />
    <link href="{{ url_for('static', filename='css/ColVis.css') }}" rel="stylesheet" type="text/css" />
	<link href="{{ url_for('static', filename='css/TableTools.css') }}" rel="stylesheet" type="text/css" />
{% endblock %}

{% block page_content %}

<div class="row">
	<div class="col-lg-12">
        <table border="0" cellpadding="0" cellspacing="0" class="display" id="dataTable">
            <thead>
                <tr class="searchRow">
					<th>Customer#</th>
					<th width="5">State</th>
					<th width="75">County</th>
					<th>Customer</th>
					<th width="100">First Name</th>
					<th width="125">Last Name</th>
					<th width="150">Phone</th>
					<th width="250">Email</th>
				</tr>
            </thead>
			
            <tbody>
                <tr>
                    <td colspan="17" class="dataTables_empty">Loading data from server</td>
                </tr>
            </tbody>
        </table>
	</div>
</div>

{% endblock %}

{% block scripts %}
	{{ super() }}
	<script type="text/javascript" charset="utf-8" src="{{ url_for('static', filename='js/jquery.dataTables.min.js') }}"></script>
	<script type="text/javascript" charset="utf-8" src="{{ url_for('static', filename='js/ColVis.min.js') }}"></script>
	<script type="text/javascript" src="{{ url_for('static', filename='js/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("Customer Lookup");
		
				var oTable = $('#dataTable').dataTable({
				"bProcessing": true,
				"bServerSide": true,
				"sAjaxSource": "{{ url_for('internal.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 CSV"
						},
						{
							"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('custNum', 'county', 'sortName2', 'firstName', 'lastName', 'phone', 'email', 'billingState');
				
				"fnRowCallback": function( nRow, aData, iDisplayIndex ) {
					 
					$('td:eq(0)', nRow).html( aData[7]);
					
					$('td:eq(1)', nRow).html( aData[1]);
					$('td:eq(1)', nRow).css('white-space','nowrap'); 
					var linkAddr = '{{ url_for('internal.customer', id=00) }}'.replace('0', aData[0]);

					$('td:eq(2)', nRow).html( '<a class="viewCust" title="Open a new window to view: '+aData[2]+'" href="'+linkAddr+'" target="_blank">'+aData[2]+'</a>');
					$('td:eq(2)', nRow).css('white-space','nowrap'); 
					
					$('td:eq(3)', nRow).html( aData[3]);
					
					$('td:eq(4)', nRow).html( aData[4]);
					
					$('td:eq(5)', nRow).html( '<span class="dt_muniPhone" ><a class="dialPhone" href="TT:'+aData[5]+'?Dial">'+aData[5]+'</a></span>');

					$('td:eq(6)', nRow).html( '<a class="emailLink" title="Send email to '+aData[6]+'" href="mailto:'+aData[6]+'" target="_blank">'+aData[6]+'</a>'  );

					return nRow;
				},
				
				"fnInitComplete": function() {
					var oSettings = oTable.fnSettings();
					$(".tableCount").html("There are <strong>"+addCommas(oSettings.fnRecordsTotal())+"</strong> customer entries.");
		
				}

			});
		});
		
	</script>

{% endblock %}

Could you share you javascript?

Hi, where is the html, javascript code ?
Thanks

Oh man, sorry I didn't see these comments until now. I assume you guys have moved on, if not I'll post my HTML/JS code.

Please post it.

I would be also interested in the HTML/JS code. Thx.

So this is just the raw HTML, I didn't clean it up. Some parts of the JS include resources you'd have to include yourself like the copy_csv_xls_pdf.swf object for copying stuff. But you can see where I'm calling the get_server_data method for retrieving the data for the table on line 66.

Sorry again for the delay, I checked back a few times and didn't see a reply so stopped looking. Hope this helps!