old web2py blog

def custsql():
  #
  #  how to execute a custom sql statement
  #  
  #  Given a database model called mod2, containing a table called tbusers
  #  and the fields userid, firstn, lastn, here's how to select a record
  #  where the lastn == 'Chan'
  #
  #  By Sterling Hankins 05/24/2008
  #
  #  the value returned is 
  #  [(u'JCHAN', u'Jackie', u'Chan')]
  # 
  r=mod2.executesql('select userid, firstn, lastn from tbusers where lastn="Chan";')
  return r

def custsql2():
  #  
  #   Another SQL example
  #
  #   ======== Start of model ==============
  # mod2=SQLDB("sqlite://mod2.db")
  # mod2.define_table('tbusers',
  #              SQLField('userid', 'string' , length=10, required=True),
  #              SQLField('firstn', 'string' , length=10, required=True),
  #              SQLField('lastn' , 'string' , length=10, required=True))
  #
  # mod2.tbusers.userid.requires=[IS_NOT_EMPTY(),IS_NOT_IN_DB(mod2, 'tbusers.userid')]
  # mod2.tbusers.firstn.requires=IS_NOT_EMPTY()
  # mod2.tbusers.lastn.requires=IS_NOT_EMPTY()
  # =============   end of model =============
  #
  # the value returned is....
  #    [(1, u'MMYER', u'Mike', u'Myers'), (2, u'JCHAN', u'Jackie', u'Chan')]
  #
  r=mod2.executesql('select * from tbusers;')
  return r