Hi, I'm ThadeusB.

I code stuff. I raise bees. I game.

web2py virtualfields as an ORM (an sqlalchemy approach)

One of the finer new features in web2py is the ability to declare what is called a "virtual field".

A virtual field is a function that gets called/calculated when the web2py DAL performs a select on the database tables.

The neat thing about virtual fields, is it allows us to give the DAL ORM functionalities like you would find in django and sqlalchemy.

    Field('item_total', 'double'),
    Field('tax_percentage', 'double'),
    Field('trans_date', 'datetime'),

class Sales():
    def total(self):
        return self.sales.item_total * self.sales.tax_percentage
    def tax(self):
        return self.total() - self.sales.item_total


2009_sales = db(
    (db.sales.trans_date <= "2009-12-31")
    (db.sales.trans_date >= "2009-01-01")

for sale in 2009_sales:
    print "Item: ", sale.item
    print "Price: ", sale.item_total
    print "Tax: ", sale.tax # Notice, we are getting from our virtualfield class we defined!
    print "Total: ", sale.total

Sometimes you might want a lazy virtualfield that only gets calculated when you call it. A blog post permalink would be a situation where this would be beneficial.


class Post():
    def permalink(self):
        def lzy():
            # Define an inner function that will perform our action
            return URL(r=request, c='default', f='view', args=self.post.title.replace(' ', '-'))
        # We are only returning a reference to the function, this will get called later!
        return lzy


for row in db(db.post.id > 0):
    print row.title
    print row.permalink() # Notice, we have to call the fucntion!
    print content