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

Instances of classes defined in modules are not pickable and cannot be stored in the session.  In particular web2py db objects cannot be pickled because they contain a database connection.  How can you get around this limitation?  There are two options, depending on the complexity of your query.


Option 1

Simple queries can be saved in the session by converting them to strings and then using gluon.dal.Query to bring them back.  For example:


==Controller 1==
query = (db.comment.id > 0) 
session.query = str(query)

==Controller 2==
from gluon.dal import Query
query = Query(db, session.query)

This only seems to work for simple queries as web2py does not fully pharse the return string, but simply wraps a new query around the string.  This is very cunning, but means it can miss some of the details, especially where there are implicit joins. 


Option 2

If you want to store a more complex query then it must first be seralized.  I have written two functions to do this.  The first function takes a gluon.dal.Query object (or a  gluon.dal.Expression) and converts it into a dictionary.  The routine is based on gluon.dal.BaseAdapter.expand (the one that web2py itself uses to produce the SQL string).

def query_as_dict(expression, field_type=None):
    """Seralizes gluon.dal.Query as dictionary.
    Converts a gluon.dal.Query or gluon.dal.Expression
    into a dictionary structure that can be pickled and
    stored in the session.
        expression: gluon.dal.Query or gluon.dal.Expression
        Dictionary in the form {op:op, first:expression, second:expression}
        op: the query operation (eg, AND, EQ, GT)
        expression: either a dictionary (that expands a gluon Table,
            Field, Expression or Query object) or a base object such as
            a string or list.
    For example:
        >>>query = (db.comment.id.belongs((1,2,3))) & (db.webpage.title == 'FAQ')
        >>>print query_as_dict(query)
        "{'second': {'second': 'FAQ', 'first': {'table': 'webpage', 'fieldname': 'title',
        'tablename': 'webpage'}, 'op': 'EQ'}, 'first': {'second': (1, 2, 3), 'first':
        {'table': 'comment', 'fieldname': 'id', 'tablename': 'comment'}, 'op': 'BELONGS'},
        'op': 'AND'}"
    from gluon.dal import Query, Expression, Table, Field
    if isinstance(expression, Field):
        tablename = expression._tablename
        return dict(tablename=expression._tablename,
                    table = str(expression._table),
                    fieldname = expression.name)
    elif isinstance(expression, (Expression, Query)):
        if not expression.second is None:
            return dict(op=expression.op.im_func.__name__, 
        elif not expression.first is None:
            if not expression.op is None:
                return dict(op=expression.op.im_func.__name__,
                            second=None) # eg '(person.title IS NULL)'
                return expression.first
        elif not isinstance(expression.op, str):
            return expression.op()
            return '(%s)' % expression.op
    elif field_type:
        return str(represent(expression,field_type))
    elif isinstance(expression,(list,tuple)):
        return expression
    elif isinstance(expression, Table):
        return dict(tablename=expression._tablename,
                    table = str(expression))
    elif expression==None:
        return None
        return str(expression)


The second function just does the reverse.

def query_from_dict(db, query, out='Query'):
    """Builds gluon.dal.Query from dictionary structure.
        db: gluon.dal.db object
        query: A dictionary in the form {op:op, first:expression, second:expression}
            as returned by query_as_dict()
        out: Set to 'Expression' for gluon.dal.Expression 
            rather than Query.
        gluon.dal.Query or gluon.dal.Expression object
    from gluon.dal import Expression, Query
    if out == 'Expression':
        out_class = Expression
        out_class = Query
    if type(query) == dict:
        if 'op' in query.keys():
            first = query_from_dict(db, query['first'], out=out)
            second = query_from_dict(db, query['second'], out=out)
            op = getattr(db._adapter, query['op'])
            return out_class(db, op, first=first, second=second)
        elif 'fieldname' in query.keys():
            if query['tablename'] == query['table']:
                return db[query['tablename']][query['fieldname']]
            else: # a table.field with alias
                return db[query['table']].with_alias(query['tablename'])[query['fieldname']]
        elif 'tablename' in query.keys():
            if query['tablename'] == query['table']:
                return db[query['tablename']]
            elif ' AS ' in query['table']: # a table with alias
                t = query['table'].split(' ')[0]
                return db[t].with_alias(query['tablename'])          
            raise ValueError
        return query


Place both functions in a module (for example  "/myapp/modules/helpers.py") and use it like this:

#==Controller 1==
from helpers import query_as_dict
session.query = query_as_dict(query)

#==Controller 2==
from helpers import query_from_dict
query = query_from_dict(db, session.query)


Your milage may vary.  I've tested it for queries that use belongs, inner joins, with_alias, left outer joins.  Not tested for aggregate functions (count, sum), like, upper, date, time, etc.  Tell me where it doesn't work an we can try to improve it.

Also note that the code includes one "hack".  I couldn't find a proper way to identify the original table name from an aliased table (the older sql.py used to provide "table._ot", but this is not in the new dal) so I've ripped the name from the SQL string.  Not sure how robust this is.

Hope you find the functions useful.

Related slices

Comments (0)

Hosting graciously provided by:
Python Anywhere