Create a table with all possible field types
'sqlite://test.db'
'mysql://root:none@localhost/test'
'postgres://mdipierro:none@localhost/test'
'mssql://web2py:none@A64X2/web2py_test'
'oracle://username:password@database'
'firebird://user:password@server:3050/database'
'db2://DSN=dsn;UID=user;PWD=pass'
'firebird_embedded://username:password@c://path')
'informix://user:password@server:3050/database'
'gae' # for google app engine
>>> if len(sys.argv)<2: db = SQLDB("sqlite://test.db")
>>> if len(sys.argv)>1: db = SQLDB(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('blobf', 'blob'), Field('uploadf', 'upload'), 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')
>>> 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
1
>>> db(db.person.name=='Marco').delete() # 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 consitions 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]._extra[mynumber.x.sum()]
45
>>> db(mynumber.x+2==5).select(mynumber.x + 2)[0]._extra[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
# >>> SQLDB.distributed_transaction_commit(db)
>>> db.mynumber.drop()
>>> db.authorship.drop()
>>> db.author.drop()
>>> db.paper.drop()
|
