Skip to content

Instantly share code, notes, and snippets.

@kaiserama
Last active March 16, 2022 14:52
Show Gist options
  • Save kaiserama/5732513 to your computer and use it in GitHub Desktop.
Save kaiserama/5732513 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)
{% 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 %}
@fdebef
Copy link

fdebef commented Sep 18, 2017

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

@kaiserama
Copy link
Author

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!

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