Hi, I'm ThadeusB.

One of the nice things about MS Access is that you can define a query, and it will be accessible to your code anywhere else in the database. You can even base another query off of a query. Making queries inheritable properties. Another nice feature available to queries in MS Access is parameterization, the ability to pass variables to your queries before they execute.

The DAL in web2py allows you to save a query as an object for later use. The DAL will only execute SQL to the database on special commands such as .select(), .count(), .update(), .delete(), etc...

With a Query object, you can drill basic queries down as fine grained as you want. I find that the query naming convention typically used for MS Access fits well with web2py.

qry_published = (db.post.status == "publish")
qry_blog = (db.post.type == "blog")
qry_page = (db.post.type == "page")

# get all published
all_pub = db(qry_published).select()
# get all blog posts
posts = db(qry_blog)(qry_published).select()
# get unpublished pages
unpub_pages = db(~qry_published)(qry_page).select()

As you can see, the chaining ability can be very powerful, concise, and allows you to focus on your business logic.

We can accomplish parameterized queries by using functions!

qry_between = lambda start, end: ((db.post.pub_date >= start) & (db.post.pub_date <= end))

# get all published posts in the last month
import datetime
archive = db(qry_between(request.now - datetime.timedelta(days=30), request.now)(qry_published).select()

In an previous post I discussed dynamic queries. We can easily apply the concepts of both dynamic queries with parameterized queries.

# given args:
# arg1 = "published"
# arg2 = "between 2009-12-25 2010-01-15"

qset=db()
if arg1 == "published": qset=qset(qry_published)
if arg2.startswith("between"): qset=qset(qry_between(arg2.split()[1], arg2.split()[2]))
qset.select()

With these tips you will be able to increase your productivity with web2py by keeping your code simple, concise, and DRY (don't repeat yourself)