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.
SQLite, MySQL, PostgreSQL, MSSQL, DB2, Informix, Oracle, FireBird, Ingres.
Google App Engine
Here are some examples:
db=DAL('sqlite://storage') # or DAL('gae')
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))
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)
icecream.update_record(price=1.99)
or
icecream.price=1.99
icecream.update_record()
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
Ice Cream 1.99 Kenny 1 Ice Cream 1.99 Cartman 4
rows = db(purchase.id>0).select()
for row in rows:
print row.product.name, row.product.price, row.buyer.name, row.quantity
Ice Cream 1.99 Kenny 1 Ice Cream 1.99 Cartman 4
rows = db((purchase.buyer==kenny)|(purchase.buyer==cartman)).select()
rows = db(purchase.buyer==kenny).select()|db(purchase.buyer==cartman).select()
rows = db((purchase.buyer==kenny)|(purchase.buyer==cartman))\
.select(orderby=purchase.quantity)
rows = (db(purchase.buyer==kenny).select()|db(purchase.buyer==cartman)\
.select()).sort(lambda row:row.quantity)
rows = db(purchase.buyer.belongs((kenny,cartman)))\
.select(orderby=purchase.quantity)
rows = (db(purchase.buyer==kenny).select()|db(purchase.buyer==cartman).select())
rows = db((purchase.buyer==kenny)|(purchase.buyer==cartman))\
(purchase.buyer==buyer.id).select(orderby=buyer.name)
rows = (db(purchase.buyer==kenny).select()|db(purchase.buyer==cartman)\
.select()).sort(lambda row:row.buyer.name)
print rows
rows = db(buyer.name.like('C%')).select()
print rows
rows = db(buyer.id>0).select().find(lambda row:row.name.startswith('C'))
rows = db(purchase.order_date.day()==11).select()
rows = db(purchase.id>0).select().find(lambda row:row.order_date.day==11)
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