web2py^TM Database Abstraction Layer (DAL)

Examples

define_table, insert, count, delete, update

1.
2.
3.
4.
5.
6.
7.
8.
9.

db = DAL(‘postgres://user:password@hostname/db’, pool_size=10)
db.define_table(‘person’,Field(’name’,’string’))
id= db.person.insert(name=’max’)
query=(db.person.id==id)
db(query).count()
db(query).delete()
db(query).update(name=’Max’)
rows = db(query).select(orderby=db.person.name)
for row in rows: print row.name

Examples of uri strings for DAL

sqlite://test.db
mysql://user:password@localhost/database
postgres://user:password@localhost/database
mssql://user:password@host/database
firebird://user:password@server:3050/database
oracle://user/password@database

Valid field Types

Field(name, 'string')
Field(name, 'text')
Field(name, 'password')
Field(name, 'blob')
Field(name, 'upload')
Field(name, 'boolean')
Field(name, 'integer')
Field(name, 'double')
Field(name, 'time')
Field(name, 'date')
Field(name, 'datetime')
Field(name, db.referenced_table) # reference field

Valid Field Attribute

length (only for string type, defaults to 32)
default (defaults to None)
required (defaults to False)
notnull (defaults to False)
unique (defaults to False)
requires (validator or list of validators, for forms)
comment (for forms)
widget (for forms)
represent (for forms)
readable (for forms)
writable (for forms)
update (default value if the record is updated)
uploadfield (for upload fields)
authorize (for upload fields, function to be used if data can be downloaded, see authentication)
autodelete (for upload fields, if set to true linked uploaded images are removed upon deletion of the record)
label (for forms)

On Migrations

Changing the list of fields or field types in a model, triggers an automatic migration, i.e. web2py generates SQL to alter the table accordingly. If the table does not exist it is created. Migration actions are logged in the file sql.log accessible via the admin/design interface. Migration can be turned off on a per-table basis by passing migrate=False to define_table.

Select Attributes

1.	rows = db(query).select(*fields, orderby=..., left=..., groupby=..., having=..., limitby=..., cache=...)

Shortcuts

1.
2.
3.
4.
5.
6.
7.

db['person']                   ### db.person
db.person['name']              ### db.person.name
db['person']['name']           ### db.person.name
db.person[0]=dict(name='Max')  ### insert
db.person[id]=dict(name='Max') ### update by db.person.id
print db.person[id]            ### select by db.person.id
del db.person[id]              ### delete by db.person.id

Truncate and Drop a table

1.
2.

db.person.truncate()
db.person.drop()

Reference Fields Inner joins

1.
2.
3.
4.
5.
6.

db.define_table(‘dog’,Field(’name’))
db.define_table(‘friendship’, Field(’person’,db.person), Field(‘dog’,db.dog))
db.friendship.insert(person=id, dog=db.dog.insert(name=’Snoopy’))
friends=(db.person.id==db.friendship.person)&(db.dog.id==db.friendship.dog)
rows = db(friends).select(db.person.name, db.dog.name)
for row in rows: print row.person.name, ’is friend of’, row.dog.name

Left Outer Joins

1.
2.
3.
4.

query=(db.person.id>0)
friends=(db.person.id==db.friendship.person)&(db.dog.id==db.friendship.dog)
rows = db(query).select(db.person.name, db.dog.name, left=db.dog.on(friends))
for row in rows: print row.person.name, ’is friend of’, row.dog.name or ‘nobody’

Complex queries

1.
2.
3.
4.
5.
6.

query = (db.person.id==1)|((db.person.id==2)&(db.person.name==’Max’))
query = (db.person.id==db.friendship.person)&(db.dog.id==db.friendship.dog)
query = db.person.name.lower().like(‘m%’)
query = db.person.id.belongs(('max','Max','MAX'))
query = db.person.birth.year()+1==2008
rows = db(query).select()

Nested selects

1.	query = db.person.id.belongs(db()._select(db.friendship.person)

Aggregates

1.	rows=db(friends).select(db.person.name,db.dog.id.count(),groupby=db.dog.id)

Aggregate functions db.table.field.count(), .max(), .min(), sum().

Aliases

1.
2.
3.
4.
5.

person=db.person
friendship=db.friendship
puppy=db.dog.with_alias('puppy')
query=(puppy.id==friendhip.dog)&(friendship.person==person.id)
rows=db().select(person.name,puppy.name,left=puppy.on(query))

Caching

1.	rows=db().select(db.person.ALL,cache=(cache.ram,3600))

cache=(model,cache_timeout) where model can be cache.ram, cache.disk, cache.memcache or user defined caching model, cache_timeout is in seconds.

CSV Input

1.	db.person.import_from_csv_file(open(filename,’rb’))

CSV Output

1.

str(rows)

HTML output

1.	print rows.xml()

Set field validators

1.
2.

db.person.name.requires=IS_NOT_IN_DB(db,db.person.name)
db.friendship.person.requires=IS_IN_DB(db,db.person.id,’%(name)s’)

Generate and process a form from a model

1.
2.
3.

form = SQLFORM(db.friendship)
if form.accepts(request.vars, session): response.flash=’record inserted’
elif form.errors: response.flash=’form errors’

The form can then be displayed in a view with:

1.

{{=form}}

web2pyTM Database Abstraction Layer (DAL)