Create a table with all possible field types
'sqlite://test.db'
'mysql://root:none@localhost/test'
'postgres://mdipierro:none@localhost/test'
>>> 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', SQLField('stringf','string',length=32,required=True), SQLField('booleanf','boolean',default=False), SQLField('passwordf','password',notnull=True), SQLField('blobf','blob'), SQLField('uploadf','upload'), SQLField('integerf','integer',unique=True), SQLField('doublef','double',unique=True,notnull=True), SQLField('datef','date',default=datetime.date.today()), SQLField('timef','time'), SQLField('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', 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<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=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\r\nMassimo,QCD\r\n'
Delete all leftover tables
>>> db.authorship.drop()
>>> db.author.drop()
>>> db.paper.drop()
|