Good contact Table

##Contact Table Def
Here’s a good contact table def i want to remember.
– Automatic change for Info Last Updated datetime field.
– Uses represent

db.define_table("contact",
    Field("f_name", "string",  requires=IS_NOT_EMPTY() ),
    Field("l_name", "string", requires=IS_NOT_EMPTY() ),
    Field("m_initial", "string", default=None),
    Field('prime_phone', requires=IS_NOT_EMPTY(), comment='phone eg: (650) 555-1212'),
    Field('other_phone',   comment='phone eg: (650) 555-1212'),
    Field('email', requires=IS_EMPTY_OR(IS_EMAIL()) ),    
    #Field('ca_address', 'reference ca_address', writable=False, readable=False),
    Field("address_line1", "string", requires=IS_NOT_EMPTY() ),
    Field("address_line2", "string"),
    Field("city", "string", requires=IS_NOT_EMPTY() ),
    Field("state", "string", length=2, default='CA', writable=False),
    Field("zip", "string", length=5, default=None),
    Field("date_modified", "datetime", default=datetime.datetime.now(),
        update=request.now,   # working - needed below line
        writable=False, readable=True,
    # http://strftime.org/   AND http://stackoverflow.com/questions/9678172/modify-column-output-for-sqlform-grid-in-web2py
        represent=lambda x, row: x.strftime("%c"),
        comment='Last change date of contact info.'),

      ) 

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

Web2py DAL Quick Reference

Background
The following was referred to here in a post by Iceberg on May 4, 2009 at 9:14 AM on the web2py-users google group. It is a merged version of the old and new DAL info.

I couldn’t find it published anywhere so I am publishing it here so I can refer to it. Some of it is here.

web2pyTM Database Abstraction Layer (DAL)

Quick Reference

define_table, insert, count, delete, update

db = SQLDB('postgres://user:password@hostname/db', pools=10)
db.define_table('person',db.<a href="http://127.0.0.1:8000/examples/global/vars/Field">Field</a>('name','string'))
id= db.person.insert(name='max')
query=(db.person.id==id)
db(query).count()
db(query).delete()
db(query).update(name='Max')
rows = db(query).select(orderby=db.person.name)
for row in rows: print row.name

Examples of uri strings for SQLDB

sqlite://test.db
mysql://user:password@localhost/database
postgres://user:password@localhost/database
mssql://user:password@host/database
firebird://user:password@server:3050/database
oracle://user:password@database

Valid field Types

db.Field(name, 'string')
db.Field(name, 'text')
db.Field(name, 'password')
db.Field(name, 'blob')
db.Field(name, 'upload')
db.Field(name, 'boolean')
db.Field(name, 'integer')
db.Field(name, 'double')
db.Field(name, 'time')
db.Field(name, 'date')
db.Field(name, 'datetime')
db.Field(name, db.referenced_table) # reference field

Valid Field Attributes

length (only for string type, defaults to 32)
default (defaults to None)
required (defaults to False)
notnull (defaults to False)
requires (<a href="http://127.0.0.1:8000/examples/default/api">validator or list of validators</a>, for forms)
comment (for forms)
widget (for forms)
represent (for forms)
readable (for forms)
writable (for forms)
update (default value if the record is updated)
uploadfield (for upload fields)
authorize (for upload fields, function to be used if data can be downloaded, see authentication)
autodelete (for upload fields, if set to true linked uploaded images are removed upon deletion of the record)
label (for forms)

Migrations
Changing the list of fields or field types in a model, triggers an automatic migration, i.e. web2py generates SQL to alter the table accordingly. If the table does not exist it is created. Migration actions are logged in the file sql.log accessibled via the admin/design interface. Migration can be turned off on a per-table basis by passing migrate=False to define_table.
Select Attributes

rows = db(query).select(*fields, orderby=..., left=..., groupby=..., having=..., limitby=..., cache=...)

DAL Shortcuts

db['person']                   ### db.person
db.person['name']              ### db.person.name
db['person']['name']           ### db.person.name
db.person[0]=dict(name='Max')  ### insert
db.person[id]=dict(name='Max') ### update by db.person.id
print db.person[id]            ### select by db.person.id
del db.person[id]              ### delete by db.person.id</pre>

Truncate and Drop a Table

db.person.truncate()
db.person.drop()

**Reference Fields and Inner Joins*

db.define_table('dog',db.<a href="http://127.0.0.1:8000/examples/global/vars/Field">Field</a>('name'))
db.define_table('friendship', db.<a href="http://127.0.0.1:8000/examples/global/vars/Field">Field</a>('person',db.person), db.<a href="http://127.0.0.1:8000/examples/global/vars/Field">Field</a>('dog',db.dog))
db.friendship.insert(person=id, dog=db.dog.insert(name='Snoopy'))
friends=(db.person.id==db.friendship.person)&amp;(db.dog.id==db.friendship.dog)
rows = db(friends).select(db.person.name, db.dog.name)
for row in rows: print row.person.name, 'is friend of', row.dog.name

Left Outer Joins

query=(db.person.id>0)
friends=(db.person.id==db.friendship.person)&(db.dog.id==db.friendship.dog)
rows = db(query).select(db.person.name, db.dog.name, left=db.dog.on(friends))
for row in rows: print row.person.name, 'is friend of', row.dog.name or 'nobody'

Complex Queries

query = (db.person.id==1)|((db.person.id==2)&(db.person.name=='Max'))
query = (db.person.id==db.friendship.person)&(db.dog.id==db.friendship.dog)
query = db.person.name.lower().like('m%')
query = db.person.id.belongs(('max','Max','MAX'))
query = db.person.birth.year()+1==2008
rows = db(query).select()

Nested Selects

query = db.person.id.belongs(db()._select(db.friendship.person)

Aggregates

rows=db(friends).select(db.person.name,db.dog.id.count(),groupby=db.dog.id) 
''' Notes:
Aggregate functions: 
    db.table.field.count()
    db.table.field.max() 
    db.table.field.min()
    db.table.field. sum()
'''

Aliases

person=db.person
friendship=db.friendship
puppy=db.dog.with_alias('puppy')
query=(puppy.id==friendhip.dog)&(friendship.person==person.id)
rows=db().select(person.name,puppy.name,left=puppy.on(query))

Caching

rows=db().select(db.person.ALL,cache=(cache.ram,3600))
''' Notes:
cache=(model,cache_timeout) where model can be:
    cache.ram, 
    cache.disk, 
    cache.memcache or 
    user defined caching model
cache_timeout is in seconds.
'''

CSV Input/Output

# CVS Input
db.person.import_from_csv_file(open(filename,’rb’))
# CVS Output
str(rows)

HTML Output

print rows.xml()

Set Field Validators

db.person.name.requires=IS_NOT_IN_DB(db,db.person.name)
db.friendship.person.requires=IS_IN_DB(db,db.person.id,'%(name)s')

Generate and Process a Form from a Model

form = SQLFORM(db.friendship)
if form.accepts(request.vars, session): response.flash='record inserted'
elif form.errors: response.flash='form errors'
''' Note:
The form can then be displayed in a view with:
{{=form}}
'''

SQL + HTML Examples

# Given:
rows=db().select(db.users.ALL)
# Then:
SQLFORM (rows) # turns the rows into a CSS friendly table
SQLFORM(db.users)  # makes an input form for users
SQLFORM(db.users,rows[i]) # makes an edit form for the user in rows[i]
SQLFORM(db.users,rows[i],deletable=True) # makes an edit/delete form for the user in row[i]
SQLFORM(....,fields=['name','email']) # allows defining which fields should be displayed in the form. Only those fields will be validated.
SQLFORM(....,labels=['name':'Your Name']) # allows changing the labels of the listed fields.

''' Notes:
SQLFORM
-------
A form=SQLFORM(…) object has one method of practical interest:
    form.accepts(request.vars,session) 
The above method: 
    1. Processes the input variables (in request.vars) in the session
    2. Returns true if the form is valid, false otherwise. 
- Processed variables are in     form.vars
- Errors are in                  form.errors. 
- The form is modified accordingly. 
- If the form is accepted: 
    accept also performs the appropriate insert/update/delete in the database.

Extended Usage
--------------
The DAL API is automatically exposed in web2py 
    models, 
    controllers and 
    views 
but you can access them anywhere with:
'''
from gluon.sql import * 

Doctest Examples
These are old syntax but good examples. They use:

  • SQLDB instead of DAL and
  • SQLField instead of Field

However I believe they are still useful.

>>> db=SQLDB("sqlite://test.db")
>>> #OR db=SQLDB("mysql://username:password@host:port/dbname")
>>> #OR db=SQLDB("postgres://username:password@host:port/dbname")

# syntax: SQLField('fieldname','fieldtype',length=32,
#                   required=False, default=None,
#                   requires=[IS_EMAIL(error_message='invalid email')])

>>> tmp=db.define_table('users',
      # But notice that SQLField(...) is depreciated and you'd better use db.Field(...) instead
      SQLField('stringfield','string',length=32,required=True),
      SQLField('booleanfield','boolean',default=False),
      SQLField('passwordfield','password'),
      SQLField('textfield','text'),
      SQLField('blobfield','blob'),
      SQLField('uploadfield','upload'),
      SQLField('integerfield','integer'),
      SQLField('doublefield','double'),
      SQLField('datefield','date',default=datetime.date.today()),
      SQLField('timefield','time'),
      SQLField('datetimefield','datetime'),
      migrate='test_user.table')

# Insert a field

>>> db.users.insert(stringfield='a',booleanfield=True,
                   passwordfield='p',textfield='x',blobfield='x',
                   uploadfield=None, integerfield=5,doublefield=3.14,
                   datefield=datetime.date(2001,1,1),
                   timefield=datetime.time(12,30,15),
                   datetimefield=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>> len(db((db.person.name=='Max')|(db.person.birth>> 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=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
Massimo,QCD

# Delete all leftover tables

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

# Commit or rollback your work

>>> db.commit() # or db.rollback()

''' Notes:
migrate can be:
    False (do not create/alter tables), 
    True (create/alter tables) or 
    a filename (create/alter tables and store migration information in the file).

There are little idiosyncrasies in every  backend like the fact that:
    "user" is not a valid field name in PostgreSQL, or 
    that sqlite3 will ignore the type of a field and allow you to put anything in it despite the declared type.
Each database has its own keywords that may conflict with your tablenames.