Thank you that works !
I am posting this list of samples that I created to help me get
familiar with the DAL. Hope this helps someone out.
If anyone has any suggested improvements on the syntax please feel
free to repost this with the improvements.
#=================================================================================================================================
"""
The intent of this code is to provide examples on how to use the
web2py DAL.
Common SQL queries on 2 tables, appname and appinfo, are used as
examples and translated into DAL syntax.
The output from the code is raw SQL as generated by the DAL and the
queries are not actually executed.
In order to execute the queries remove the underscore e.g. select
instead of _select, delete instead of _delete.
In order to browse the list of examples easily use the outline view in
PyDev
This code needs to reside in the web2py root folder e.g. C:\Web2py
Ver 1.1
"""
from gluon.dal import *
from gluon.dal import Expression
migrate_flag = True
line_prefix = '\n---------------------------'
line_suffix = '---------------------------'
db = DAL('sqlite://storage.sqlite')
db.define_table('appname', Field('appid'), Field('appname'),
Field('type'),Field('description'), migrate = migrate_flag)
db.define_table('appinfo', Field('appid'),
Field('version'),Field('category'), Field('description'), migrate =
migrate_flag)
# uncomment to insert data into tables
#db.appname.insert(appname='test', type='testtype')
#db.appinfo.insert(appid='1', version='1.00', category='test')
#db.commit()
# select * from appname
simple_select = db().select(db.appname.ALL)
print line_prefix + '[Simple Select Example]' + line_suffix
print simple_select
# select top 10 * from appname
select_top = db()._select(db.appname.ALL, limitby=(0, 10))
print line_prefix + '[Using Top with Select]' + line_suffix
print select_top
# select * from appname order by appname,appid
order_by = db()._select(db.appname.ALL, orderby=db.appname.appname|
db.appname.appid)
print line_prefix + '[Using Order By]' + line_suffix
print order_by
# select distinct appid from appname
select_distinct = db()._select(db.appname.appid, distinct=True)
print line_prefix + '[Using Distinct with Select]' + line_suffix
print select_distinct
# select * from appname where appid>2
simple_where_clause = db(db.appname.appid>2)._select(db.appname.ALL)
# OR rows = db(db.appname.appid>2)._select()
print line_prefix + '[Simple Where Clause]' + line_suffix
print simple_where_clause
# select * from appname where appid>2 and appname like '%PVS%'
multiple_where_clauses = db((db.appname.appid>2) &
(db.appname.appname.like('%PVS%')))._select(db.appname.ALL)
print line_prefix + '[Multiple Where Clauses]' + line_suffix
print multiple_where_clauses
# select * from appname where appname like '%PVS%'
like_clause = db(db.appname.appname.like('%PVS%'))._select()
print line_prefix + '[Using Like Clause]' + line_suffix
print like_clause
# select * from appname an, apppinfo ai where an.appid=ai.appid
inner_join = db(db.appname.appid==db.appinfo.appid)._select()
print line_prefix + '[Using Inner Joins]' + line_suffix
print inner_join
# select * from appname an left outer join appinfo ai on
an.appid=ai.appid
# It is necessary to be explicit about which fields to select in this
case
left_join = db()._select(db.appinfo.ALL, db.appname.ALL,
left=db.appinfo.on(db.appname.appid==db.appinfo.appid))
print line_prefix + '[Using Left Joins]' + line_suffix
print left_join
# select * from appname where appid in (1,2)
# belongs takes a list of tuples as arguments
in_clause = db(db.appname.appid.belongs((1,2)))._select()
print line_prefix + '[Using In Clause]' + line_suffix
print in_clause
# select * from appname where appid in (select distinct appid from
appinfo)
# sub query must be _select so SQL statement is generated and not
actually executed
sub_qry = db()._select(db.appinfo.appid, distinct=True)
qry_of_qries = db(db.appname.appid.belongs(sub_qry))._select()
print line_prefix + '[Using Query of Queries]' + line_suffix
print qry_of_qries
# select count(*) from appname where appid>1
using_count = db(db.appname.appid>1)._count()
print line_prefix + '[Using Count]' + line_suffix
print using_count
# insert into appname (appname) values('test')
using_insert = db.appname._insert(appname='test')
db.commit()
print line_prefix + '[Insert Example]' + line_suffix
print using_insert
# delete from appname where appid=0
# IMPORTANT: After executing any delete, insert or update you must
always call commit other the table will be locked and future requests
will be suspended on it!
using_delete = db(db.appname.appid==0)._delete()
db.commit()
print line_prefix + '[Delete Example]' + line_suffix
print using_delete
# update appname set appname='test' where appid=1
using_update = db(db.appname.appid==2)._update(appname='test2')
db.commit()
print line_prefix + '[Update Example]' + line_suffix
print using_update
# select * from appname an, apppinfo ai where an.appid=ai.appid
AP = db.appname.with_alias('ap')
AI = db.appinfo.with_alias('ai')
using_aliases_inner_join =
db(db.ap.appid==db.ai.appid)._select(db.ap.appname)
print line_prefix + '[Using aliases with inner join]' + line_suffix
print using_aliases_inner_join
# select * from appname an left outer join appinfo ai on
an.appid=ai.appid
AP = db.appname.with_alias('ap')
AI = db.appinfo.with_alias('ai')
using_aliases_left_join = db()._select(db.ai.ALL,
left=db.ai.on(db.ai.appid==db.ap.appid))
# OR using_aliases_left_join = db()._select(AI.ALL,
left=AI.on(AI.appid==db.ap.appid))
print line_prefix + '[Using aliases with left join]' + line_suffix
print using_aliases_left_join
# select *, case when 1=1 then 1 else 0 end=1 testcol- from appname
where appid>2 and case when 1=1 then 1 else 0 end=1 order by testcol
using_case = db((db.appname.appid>2)&("case when 1=1 then 1 else 0
end=1"))._select(db.appname.ALL,"CASE WHEN appname.appid=1 THEN 0 ELSE
1 END testcol", orderby='testcol');
print line_prefix + '[Using Case]' + line_suffix
print using_case
#=================================================================================================================================
