Updated 5/9/2015: replaced obsolete CRUD class with SQLFORM, changed CDN, and reformatted code to fit narrower viewports

This tutorial shows how to create a data entry form for a To Do app in web2py and to display all To Do records on the home page using a Handsontable control. Handsontable is an Excel-like grid control written in Javascript. It’s one of those commercial-quality products that happen to be available free.

From the web2py admin interface, create an application. It’s named Handsontable in this example.

Create the model

This application will let you create to do items using a simplified model. The following code is all you need to create a database schema for any database web2py supports. You don’t need to use a separate database administration tool. It’s all in the code.

Append this code to the bottom of db.py:

file: /models/db.py

db.define_table('todo',
    Field('title',notnull=True),
    Field('description','text'),
    Field('priority','integer',default=3,
        requires=IS_IN_SET([1,2,3,4,5],
        labels=
          [T('Very low'), 
          T('Low'), 
          T('Medium'), 
          T('High'), 
          T('Very High')], 
          zero=None)),
    Field('completed',
          'boolean',
          default=False),
          format='%(name)s')

Create the controllers

The home page (rendered from the index controller) will list all items in the to do list using the Handsontable control. The new controller will allow you to create tasks using web2py’s built-in.

Replace the default index() controller method

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

file: /controllers/default.py

def index():
    # Choose all records for display
    todos = db(db.todo).select()
    # Pass the selected records to the view index.html
    return dict(todos=todos)

Create the new() controller method

Still in the default.py controller, create a controller called new():

file: /controllers/default.py

def new():
    # Change form submit button text to "Save" 
    # instead of the less friendly "Submit".
    # Use styling for the current Bootstrap version.
    form=SQLFORM(db.todo, 
        submit_button='Save', 
        formstyle='bootstrap3_stacked').process()
    return dict(form=form)

Create the views

You’ll need to replace the provided index() view and create a new one for the new() controller.

Replace the default view index.html

Replace the contents of /default/index.html with the following:

file: /views/default/index.html

<!-- Simply lists all todos on the index page. Not scalable 
     but useful for illustration.-->
<!-- Use web2py's HTML page template -->
{{extend 'layout.html'}}
<!-- Add a link called 'New' pointing to new.html -->
{{=A('New', _href=URL('new'))}}
<script
  src="https://cdnjs.cloudflare.com/ajax/libs/handsontable/0.14.1/handsontable.full.js">
</script>
<link rel="stylesheet"
  media="screen"
  href="https://cdnjs.cloudflare.com/ajax/libs/handsontable/0.14.1/handsontable.css">
<!-- This empty div will be populated with a Handonstable
     grid when it's created via new. -->
<div id="todo-table"></div>
<script>
var
    // Convert Python list of todo records from web2py
    // to a JSON object. The XML() helper prevents
    // quotes from being escaped.
    todoTableData = {{=XML(todos.json())}},
 
    // Get a reference to the div where the 
    // Handsontable grid control will be placed.
    todoTableDiv = document.getElementById('todo-table'),
 
    // These settings determine what data,  
    // display options, and behaviors will
    // be used by the Handsontable control.
    todoTableSettings = {
 
        // Records passed in from Python.
        data: todoTableData,
 
        // Titles used at top of each column.
        colHeaders: 
          ["Title", 
           "Description", 
            "Priority", 
           "Completed"], // Omit id field
 
        // Choose which fields (columns) of the
        // database query results will be used.
        // Note the ID field is omitted, just
        // to show that selected columns can be used.
        columns: [
            {data: 'title'},
            {data: 'description'},
            {data: 'priority'},
            {data: 'completed'}
        ]
    },
    // The reference will be kept as a handle to avoid
    // multiple renderings of the table.
    todoTableRef;
      
    todoTableRef = 
        // The new operator will cause the div
        // to be populated by the grid and to be
        // rendered into HTML (displayed)
        // the first time.
        new Handsontable(todoTableDiv, todoTableSettings);
</script>

Create a view for entering records called default/new.html

Create a view file named default/new.html and replace the provided code with this:

file: /views/default/new.html

{{extend 'layout.html'}}
<!-- The 'Home' link returns user to the index.html view. -->
{{=A('Home', _href=URL('index'))}}
{{if 'message'in globals():}}
<h3>{{=message}}</h3>
{{pass}}
{{=form}}