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.
Args:
expression: gluon.dal.Query or gluon.dal.Expression
Returns:
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__,
first=query_as_dict(expression.first),
second=query_as_dict(expression.second))
elif not expression.first is None:
if not expression.op is None:
return dict(op=expression.op.im_func.__name__,
first=query_as_dict(expression.first),
second=None) # eg '(person.title IS NULL)'
else:
return expression.first
elif not isinstance(expression.op, str):
return expression.op()
else:
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
else:
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.
Args:
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.
Returns:
gluon.dal.Query or gluon.dal.Expression object
"""
from gluon.dal import Expression, Query
if out == 'Expression':
out_class = Expression
else:
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'])
else:
raise ValueError
else:
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)
Limitations
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.
Comments (0)