Module dal

Field type validation, using web2py's validators mechanism.

 >>> if len(sys.argv)<2: db = DAL("sqlite://test.db")
 >>> if len(sys.argv)>1: db = DAL(sys.argv[1])
 >>> tmp = db.define_table('users',              Field('stringf', 'string', length=32, required=True),              Field('booleanf', 'boolean', default=False),              Field('passwordf', 'password', notnull=True),              Field('uploadf', 'upload'),              Field('blobf', 'blob'),              Field('integerf', 'integer', unique=True),              Field('doublef', 'double', unique=True,notnull=True),              Field('datef', 'date', default=datetime.date.today()),              Field('timef', 'time'),              Field('datetimef', 'datetime'),              migrate='test_user.table')

Insert a field

 >>> db.users.insert(stringf='a', booleanf=True, passwordf='p', blobf='0A',                       uploadf=None, integerf=5, doublef=3.14,                       datef=datetime.date(2001, 1, 1),                       timef=datetime.time(12, 30, 15),                       datetimef=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',              Field('name'),              Field('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')

 commented len(db().select(db.person.ALL))
 commented 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
 1
 >>> db(db.person.name=='Marco').select().first().delete_record() # test delete
 1

 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<datetime.date(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=db.person.name, 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',               Field('name'),               Field('birth','date'),               Field('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

 >>> len(db().select(db.person.ALL, db.dog.name,left=db.dog.on(db.dog.owner==db.person.id)))
 1

 Drop tables

 >>> db.dog.drop()
 >>> db.person.drop()

 Example of many 2 many relation and Set

 >>> tmp = db.define_table('author', Field('name'),                            migrate='test_author.table')
 >>> tmp = db.define_table('paper', Field('title'),                            migrate='test_paper.table')
 >>> tmp = db.define_table('authorship',            Field('author_id', db.author),            Field('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 Set

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

 Example of expressions

 >>> mynumber = db.define_table('mynumber', Field('x', 'integer'))
 >>> db(mynumber.id>0).delete()
 0
 >>> for i in range(10): tmp = mynumber.insert(x=i)
 >>> db(mynumber.id>0).select(mynumber.x.sum())[0](mynumber.x.sum())
 45

 >>> db(mynumber.x+2==5).select(mynumber.x + 2)[0](mynumber.x + 2)
 5

 Output in csv

 >>> print str(authored_papers.select(db.author.name, db.paper.title)).strip()
 author.name,paper.title
 Massimo,QCD

 Delete all leftover tables

 >>> DAL.distributed_transaction_commit(db)

 >>> db.mynumber.drop()
 >>> db.authorship.drop()
 >>> db.author.drop()
 >>> db.paper.drop()
 

CALLABLETYPES

Value:

(<type 'function'>, <type 'function'>, <type 'builtin_function_or_method'>, <type 'instancemethod'>, <type 'builtin_function_or_method'>)

regex_content

Value:

re.compile(r'(?P<table>[\w-]+)\.(?P<field>[\w-]+)\.(?P<uuidkey>[\w-]+) \.(?P<name>\w+)\.\w+$')

regex_python_keywords

Value:

re.compile(r'^(and|del|from|not|while|as|elif|global|or|with|assert|el se|if|pass|yield|break|except|import|print|class|exec|in|raise|continu e|finally|is|return|def|for|lambda|try)$')

ADAPTERS

Value:

{'couchdb': <class 'web2py.gluon.dal.CouchDBAdapter'>, 'cubrid': <class 'web2py.gluon.dal.CubridAdapter'>, 'db2': <class 'web2py.gluon.dal.DB2Adapter'>, 'firebird': <class 'web2py.gluon.dal.FireBirdAdapter'>, 'firebird_embedded': <class 'web2py.gluon.dal.FireBirdAdapter'>, 'gae': <class 'web2py.gluon.dal.GoogleDatastoreAdapter'>, 'google:datastore': <class 'web2py.gluon.dal.GoogleDatastoreAdapter'> , ...