old web2py blog

A major limitation of the Google App Engine (GAE) it is the inability to perform queries that use the OR, BELONGS(IN), and LIKE operators.

The web2py Database Abstraction Layer provides a system for abstracting database query and it works on relational databases (RDBS) as well as on GAE but, still, it is crippled by the limitations mentioned above. Until now.

We have created additional API that allows merging records, filtering and sorting records at the web2py level, after they are extracted from the GAE storage. They can be used to mimic the missing features and will make your GAE code portable to RDBS.

Current supported RDBS

SQLite, MySQL, PostgreSQL, MSSQL, DB2, Informix, Oracle, FireBird, Ingres.

Current supported NoDB

Google App Engine

Here are some examples:

Assumptions

Connect to DB

db=DAL('sqlite://storage') # or DAL('gae')

Define tables

product = db.define_table('product',
                          Field('name'),
                          Field('price','double'))

buyer = db.define_table('buyer',
                        Field('name'))

purchase = db.define_table('purchase',
                           Field('product',db.product),
                           Field('buyer',db.buyer),
                           Field('quantity','integer'),
                           Field('order_date','date',default=request.now))

Insert some records

icecream = db.product.insert(name='Ice Cream',price=1.50)
kenny = db.buyer.insert(name='Kenny')
cartman = db.buyer.insert(name='Cartman')
db.purchase.insert(product=icecream,buyer=kenny,quantity=1,
                   order_date=datetime.datetime(2009,10,10))
db.purchase.insert(product=icecream,buyer=cartman,quantity=4,
                   order_date=datetime.datetime(2009,10,11))

(the inserts return the inserted records)

Update records

icecream.update_record(price=1.99)

or

icecream.price=1.99
icecream.update_record()

Two Table select

on RDBS only (JOINS)

rows = db(purchase.product==product.id)(purchase.buyer==buyer.id).select()
for row in rows:
    print row.product.name, row.product.price, 
          row.buyer.name, row.purchase.quantity

OUTPUT:

Ice Cream 1.99 Kenny 1 Ice Cream 1.99 Cartman 4

on RDBS and GAE (no JOINS)

rows = db(purchase.id>0).select()
for row in rows:
    print row.product.name, row.product.price, row.buyer.name, row.quantity

OUTPUT:

Ice Cream 1.99 Kenny 1 Ice Cream 1.99 Cartman 4

"OR"

on RDBS only

rows = db((purchase.buyer==kenny)|(purchase.buyer==cartman)).select()

on RDBS and GAE

rows = db(purchase.buyer==kenny).select()|db(purchase.buyer==cartman).select()

"OR" with "ORDERBY"

on RDBS only

rows = db((purchase.buyer==kenny)|(purchase.buyer==cartman))\
         .select(orderby=purchase.quantity)

on RDBS and GAE

rows = (db(purchase.buyer==kenny).select()|db(purchase.buyer==cartman)\
       .select()).sort(lambda row:row.quantity)

"IN"/"BELONGS"

on RDBS only

rows = db(purchase.buyer.belongs((kenny,cartman)))\
         .select(orderby=purchase.quantity)

on RDBS and GAE

rows = (db(purchase.buyer==kenny).select()|db(purchase.buyer==cartman).select())

"OR" with complex "ORDERBY"

on RDBS

rows = db((purchase.buyer==kenny)|(purchase.buyer==cartman))\
         (purchase.buyer==buyer.id).select(orderby=buyer.name)

on RDBS and GAE

rows = (db(purchase.buyer==kenny).select()|db(purchase.buyer==cartman)\
       .select()).sort(lambda row:row.buyer.name)
print rows

"LIKE"

on RDBS

rows = db(buyer.name.like('C%')).select()
print rows

on RDBS and GAE

rows = db(buyer.id>0).select().find(lambda row:row.name.startswith('C'))

Date/Datetime manipulations in expression

on RDBS only

rows = db(purchase.order_date.day()==11).select()

on RDBS and GAE

rows = db(purchase.id>0).select().find(lambda row:row.order_date.day==11)

Computed virtual fields

on RDBS and GAE

rows = db(db.purchase.id>0).select()

class ComputatedFields:
    def revenues(self):
        return self.purchase.product.price*self.purchase.quantity

rows = rows.setvirtualfields(purchase=ComputedFields())

for row in rows:
    print row.product.name, row.product.price, 
          row.buyer.name, row.quantity,
          row.revenues