Module dal

Field type validation, using web2py's validators mechanism.

makes sure the content of a field is in line with the declared fieldtype

 >>> 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('jsonf', 'json'),              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,                       jsonf={"j": True},                       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.person[2].name
 'Massimo'
 >>> db.person(2).name
 'Massimo'
 >>> db.person(name='Massimo').name
 'Massimo'
 >>> db.person(db.person.name=='Massimo').name
 'Massimo'
 >>> row = db.person[2]
 >>> row.name == row['name'] == row['person.name'] == row('person.name')
 True
 >>> 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")
 <Row {'name': 'Max', 'birth': datetime.date(1971, 12, 21), 'id': 2}>
 >>> 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).delete()
 0
 >>> for i in range(10): tmp = mynumber.insert(x=i)
 >>> db(mynumber).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()
 

DEFAULTLENGTH

Value:

{'blob': 2147483648, 'password': 512, 'string': 512, 'text': 32768, 'upload': 512}

SPATIALLIBS

Value:

{'Darwin': 'libspatialite.dylib', 'Linux': 'libspatialite.so', 'Windows': 'libspatialite'}

CALLABLETYPES

Value:

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

TABLE_ARGS

Value:

set(['common_filter', 'fake_migrate', 'fields', 'format', 'migrate', 'on_define', 'plural', 'polymodel', ...

SELECT_ARGS

Value:

set(['cache', 'cacheable', 'distinct', 'for_update', 'groupby', 'having', 'join', 'left', ...

REGEX_UPLOAD_PATTERN

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)$')

REGEX_SEARCH_PATTERN

Value:

re.compile(r'^\{[^\.]+\.[^\.]+(\.(lt|gt|le|ge|eq|ne|contains|startswit h|year|month|day|hour|minute|second))?(\.not)?\}$')

DRIVERS

Value:

['SQLite(sqlite3)', 'MySQL(pymysql)', 'MySQL(MySQLdb)', 'MySQL(mysqlconnector)', 'PostgreSQL(psycopg2)', 'PostgreSQL(pg8000)', 'MongoDB(pymongo)', 'IMAP(imaplib)']

PLURALIZE_RULES

Value:

[(re.compile(r'child$'), re.compile(r'child$'), 'children'), (re.compile(r'oot$'), re.compile(r'oot$'), 'eet'), (re.compile(r'ooth$'), re.compile(r'ooth$'), 'eeth'), (re.compile(r'l[eo]af$'), re.compile(r'l([eo])af$'), 'l\\1aves'), (re.compile(r'sis$'), re.compile(r'sis$'), 'ses'), (re.compile(r'man$'), re.compile(r'man$'), 'men'), (re.compile(r'ife$'), re.compile(r'ife$'), 'ives'), (re.compile(r'eau$'), re.compile(r'eau$'), 'eaux'), ...

ADAPTERS

Value:

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