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
Comments (5)
0
andrej 14 years ago
0
mrfreeze 14 years ago
0
mrfreeze 15 years ago
0
mrfreeze 15 years ago
0
renatocaliari 15 years ago