Some of the information here may be outdated, please check the book instead

Edit page

Title:

Security Code:

Body: (use this wiki markup)

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)

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

AlterEgo

old web2py blog

Useful Links

Edit page