SQLFORM

We now move to the next level by providing the application with a model file:

  1. db = DAL('sqlite://storage.sqlite')
  2. db.define_table('person', Field('name', requires=IS_NOT_EMPTY()))

Modify the controller as follows:

  1. def display_form():
  2.    form = SQLFORM(db.person)
  3.    if form.process().accepted:
  4.        response.flash = 'form accepted'
  5.    elif form.errors:
  6.        response.flash = 'form has errors'
  7.    else:
  8.        response.flash = 'please fill out the form'
  9.    return dict(form=form)

The view does not need to be changed.

In the new controller, you do not need to build a FORM, since the SQLFORM constructor built one from the table db.person defined in the model. This new form, when serialized, appears as:

  1. <form enctype="multipart/form-data" action="" method="post">
  2.   <table>
  3.     <tr id="person_name__row">
  4.        <td><label id="person_name__label"
  5.                   for="person_name">Your name: </label></td>
  6.        <td><input type="text" class="string"
  7.                   name="name" value="" id="person_name" /></td>
  8.        <td></td>
  9.     </tr>
  10.     <tr id="submit_record__row">
  11.        <td></td>
  12.        <td><input value="Submit" type="submit" /></td>
  13.        <td></td>
  14.     </tr>
  15.   </table>
  16.   <input value="9038845529" type="hidden" name="_formkey" />
  17.   <input value="person" type="hidden" name="_formname" />
  18. </form>

The automatically generated form is more complex than the previous low-level form. First of all, it contains a table of rows, and each row has three columns. The first column contains the field labels (as determined from the db.person), the second column contains the input fields (and eventually error messages), and the third column is optional and therefore empty (it can be populated with the fields in the SQLFORM constructor).

All tags in the form have names derived from the table and field name. This allows easy customization of the form using CSS and JavaScript. This capability is discussed in more detail in Chapter 11.

More important is that now the accepts method does a lot more work for you. As in the previous case, it performs validation of the input, but additionally, if the input passes validation, it also performs a database insert of the new record and stores in form.vars.id the unique “id” of the new record.

A SQLFORM object also deals automatically with “upload” fields by saving uploaded files in the “uploads” folder (after having them renamed safely to avoid conflicts and prevent directory traversal attacks) and stores their names (their new names) into the appropriate field in the database. After the form has been processed, the new filename is available in form.vars.fieldname (i.e., it replaces the cgi.FieldStorage object in request.vars.fieldname), so you can easily reference the new name right after upload.

Warning: the default field length is 512 chars. If one’s filesystem does not support filenames this long, it can generate names that will cause an error when an attempt is made to create them. This can be solved by setting the Field(..., length=...) to approriate value. Also notice this may truncate the encoding of the original filename and it may be impossible to recover it upon downloading of the uploaded file.

A SQLFORM displays “boolean” values with checkboxes, “text” values with textareas, values required to be in a definite set or a database with drop-downs, and “upload” fields with links that allow users to download the uploaded files. It hides “blob” fields, since they are supposed to be handled differently, see More on uploads section in Chapter 6 for an example.

For example, consider the following model:

  1. db.define_table('person',
  2.     Field('name', requires=IS_NOT_EMPTY()),
  3.     Field('married', 'boolean'),
  4.     Field('gender', requires=IS_IN_SET(['Male', 'Female', 'Other'])),
  5.     Field('profile', 'text'),
  6.     Field('image', 'upload'))

In this case, SQLFORM(db.person) generates the form shown below:

image

The SQLFORM constructor allows various customizations, such as displaying only a subset of the fields, changing the labels, adding values to the optional third column, or creating UPDATE and DELETE forms, as opposed to INSERT forms like the current one. SQLFORM is the single biggest time-saver object in web2py.

The class SQLFORM is defined in “gluon/sqlhtml.py”. It can be easily extended by overriding its xml method, the method that serializes the objects, to change its output.

The signature for the SQLFORM constructor is the following:

  1. SQLFORM(table, record=None,
  2.         deletable=False, linkto=None,
  3.         upload=None, fields=None, labels=None,
  4.         col3={}, submit_button='Submit',
  5.         delete_label='Check to delete',
  6.         showid=True, readonly=False,
  7.         comments=True, keepopts=[],
  8.         ignore_rw=False, record_id=None,
  9.         formstyle='table3cols',
  10.         buttons=['submit'], separator=': ',
  11.         extra_fields=None,
  12.         **attributes)
  • The optional second argument turns the INSERT form into an UPDATE form for the specified record (see next subsection).
  • If deletable is set to True, the UPDATE form displays a “Check to delete” checkbox. The value of the label for this field is set via the delete_label argument.
  • submit_button sets the value of the submit button.
  • id_label sets the label of the record “id”
  • The “id” of the record is not shown if showid is set to False.
  • fields is an optional list of field names that you want to display. If a list is provided, only fields in the list are displayed. For example:
  1. fields = ['name']
  • labels is a dictionary of field labels. The dictionary key is a field name and the corresponding value is what gets displayed as its label. If a label is not provided, web2py derives the label from the field name (it capitalizes the field name and replaces underscores with spaces). For example:
  1. labels = {'name':'Your Full Name:'}
  • col3 is a dictionary of values for the third column. For example:
  1. col3 = {'name':A('what is this?',
  2.       _href='http://www.google.com/search?q=define:name')}
  • linkto and upload are optional URLs to user-defined controllers that allow the form to deal with reference fields. This is discussed in more detail later in Links to referencing records and SQLFORM and uploads sections respectively.
  • readonly. If set to True, displays the form as readonly
  • comments. If set to False, does not display the col3 comments
  • ignore_rw. Normally, for a create/update form, only fields marked as writable=True are shown, and for readonly forms, only fields marked as readable=True are shown. Setting ignore_rw=True causes those constraints to be ignored, and all fields are displayed. This is mostly used in the appadmin interface to display all fields for each table, overriding what the model indicates.
  • formstyle determines the style to be used when serializing the form in html. The default value is sourced from response.formstyle, currently it is “bootstrap4_inline”. Other options are “bootstrap4_stacked”, “bootstrap3_inline, “bootstrap3_stacked”, “bootstrap2”, “table3cols”, “table2cols” (one row for label and comment, and one row for input), “ul” (makes an unordered list of input fields), “divs” (represents the form using css friendly divs, for arbitrary customization), “bootstrap” which uses the bootstrap 2.3 form class “form-horizontal”. formstyle can also be a function which generates everything inside the FORM tag. You pass to your form constructor function two arguments, form and fields. Hints can be found in the source code file sqlhtml.py (look for functions named formstyle_)
  • buttons is a list of INPUTs or BUTTONs (though technically could be any combination of helpers) that will be added to a DIV where the submit button would go.

For example, adding a URL-based back-button (for a multi-page form) and a renamed submit button:

  1. buttons = [BUTTON('Back', _type="button", _onClick="parent.location='%s'" % URL(...),
  2.            BUTTON('Next', _type="submit")]

or a button which links to another page:

  1. buttons = [..., A("Go to another page", _class='btn', _href=URL("default", "anotherpage"))]
  • separator sets the string that separates form labels from form input fields, if set to None the default value is sourced from response.form_label_separator
  • extra_fields is a list of extra Fields to add.
  • Optional attributes are arguments starting with underscore that you want to pass to the FORM tag that renders the SQLFORM object. Examples are:
  1. _action = '.'
  2. _method = 'POST'

There is a special hidden attribute. When a dictionary is passed as hidden, its items are translated into “hidden” INPUT fields (see the example for the FORM helper in Chapter 5).

  1. form = SQLFORM(..., hidden=...)

causes the hidden fields to be passed with the submission, no more, no less. form.accepts(...) is not intended to read the received hidden fields and move them into form.vars. The reason is security. Hidden fields can be tampered with. So you have to explicitly move hidden fields from the request to the form:

  1. form = SQLFORM(..., hidden=dict(a='b'))
  2. form.vars.a = request.vars.a

The process method

SQLFORM inherits the process method from FORM.

SQLFORM and insert/update/delete

SQLFORM creates a new db record when the form is accepted. Assuming

  1. form = SQLFORM(db.test)

, then the id of the last-created record will be accessible in form.vars.id.

If you pass a record as the optional second argument to the SQLFORM constructor, the form becomes an UPDATE form for that record. This means that when the form is submitted the existing record is updated and no new record is inserted. If you set the argument deletable=True, the UPDATE form displays a “Check to delete” checkbox. If checked, the record is deleted.

If a form is submitted and the delete checkbox is checked the attribute form.deleted is set to True.

You can modify the controller of the previous example so that when we pass an additional integer argument in the URL path, as in:

  1. /test/default/display_form/2

and if there is a record with the corresponding id, the SQLFORM generates an UPDATE/DELETE form for the record:

  1. def display_form():
  2.    record = db.person(request.args(0)) or redirect(URL('index'))
  3.    form = SQLFORM(db.person, record)
  4.    if form.process().accepted:
  5.        response.flash = 'form accepted'
  6.    elif form.errors:
  7.        response.flash = 'form has errors'
  8.    return dict(form=form)

Line 2 finds the record and line 3 makes an UPDATE/DELETE form. Line 4 does all the corresponding form processing.

An update form is very similar to a create form except that it is pre-populated with the current record and it previews images. By default deletable = True which means the update form will display a “delete record” option.

Edit forms also contain a hidden INPUT field with name="id" which is used to identify the record. This id is also stored server-side for additional security and, if the visitor tampers with the value of this field, the UPDATE is not performed and web2py raises a SyntaxError, “user is tampering with form”.

When a field is marked with writable=False, the field is not shown in create forms, and it is shown readonly in update forms. If a field is marked as writable=False and readable=False, then the field is not shown at all, not even in update forms.

Forms created with

  1. form = SQLFORM(..., ignore_rw=True)

ignore the readable and writable attributes and always show all fields. Forms in appadmin ignore them by default.

Forms created with

  1. form = SQLFORM(table, record_id, readonly=True)

always show all fields in readonly mode, and they cannot be accepted.

Marking a field with writable=False prevents the field from being part of the form, and causes the form processing to disregard the value of request.vars.field when processing the form. However, if you assign a value to form.vars.field, this value will be part of the insert or update when the form is processed. This enables you to change the value of fields that for some reason you do not wish to include in a form.

SQLFORM in HTML

There are times when you want to use SQLFORM to benefit from its form generation and processing, but you need a level of customization of the form in HTML that you cannot achieve with the parameters of the SQLFORM object, so you have to design the form using HTML.

Now, edit the previous controller and add a new action:

  1. def display_manual_form():
  2.     form = SQLFORM(db.person)
  3.     if form.process(session=None, formname='test').accepted:
  4.         response.flash = 'form accepted'
  5.     elif form.errors:
  6.         response.flash = 'form has errors'
  7.     else:
  8.         response.flash = 'please fill the form'
  9.     # Note: no form instance is passed to the view
  10.     return dict()

and insert the form in the associated “default/display_manual_form.html” view:

  1. {{extend 'layout.html'}}
  2. <form action="#" enctype="multipart/form-data" method="post">
  3. <ul>
  4.   <li>Your name is <input name="name" /></li>
  5. </ul>
  6.   <input type="submit" />
  7.   <input type="hidden" name="_formname" value="test" />
  8. </form>

Notice that the action does not return the form because it does not need to pass it to the view. The view contains a form created manually in HTML. The form contains a hidden field “_formname” that must be the same formname specified as an argument of accepts in the action. web2py uses the form name in case there are multiple forms on the same page, to determine which one was submitted. If the page contains a single form, you can set formname=None and omit the hidden field in the view.

form.accepts will look inside response.vars for data that matches fields in the database table db.person. These fields are declared in the HTML in the format

  1. <input name="field_name_goes_here" />

Note: the HTML form uses POST protocol (i.e. form variables will not be passed on the URL) and multipart/form-data encoding type. The latter is needed for upload fields to work.

SQLFORM and uploads

Fields of type “upload” are special. They are rendered as INPUT fields of type="file". Unless otherwise specified, the uploaded file is streamed in using a buffer, and stored under the “uploads” folder of the application using a new safe name, assigned automatically. The name of this file is then saved into the field of type “upload”.

As an example, consider the following model:

  1. db.define_table('person',
  2.     Field('name', requires=IS_NOT_EMPTY()),
  3.     Field('image', 'upload'))

You can use the same controller action “display_form” shown above.

When you insert a new record, the form allows you to browse for a file. Choose, for example, a jpg image. The file is uploaded and stored as:

  1. applications/test/uploads/person.image.XXXXX.jpg

“XXXXXX” is a random identifier for the file assigned by web2py.

Notice that, by default, the original filename of an uploaded file is b16encoded and used to build the new name for the file. This name is retrieved by the default “download” action and used to set the content disposition header to the original filename.

Only its extension is preserved. This is a security requirement since the filename may contain special characters that could allow a visitor to perform directory traversal attacks or other malicious operations.

The new filename is also stored in form.vars.image.

When editing the record using an UPDATE form, it would be nice to display a link to the existing uploaded file, and web2py provides a way to do it.

If you pass a URL to the SQLFORM constructor via the upload argument, web2py uses the action at that URL to download the file. Consider the following actions:

  1. def display_form():
  2.    record = db.person(request.args(0))
  3.    form = SQLFORM(db.person, record, deletable=True,
  4.                   upload=URL('download'))
  5.    if form.process().accepted:
  6.        response.flash = 'form accepted'
  7.    elif form.errors:
  8.        response.flash = 'form has errors'
  9.    return dict(form=form)
  11. def download():
  12.     return response.download(request, db)

Now, insert a new record at the URL:

  1. http://127.0.0.1:8000/test/default/display_form

Upload an image, submit the form, and then edit the newly created record by visiting:

  1. http://127.0.0.1:8000/test/default/display_form/3

(here we assume the latest record has id=3). The form will display an image preview as shown below:

image

This form, when serialized, generates the following HTML:

  1. <td><label id="person_image__label" for="person_image">Image: </label></td>
  2. <td><div><input type="file" id="person_image" class="upload" name="image"
  3. />[<a href="/test/default/download/person.image.0246683463831.jpg">file</a>|
  4. <input type="checkbox" name="image__delete" />delete]</div></td><td></td></tr>
  5. <tr id="delete_record__row"><td><label id="delete_record__label" for="delete_record"
  6. >Check to delete:</label></td><td><input type="checkbox" id="delete_record"
  7. class="delete" name="delete_this_record" /></td>

which contains a link to allow downloading of the uploaded file, and a checkbox to remove the file from the database record, thus storing NULL in the “image” field.

Why is this mechanism exposed? Why do you need to write the download function? Because you may want to enforce some authorization mechanism in the download function. See Chapter 9 for an example.

Normally uploaded files are stored into “app/uploads” but you can specify an alternate location:

  1. Field('image', 'upload', uploadfolder='...')

In most operating system, accessing the file system can become slow when there are many files in the same folder. If you plan to upload more than 1000 files you can ask web2py to organize the uploads in subfolders:

  1. Field('image', 'upload', uploadseparate=True)

Storing the original filename

web2py automatically stores the original filename inside the new UUID filename and retrieves it when the file is downloaded. Upon download, the original filename is stored in the content-disposition header of the HTTP response. This is all done transparently without the need for programming.

Occasionally you may want to store the original filename in a database field. In this case, you need to modify the model and add a field to store it in:

  1. db.define_table('person',
  2.     Field('name', requires=IS_NOT_EMPTY()),
  3.     Field('image_filename'),
  4.     Field('image', 'upload'))

Then you need to modify the controller to handle it:

  1. def display_form():
  2.     record = db.person(request.args(0)) or redirect(URL('index'))
  3.     url = URL('download')
  4.     form = SQLFORM(db.person, record, deletable=True,
  5.                    upload=url, fields=['name', 'image'])
  6.     if request.vars.image != None:
  7.         form.vars.image_filename = request.vars.image.filename
  8.     if form.process().accepted:
  9.         response.flash = 'form accepted'
  10.     elif form.errors:
  11.         response.flash = 'form has errors'
  12.     return dict(form=form)

Notice that the SQLFORM does not display the “image_filename” field. The “display_form” action moves the filename of the request.vars.image into the form.vars.image_filename, so that it gets processed by accepts and stored in the database. The download function, before serving the file, checks in the database for the original filename and uses it in the content-disposition header.

autodelete

The SQLFORM, upon deleting a record, does not delete the physical uploaded file(s) referenced by the record. The reason is that web2py does not know whether the same file is used/linked by other tables or used for other purpose. If you know it is safe to delete the actual file when the corresponding record is deleted, you can do the following:

  1. db.define_table('image',
  2.     Field('name', requires=IS_NOT_EMPTY()),
  3.     Field('source', 'upload', autodelete=True))

The autodelete attribute is False by default. When set to True is makes sure the file is deleted when the record is deleted.

Links to referencing records

Now consider the case of two tables linked by a reference field. For example:

  1. db.define_table('person',
  2.     Field('name', requires=IS_NOT_EMPTY()))
  3. db.define_table('dog',
  4.     Field('owner', 'reference person'),
  5.     Field('name', requires=IS_NOT_EMPTY()))
  6. db.dog.owner.requires = IS_IN_DB(db, 'person.id', '%(name)s')

A person has dogs, and each dog belongs to an owner, which is a person. The dog owner is required to reference a valid db.person.id by '%(name)s'.

Let’s use the appadmin interface for this application to add a few persons and their dogs.

When editing an existing person, the appadmin UPDATE form shows a link to a page that lists the dogs that belong to the person. This behavior can be replicated using the linkto argument of the SQLFORM. linkto has to point to the URL of a new action that receives a query string from the SQLFORM and lists the corresponding records. Here is an example:

  1. def display_form():
  2.    record = db.person(request.args(0)) or redirect(URL('index'))
  3.    link = URL('list_records', args='db')
  4.    form = SQLFORM(db.person, record, deletable=True, linkto=link)
  5.    if form.process().accepted:
  6.        response.flash = 'form accepted'
  7.    elif form.errors:
  8.        response.flash = 'form has errors'
  9.    return dict(form=form)

Here is the page:

image

There is a link called “dog.owner”. The name of this link can be changed via the labels argument of the SQLFORM, for example:

  1. labels = {'dog.owner':"This person's dogs"}

If you click on the link you get directed to:

  1. /test/default/list_records/db/dog?query=db.dog.owner%3D%3D3

“list_records” is the specified action, with request.args(0) set to the name of the referencing table and request.vars.query set to the SQL query string. The query string in the URL contains the value “dog.owner==3” appropriately url-encoded (web2py decodes this automatically when the URL is parsed).

You can easily implement a very general “list_records” action as follows:

  1. def list_records():
  2.     import re
  3.     REGEX = re.compile(r'^(\w+).(\w+).(\w+)==(\d+)$')
  4.     match = REGEX.match(request.vars.query)
  5.     if not match:
  6.         redirect(URL('error'))
  7.     table, field, id = match.group(2), match.group(3), match.group(4)
  8.     records = db(db[table][field]==id).select()
  9.     return dict(records=records)

with the associated “default/list_records.html” view:

  1. {{extend 'layout.html'}}
  2. {{=records}}

When a set of records is returned by a select and serialized in a view, it is first converted into a SQLTABLE object (not the same as a Table) and then serialized into an HTML table, where each field corresponds to a table column.

Pre-populating the form

It is always possible to pre-populate a form using the syntax:

  1. form.vars.name = 'fieldvalue'

Statements like the one above must be inserted after the form declaration and before the form is accepted, whether or not the field (“name” in the example) is explicitly visualized in the form.

Adding extra form elements to SQLFORM

Sometimes you may wish to add an extra element to your form after it has been created. For example, you may wish to add a checkbox which confirms the user agrees with the terms and conditions of your website:

  1. form = SQLFORM(db.yourtable)
  2. my_extra_element = TR(LABEL('I agree to the terms and conditions'),
  3.                       INPUT(_name='agree', value=True, _type='checkbox'))
  4. form[0].insert(-1, my_extra_element)

The variable my_extra_element should be adapted to the formstyle. In this example, the default formstyle='table3cols' has been assumed.

After submission, form.vars.agree will contain the status of the checkbox, which could then be used in an onvalidation function, for instance.

SQLFORM without database IO

There are times when you want to generate a form from a database table using SQLFORM and you want to validate a submitted form accordingly, but you do not want any automatic INSERT/UPDATE/DELETE in the database. This is the case, for example, when one of the fields needs to be computed from the value of other input fields. This is also the case when you need to perform additional validation on the inserted data that cannot be achieved via standard validators.

This can be done easily by breaking:

  1. form = SQLFORM(db.person)
  2. if form.process().accepted:
  3.     response.flash = 'record inserted'

into:

  1. form = SQLFORM(db.person)
  2. if form.validate():
  3.     ### deal with uploads explicitly
  4.     form.vars.id = db.person.insert(**dict(form.vars))
  5.     response.flash = 'record inserted'

The same can be done for UPDATE/DELETE forms by breaking:

  1. form = SQLFORM(db.person, record)
  2. if form.process().accepted:
  3.     response.flash = 'record updated'

into:

  1. form = SQLFORM(db.person, record)
  2. if form.validate():
  3.     if form.deleted:
  4.         db(db.person.id==record.id).delete()
  5.     else:
  6.         form.record.update_record(**dict(form.vars))
  7.     response.flash = 'record updated'

In the case of a table including an “upload”-type field (“fieldname”), both process(dbio=False) and validate() deal with the storage of the uploaded file as if process(dbio=True), the default behavior.

The name assigned by web2py to the uploaded file can be found in:

  1. form.vars.fieldname