web2pyTM Database Abstration Layer (DAL)

The DAL API are exposed in web2py models, controllers and views but you can access them anywhere with 

1.
from gluon.sql import *

Examples

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
    >>> 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',
              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<'2003-01-01')).select())
    1
    >>> len(db((db.person.name=='Max')|(db.person.birth<'2003-01-01')).select())
    1
    >>> 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()

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).

Mind there are little idiosyncrasies like the fact that "user" is not a valid field name in PostgreSQL, or the fact that sqlite3 will ignore the type of a field and allow you to put anything in it despite the declared type. Every database backend has its own keywords that may conflict with your tablenames.



SQL + HTML Examples



Given 
1.
rows=db().select(db.users.ALL)
  • SQLTABLE(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 to specify which fields should be displayed in the form. Only those fields will be validated.
  • SQLFORM(....,labels=['name':'Your Name']) allows to change the labels of the listed fields.

A form=SQLFORM(...) object has one method of practical interest: form.accepts(request.vars,session) that processed the input variables (in request.vars) within the session and returns true if the form is valid, false otherwise. Processed variables are in form.vars and errors are in form.errors. The form is modified accordingly. If the form is accepted, accepts also perform the appropriate insert/update/delete in the database.