If you benefit from web2py hope you feel encouraged to pay it forward by contributing back to society in whatever form you choose!

OR, LIKE, BELONGS, and more on Google App Engine (new in 1.69.1)

A major limitation of the Google App Engine (GAE) 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 queries 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 allow merging, filtering and sorting records after they are extracted from the GAE storage, at the web2py level. They can be used to mimic the missing features and will make your GAE code portable to RDBS too.

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)

"BELONGS" (same as SQL "IN")

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

Related slices

Comments (5)


Hosting graciously provided by:
Python Anywhere