Skip to content

Instantly share code, notes, and snippets.

@kaiserama

kaiserama/DataTables.py

Last active March 16, 2022 14:52
Download ZIP

Revisions

  1. kaiserama revised this gist Oct 2, 2017. 1 changed file with 149 additions and 0 deletions.
    149 changes: 149 additions & 0 deletions Table.html
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,149 @@
    {% 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 %}
  2. kaiserama created this gist Jun 7, 2013.
    107 changes: 107 additions & 0 deletions DataTables.py
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,107 @@
    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
    11 changes: 11 additions & 0 deletions FlaskApp.py
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,11 @@
    # 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)