Web2py DAL Quick Reference

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)&amp;(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.
Advertisements

15 thoughts on “Web2py DAL Quick Reference

  1. Pingback: Web2py resources at Tales of Ordinary Madness

  2. thanks a lot, but i did not find one thing:
    when the request is written like this :
    for row in dbRH(dbRH.test.login==”Laurent”).select():
    firstname = row.firstname
    It works .
    Now i want to replace “Laurent” by” myvar” and it doesn’t work , i did not see an example of select with a var

    thank you

  3. Great summary!
    I’m newbie on web2py and i like it. You have so many examples – but when you allow will start a little question:
    1. i use in my web2py project default DAL
    2. i connect to an external mysql database with DAL, and here are your statments very helpful
    3. but now, i would connect to a mysql database view (table view) – so i can’t normal use the Field Constructor routine – because web2py (DAL) create an ID for every tables – but i don’t need for my database view an ID.

    Have you any idea – sorry my english isn’t the best – but i hope you can understand my problem?!

    • Dear Vertiefer,

      I suggest you MAKE your “mysql database view (table view) ” have an ID field specified something like ‘id INTEGER PRIMARY KEY’. That way the 2 will match. I did something similar for PostgresSQL.

      Sorry for the delay in the answer.

      Love and peace,
      Joe

  4. Pingback: Useful Links for web2py Beginners | pythonmarketer

  5. Hello, I have a query. I get AttributeError when I say db.person. I have not defined it in current session, but created it via appadmin via UI. Database is Mysql. Any clue, how to access table contents ?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s