The DAL: A quick tour

web2py defines the following classes that make up the DAL:

The DAL object represents a database connection. For example:

db = DAL('sqlite://storage.sqlite')

Table represents a database table. You do not directly instantiate Table; instead, DAL.define_table instantiates it.

db.define_table('mytable', Field('myfield'))

The most important methods of a Table are:

insert, truncate, drop, and import_from_csv_file.

Field represents a database field. It can be instantiated and passed as an argument to DAL.define_table.

DAL Rows

is the object returned by a database select. It can be thought of as a list of Row rows:

rows = db(db.mytable.myfield != None).select()

Row contains field values.

for row in rows:
    print row.myfield

Query is an object that represents a SQL “where” clause:

myquery = (db.mytable.myfield != None) | (db.mytable.myfield > 'A')

Set is an object that represents a set of records. Its most important methods are count, select, update, and delete. For example:

myset = db(myquery)
rows = myset.select()
myset.update(myfield='somevalue')
myset.delete()

Expression is something like an orderby or groupby expression. The Field class is derived from the Expression. Here is an example.

myorder = db.mytable.myfield.upper() | db.mytable.id
db().select(db.table.ALL, orderby=myorder)