Field constructor

These are the default values of a Field constructor:

Field(fieldname, type='string', length=None, default=DEFAULT,
      required=False, requires=DEFAULT,
      ondelete='CASCADE', notnull=False, unique=False,
      uploadfield=True, widget=None, label=None, comment=None,
      writable=True, readable=True, searchable=True, listable=True,
      update=None, authorize=None, autodelete=False, represent=None,
      uploadfolder=None, uploadseparate=None, uploadfs=None,
      compute=None, filter_in=None, filter_out=None,
      custom_qualifier=None, map_none=None, rname=None)

where DEFAULT is a special value used to allow the value None for a parameter.

Not all of them are relevant for every field. length is relevant only for fields of type “string”. uploadfield, authorize, and autodelete are relevant only for fields of type “upload”. ondelete is relevant only for fields of type “reference” and “upload”.

• length sets the maximum length of a “string”, “password” or “upload” field. If length is not specified a default value is used but the default value is not guaranteed to be backward compatible. To avoid unwanted migrations on upgrades, we recommend that you always specify the length for string, password and upload fields.
• default sets the default value for the field. The default value is used when performing an insert if a value is not explicitly specified. It is also used to pre-populate forms built from the table using SQLFORM. Note, rather than being a fixed value, the default can instead be a function (including a lambda function) that returns a value of the appropriate type for the field. In that case, the function is called once for each record inserted, even when multiple records are inserted in a single transaction.
• required tells the DAL that no insert should be allowed on this table if a value for this field is not explicitly specified.
• requires is a validator or a list of validators. This is not used by the DAL, but it is used by SQLFORM. The default validators for the given types are shown in the next section.

Notice that requires=... is enforced at the level of forms, required=True is enforced at the level of the DAL (insert), while notnull, unique and ondelete are enforced at the level of the database. While they sometimes may seem redundant, it is important to maintain the distinction when programming with the DAL.

• rname provides the field with a “real name”, a name for the field known to the database adapter; when the field is used, it is the rname value which is sent to the database. The web2py name for the field is then effectively an alias.

• ondelete translates into the “ON DELETE” SQL statement. By default it is set to “CASCADE”. This tells the database that when it deletes a record, it should also delete all records that refer to it. To disable this feature, set ondelete to “NO ACTION” or “SET NULL”.

• notnull=True translates into the “NOT NULL” SQL statement. It prevents the database from inserting null values for the field.
• unique=True translates into the “UNIQUE” SQL statement and it makes sure that values of this field are unique within the table. It is enforced at the database level.
• uploadfield applies only to fields of type “upload”. A field of type “upload” stores the name of a file saved somewhere else, by default on the filesystem under the application “uploads/“ folder. If uploadfield is set to True, then the file is stored in a blob field within the same table and the value of uploadfield is the name of the blob field. This will be discussed in more detail later in the More on uploads section in this chapter.

• uploadfolder sets the folder for uploaded files. By default, an uploaded file goes into the application’s “uploads/“ folder, that is into os.path.join(request.folder, 'uploads') (this seems not the case for MongoAdapter at present). For example:

  Field(..., uploadfolder=os.path.join(request.folder, 'static/temp'))

  will upload files to the “web2py/applications/myapp/static/temp” folder.

• uploadseparate if set to True will upload files under different subfolders of the uploadfolder folder. This is optimized to avoid too many files under the same folder/subfolder. ATTENTION: You cannot change the value of uploadseparate from True to False without breaking links to existing uploads. web2py either uses the separate subfolders or it does not. Changing the behavior after files have been uploaded will prevent web2py from being able to retrieve those files. If this happens it is possible to move files and fix the problem but this is not described here.

• uploadfs allows you specify a different file system where to upload files, including an Amazon S3 storage or a remote SFTP storage.

  You need to have PyFileSystem installed for this to work. uploadfs must point to PyFileSystem.

• autodelete determines if the corresponding uploaded file should be deleted when the record referencing the file is deleted. For “upload” fields only. However, records deleted by the database itself due to a CASCADE operation will not trigger web2py’s autodelete. The web2py Google group has workaround discussions.

• widget must be one of the available widget objects, including custom widgets, for example: SQLFORM.widgets.string.widget. A list of available widgets will be discussed later. Each field type has a default widget.
• label is a string (or a helper or something that can be serialized to a string) that contains the label to be used for this field in auto-generated forms.
• comment is a string (or a helper or something that can be serialized to a string) that contains a comment associated with this field, and will be displayed to the right of the input field in the autogenerated forms.
• writable declares whether a field is writable in forms.
• readable declares whether a field is readable in forms. If a field is neither readable nor writable, it will not be displayed in create and update forms.
• searchable declares whether a field is searchable in grids (SQLFORM.grid and SQLFORM.smartgrid are described in Chapter 7). Notice that a field must also be readable to be searched.
• listable declares whether a field is visible in grids (when listing multiple records)
• update contains the default value for this field when the record is updated.
• compute is an optional function. If a record is inserted or updated, the compute function will be executed and the field will be populated with the function result. The record is passed to the compute function as a dict, and the dict will not include the current value of that, or any other compute field.
• authorize can be used to require access control on the corresponding field, for “upload” fields only. It will be discussed more in detail in the context of Authentication and Authorization.

• represent can be None or can point to a function that takes a field value and returns an alternate representation for the field value. Examples:

  db.mytable.name.represent = lambda name, row: name.capitalize()
  db.mytable.other_id.represent = lambda oid, row: row.myfield
  db.mytable.some_uploadfield.represent = lambda val, row: A('get it', _href=URL('download', args=val))

• filter_in and filter_out can be set to callables for further processing of field’s value. filter_in is passed the field’s value to be written to the database before an insert or update while filter_out is passed the value retrieved from the database before field assignment. The value returned by the callable is then used. See filter_in and filter_out section in this chapter.

• custom_qualifier is a custom SQL qualifier for the field to be used at table creation time (cannot use for field of type “id”, “reference”, or “big-reference”).

Field types

Decimal requires and returns values as Decimal objects, as defined in the Python decimal module. SQLite does not handle the decimal type so internally we treat it as a double. The (n,m) are the number of digits in total and the number of digits after the decimal point respectively.

The big-id and, big-reference are only supported by some of the database engines and are experimental. They are not normally used as field types unless for legacy tables, however, the DAL constructor has a bigint_id argument that when set to True makes the id fields and reference fields big-id and big-reference respectively.

The list:<type> fields are special because they are designed to take advantage of certain denormalization features on NoSQL (in the case of Google App Engine NoSQL, the field types ListProperty and StringListProperty) and back-port them all the other supported relational databases. On relational databases lists are stored as a text field. The items are separated by a | and each | in string item is escaped as a ||. They are discussed in list: and contains section in this chapter.

The json field type is pretty much explanatory. It can store any json serializable object. It is designed to work specifically for MongoDB and backported to the other database adapters for portability.

blob fields are also special. By default, binary data is encoded in base64 before being stored into the actual database field, and it is decoded when extracted. This has the negative effect of using 33% more storage space than necessary in blob fields, but has the advantageof making the communication independent of back-end-specific escaping conventions.

Run-time field and table modification

Most attributes of fields and tables can be modified after they are defined:

>>> db.define_table('person', Field('name', default=''), format='%(name)s')
<Table person (id, name)>
>>> db.person._format = '%(name)s/%(id)s'
>>> db.person.name.default = 'anonymous'

notice that attributes of tables are usually prefixed by an underscore to avoid conflict with possible field names.

You can list the tables that have been defined for a given database connection:

>>> db.tables
['person']

You can query for the type of a table:

>>> type(db.person)
<class 'pydal.objects.Table'>

You can access a table using different syntaxes:

>>> db.person is db['person']
True

You can also list the fields that have been defined for a given table:

>>> db.person.fields
['id', 'name']

Similarly you can access fields from their name in multiple equivalent ways:

>>> type(db.person.name)
<class 'pydal.objects.Field'>
>>> db.person.name is db.person['name']
True

Given a field, you can access the attributes set in its definition:

>>> db.person.name.type
string
>>> db.person.name.unique
False
>>> db.person.name.notnull
False
>>> db.person.name.length
32

including its parent table, tablename, and parent connection:

>>> db.person.name._table == db.person
True
>>> db.person.name._tablename == 'person'
True
>>> db.person.name._db == db
True

A field also has methods. Some of them are used to build queries and we will see them later. A special method of the field object is validate and it calls the validators for the field.

>>> db.person.name.validate('John')
('John', None)

which returns a tuple (value, error). error is None if the input passes validation.