Using Datatables.net with web2py for ultra-fast grid display

Download the DataTables jQuery grid plugin from datatables.net or just use a CDN as shown in this example.

Create the model file models/person.py with the following contents, which create a database and fills it with 101 barely unique records for demonstration purposes:

# Create a database named 'person' with 'last_name' and 'first_name' fields.
# web2py also creates a field named 'id' automatically.
db.define_table('person',Field('last_name'), Field('first_name'))

# If no database exists, generate a database of 101 unique records
# with names in the form John1 Smith1, John43 Smith43, etc.
if db(db.person).isempty():
    for eachName in range(101):
        nextNumber=str(eachName)
        db.person.update_or_insert(last_name='Smith'+nextNumber,first_name='John'+nextNumber)

Replace the index() controller in default.py as follows:

def index():
    import json
    # Select all the records, to show how
    # datatables.net paginates.
    # Rows can't be serialized because they contain a reference to
    # an open database connection. Use as_list()
    # to serialize the query result.
    people = json.dumps(db(db.person).select().as_list())
    # Convert to XML for DataTable
    return dict(results=XML(people))

Replace the view file default/index.html with the following;

{{extend 'layout.html'}}
{{if 'message' in globals():}}
<h3>{{=message}}</h3>
{{pass}}
<script
  src="https://cdn.datatables.net/1.10.10/js/jquery.dataTables.min.js">
  src="https://cdn.datatables.net/1.10.10/js/dataTables.bootstrap.min.js">
</script>
<link rel="stylesheet"
  media="screen"
  href="https://cdn.datatables.net/1.10.10/css/dataTables.bootstrap.min.css">
   <table id="person-table" class="table table-bordered table-striped" cellspacing="0" width="100%">
        <thead>
            <tr>
                <th>First name</th>
                <th>Last name</th>
            </tr>
        </thead>
    </table>

<script type="text/javascript">
$(document).ready(function(){
    $("#person-table").DataTable({
        data:  {{=results}},
        columns: [
            { data: 'first_name' },
            { data: 'last_name' }
            // 'id' omitted to show that you can
            // use only the fields you choose
        ]
    })
});
</script>

Now when you run the program you'll see a (completely unstyled) and very high performance grid with automatic pagination and that lets you sort on any column.

Comments (0)