Background
The following was referred to here in a post by Iceberg on May 4, 2009 at 9:14 AM on the web2py-users google group. It is a merged version of the old and new DAL info.
I couldn’t find it published anywhere so I am publishing it here so I can refer to it. Some of it is here.
web2pyTM Database Abstraction Layer (DAL)
Quick Reference
define_table, insert, count, delete, update
db = SQLDB('postgres://user:password@hostname/db', pools=10)
db.define_table('person',db.<a href="http://127.0.0.1:8000/examples/global/vars/Field">Field</a>('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 SQLDB
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
db.Field(name, 'string') db.Field(name, 'text') db.Field(name, 'password') db.Field(name, 'blob') db.Field(name, 'upload') db.Field(name, 'boolean') db.Field(name, 'integer') db.Field(name, 'double') db.Field(name, 'time') db.Field(name, 'date') db.Field(name, 'datetime') db.Field(name, db.referenced_table) # reference field
Valid Field Attributes
length (only for string type, defaults to 32) default (defaults to None) required (defaults to False) notnull (defaults to False) requires (<a href="http://127.0.0.1:8000/examples/default/api">validator or list of validators</a>, 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)
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 accessibled via the admin/design interface. Migration can be turned off on a per-table basis by passing migrate=False to define_table.
Select Attributes
rows = db(query).select(*fields, orderby=..., left=..., groupby=..., having=..., limitby=..., cache=...)
DAL Shortcuts
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</pre>
Truncate and Drop a Table
db.person.truncate() db.person.drop()
**Reference Fields and Inner Joins*
db.define_table('dog',db.<a href="http://127.0.0.1:8000/examples/global/vars/Field">Field</a>('name'))
db.define_table('friendship', db.<a href="http://127.0.0.1:8000/examples/global/vars/Field">Field</a>('person',db.person), db.<a href="http://127.0.0.1:8000/examples/global/vars/Field">Field</a>('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
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
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
query = db.person.id.belongs(db()._select(db.friendship.person)
Aggregates
rows=db(friends).select(db.person.name,db.dog.id.count(),groupby=db.dog.id)
''' Notes:
Aggregate functions:
db.table.field.count()
db.table.field.max()
db.table.field.min()
db.table.field. sum()
'''
Aliases
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
rows=db().select(db.person.ALL,cache=(cache.ram,3600))
''' Notes:
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/Output
# CVS Input db.person.import_from_csv_file(open(filename,’rb’)) # CVS Output str(rows)
HTML Output
print rows.xml()
Set Field Validators
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
form = SQLFORM(db.friendship)
if form.accepts(request.vars, session): response.flash='record inserted'
elif form.errors: response.flash='form errors'
''' Note:
The form can then be displayed in a view with:
{{=form}}
'''
SQL + HTML Examples
# Given:
rows=db().select(db.users.ALL)
# Then:
SQLFORM (rows) # turns the rows into a CSS friendly table
SQLFORM(db.users) # makes an input form for users
SQLFORM(db.users,rows[i]) # makes an edit form for the user in rows[i]
SQLFORM(db.users,rows[i],deletable=True) # makes an edit/delete form for the user in row[i]
SQLFORM(....,fields=['name','email']) # allows defining which fields should be displayed in the form. Only those fields will be validated.
SQLFORM(....,labels=['name':'Your Name']) # allows changing the labels of the listed fields.
''' Notes:
SQLFORM
-------
A form=SQLFORM(…) object has one method of practical interest:
form.accepts(request.vars,session)
The above method:
1. Processes the input variables (in request.vars) in the session
2. Returns true if the form is valid, false otherwise.
- Processed variables are in form.vars
- Errors are in form.errors.
- The form is modified accordingly.
- If the form is accepted:
accept also performs the appropriate insert/update/delete in the database.
Extended Usage
--------------
The DAL API is automatically exposed in web2py
models,
controllers and
views
but you can access them anywhere with:
'''
from gluon.sql import *
Doctest Examples
These are old syntax but good examples. They use:
- SQLDB instead of DAL and
- SQLField instead of Field
However I believe they are still useful.
>>> db=SQLDB("sqlite://test.db")
>>> #OR db=SQLDB("mysql://username:password@host:port/dbname")
>>> #OR db=SQLDB("postgres://username:password@host:port/dbname")
# syntax: SQLField('fieldname','fieldtype',length=32,
# required=False, default=None,
# requires=[IS_EMAIL(error_message='invalid email')])
>>> tmp=db.define_table('users',
# But notice that SQLField(...) is depreciated and you'd better use db.Field(...) instead
SQLField('stringfield','string',length=32,required=True),
SQLField('booleanfield','boolean',default=False),
SQLField('passwordfield','password'),
SQLField('textfield','text'),
SQLField('blobfield','blob'),
SQLField('uploadfield','upload'),
SQLField('integerfield','integer'),
SQLField('doublefield','double'),
SQLField('datefield','date',default=datetime.date.today()),
SQLField('timefield','time'),
SQLField('datetimefield','datetime'),
migrate='test_user.table')
# Insert a field
>>> db.users.insert(stringfield='a',booleanfield=True,
passwordfield='p',textfield='x',blobfield='x',
uploadfield=None, integerfield=5,doublefield=3.14,
datefield=datetime.date(2001,1,1),
timefield=datetime.time(12,30,15),
datetimefield=datetime.datetime(2002,2,2,12,30,15))
1
# Drop the table
>>> db.users.drop()
# Examples of insert, select, update, delete
>>> tmp=db.define_table('person',
SQLField('name'),
SQLField('birth','date'),
migrate='test_person.table')
>>> person_id=db.person.insert(name="Marco",birth='2005-06-22')
>>> person_id=db.person.insert(name="Massimo",birth='1971-12-21')
>>> len(db().select(db.person.ALL))
2
>>> me=db(db.person.id==person_id).select()[0] # test select
>>> me.name
'Massimo'
>>> db(db.person.name=='Massimo').update(name='massimo') # test update
>>> db(db.person.name=='Marco').delete() # test delete
# Update a single record
>>> me.update_record(name="Max")
>>> me.name
'Max'
# Examples of complex search conditions
>>> len(db((db.person.name=='Max')&(db.person.birth>> len(db((db.person.name=='Max')|(db.person.birth>> me=db(db.person.id==person_id).select(db.person.name)[0]
>>> me.name
'Max'
# Examples of search conditions using extract from date/datetime/time
>>> len(db(db.person.birth.month()==12).select())
1
>>> len(db(db.person.birth.year()>1900).select())
1
# Example of usage of NULL
>>> len(db(db.person.birth==None).select()) ### test NULL
0
>>> len(db(db.person.birth!=None).select()) ### test NULL
1
# Examples of search conditions using lower, upper, and like
>>> len(db(db.person.name.upper()=='MAX').select())
1
>>> len(db(db.person.name.like('%ax')).select())
1
>>> len(db(db.person.name.upper().like('%AX')).select())
1
>>> len(db(~db.person.name.upper().like('%AX')).select())
0
# orderby, groupby and limitby
>>> people=db().select(db.person.name,orderby=db.person.name)
>>> order=db.person.name|~db.person.birth
>>> people=db().select(db.person.name,orderby=order)
>>> people=db().select(db.person.name,orderby=order,groupby=db.person.name)
>>> people=db().select(db.person.name,orderby=order,limitby=(0,100))
# Example of one 2 many relation
>>> tmp=db.define_table('dog',
SQLField('name'),
SQLField('birth','date'),
SQLField('owner',db.person),
migrate='test_dog.table')
>>> db.dog.insert(name='Snoopy',birth=None,owner=person_id)
1
# A simple JOIN
>>> len(db(db.dog.owner==db.person.id).select())
1
# Drop tables
>>> db.dog.drop()
>>> db.person.drop()
# Example of many 2 many relation and SQLSet
>>> tmp=db.define_table('author',SQLField('name'),
migrate='test_author.table')
>>> tmp=db.define_table('paper',SQLField('title'),
migrate='test_paper.table')
>>> tmp=db.define_table('authorship',
SQLField('author_id',db.author),
SQLField('paper_id',db.paper),
migrate='test_authorship.table')
>>> aid=db.author.insert(name='Massimo')
>>> pid=db.paper.insert(title='QCD')
>>> tmp=db.authorship.insert(author_id=aid,paper_id=pid)
# Define a SQLSet
>>> authored_papers=db((db.author.id==db.authorship.author_id)&
(db.paper.id==db.authorship.paper_id))
>>> rows=authored_papers.select(db.author.name,db.paper.title)
>>> for row in rows: print row.author.name, row.paper.title
Massimo QCD
# Example of search condition using belongs
>>> set=(1,2,3)
>>> rows=db(db.paper.id.belongs(set)).select(db.paper.ALL)
>>> print rows[0].title
QCD
# Example of search condition using nested select
>>> nested_select=db()._select(db.authorship.paper_id)
>>> rows=db(db.paper.id.belongs(nested_select)).select(db.paper.ALL)
>>> print rows[0].title
QCD
# Output in csv
>>> str(authored_papers.select(db.author.name,db.paper.title))
author.name,paper.title
Massimo,QCD
# Delete all leftover tables
>>> db.authorship.drop()
>>> db.author.drop()
>>> db.paper.drop()
# Commit or rollback your work
>>> db.commit() # or db.rollback()
''' Notes:
migrate can be:
False (do not create/alter tables),
True (create/alter tables) or
a filename (create/alter tables and store migration information in the file).
There are little idiosyncrasies in every backend like the fact that:
"user" is not a valid field name in PostgreSQL, or
that sqlite3 will ignore the type of a field and allow you to put anything in it despite the declared type.
Each database has its own keywords that may conflict with your tablenames.
Joe Codeswell - Notes to Myself and Others 