Updated web2py DAL Basic Examples

DAL Updated Examples

Content

Table: Define [a Table], Insert, Select, Update, Count, Delete, Drop [a Table]

Also See in the web2py Book:

# make this function now for later result printing
def p(rows):
    for r in rows:
        print r.name, r.addr, r.city

# Content: Define, Insert, Select, Update, Count, Delete, Drop

# Define [a Table]
db.define_table('person', Field('name'), Field('addr'), Field('city') )   # person has 4 fields person.id + person.name .addr .city

# Insert
db.person.insert( **{'name':'joe', 'addr':'5 Blue St', 'city':'waco'} )
db.person.insert( **{'name':'jim', 'addr':'6 Red St', 'city':'waco'} )
db.person.insert( **{'name':'john', 'addr':'7 Green St', 'city':'waco'} )
db.person.insert( **{'name':'jack', 'addr':'7 Green St', 'city':'houston'} )
db.person.insert( **{'name':'jackie', 'addr':'7 Green St', 'city':'houston'} )
db.person.insert( **{'name':'jill', 'addr':'7 Green St', 'city':'austin'} )
db.person.insert( **{'name':'jane', 'addr':'9 Brown St', 'city':'austin'} )


# Select        
# select all rows in person
rows = db(db.person.id > 0).select()
In [55]: p(rows)
joe 5 Blue St waco
jim 6 Red St waco
john 7 Green St waco
jack 7 Green St houston
jackie 7 Green St houston
jill 7 Green St austin
jane 9 Brown St austin

# select rows in city is waco
rows = db(db.person.city == 'waco').select()
In [61]: p(rows)
joe 5 Blue St waco
jim 6 Red St waco
john 7 Green St waco

# select rows in city is 'waco' AND addr is '7 Green St'
rows = db( (db.person.city == 'waco') & (db.person.addr == '7 Green St') ).select()
In [73]: p(rows)
john 7 Green St waco

# select rows in city is 'houston' OR name is 'joe'
rows = db( (db.person.city == 'houston') | (db.person.name == 'joe') ).select()
In [83]: p(rows)
joe 5 Blue St waco
jack 7 Green St houston
jackie 7 Green St houston

# Update  ->   get an id;   do the update
# update 1    update spelling jackie's name to 'jaquay'
id = db( (db.person.name=='jackie') & (db.person.addr=='7 Green St') & (db.person.city=='houston') ).select().first().id 
rtn = db.person[id] = dict(name='jaquay', addr='7 Green St', city='houston')

# update 1 result
In [87]: rtn
Out[87]: {'addr': '7 Green St', 'city': 'houston', 'name': 'jaquay'}
In [88]: rows = db(db.person.id > 0).select()
In [89]: p(rows)
joe 5 Blue St waco
jim 6 Red St waco
john 7 Green St waco
jack 7 Green St houston
jaquay 7 Green St houston
jill 7 Green St austin
jane 9 Brown St austin

# update 2    all '7 Green St's in 'houston' to '7 Yellow St' in 'houston'
rows = db( (db.person.addr=='7 Green St') & (db.person.city=='houston') ).select()
for row in rows:
    rtn = db.person[row.id] = dict(addr='7 Yellow St', city='houston')
    print rtn
# update 2 result
rows = db(db.person.id > 0).select()
In [94]: p(rows)
joe 5 Blue St waco
jim 6 Red St waco
john 7 Green St waco
jack 7 Yellow St houston
jaquay 7 Yellow St houston
jill 7 Green St austin
jane 9 Brown St austin


# Count

# count 1    items in city is 'houston' OR name is 'joe'
In [97]: print db( (db.person.city == 'houston') | (db.person.name == 'joe') ).count()
3
# notice relationship to .select()
In [98]: print db( (db.person.city == 'houston') | (db.person.name == 'joe') ).select()
person.id,person.name,person.addr,person.city
1,joe,5 Blue St,waco
4,jack,7 Yellow St,houston
5,jaquay,7 Yellow St,houston

# count 2    items where name startswith 'ja'
In [99]: print db( (db.person.name.startswith('ja'))  ).count()
3
In [100]: print db( (db.person.name.startswith('ja'))  ).select()
person.id,person.name,person.addr,person.city
4,jack,7 Yellow St,houston
5,jaquay,7 Yellow St,houston
7,jane,9 Brown St,austin

# Delete   delete all items where name startswith('jo')  notice similarity to select()

rtn = db( (db.person.name.startswith('jo'))  ).delete()
In [105]: print rtn
2
rows = db(db.person.id > 0).select()
In [107]: p(rows)
jim 6 Red St waco
jack 7 Yellow St houston
jaquay 7 Yellow St houston
jill 7 Green St austin
jane 9 Brown St austin


# Drop [a Table]   drop the table from the db
db.person.drop()


see_also = '''
[Other operators](http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Other-operators)
[Combining rows](http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Combining-rows)
[list:<type> and contains](http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#list--type--and-contains)
'''

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s