Skip to content

Instantly share code, notes, and snippets.

@teaguexiao
Forked from kaiserama/DataTables.py
Created April 27, 2016 15:28
Show Gist options
  • Save teaguexiao/78c90078ea7e96f8ee20fb2d135979c1 to your computer and use it in GitHub Desktop.
Save teaguexiao/78c90078ea7e96f8ee20fb2d135979c1 to your computer and use it in GitHub Desktop.
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!
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
# 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)
@ngirwa
Copy link

ngirwa commented Jun 20, 2018

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment