The view only sees what you explicitly pass to it from the controller via the returned dictionary, so you would need to do:
def mileage(): [snip] return dict(rows=rows, mysum=mysum, sql=db._lastsql) Or you could just do: return locals() which returns a dictionary of all the local variables defined in mileage(). Anthony On Monday, February 27, 2012 3:57:25 PM UTC-5, Paul wrote: > > One last part of the puzzle, this all works ok at the command line > with print row[mysum] but I cannot get the syntax for using this in a > view > > for example:- > > controller DAL query:- > def mileage(): > mysum = db.t_appointment.miles_to.sum() > +db.t_appointment.miles_from.sum() > groupyyyymm = db.t_appointment.f_start_time.year()| > db.t_appointment.f_start_time.month() > rows = db().select( > mysum, > db.t_appointment.f_start_time.year(), > db.t_appointment.f_start_time.month(), > groupby=groupyyyymm) > > return dict(rows=rows, sql = db._lastsql) > > IN VIEW: > {{for row in rows:}} > {{=row[mysum]}} > {{pass}} > > I get NameError: name 'mysum' is not defined > > If I try: > > {{for row in rows:}} > {{=row['mysum']}} > {{pass}} > > I get: > > Traceback (most recent call last): > File "D:\web2py-src\web2py\gluon\restricted.py", line 194, in > restricted > exec ccode in environment > File "D:\web2py-src\web2py\applications\caltest\views\info/ > mileage.html", line 105, in <module> > File "D:\web2py-src\web2py\gluon\dal.py", line 4331, in __getitem__ > return dict.__getitem__(self, key) > KeyError: 'mysum' > > On Feb 27, 2:08 pm, Anthony <abasta...@gmail.com> wrote: > > > That syntax works and I can use that to refer to the data, I could see > > > that a row object had an '_extra' dict for the selected expressions > > > but could not see that the data could be referred to be the name of > > > the expression 'mysum' (its in there somewhere but not sure where!!) > > > > The book example does show that usage: > > > > >>> sum = db.log.severity.sum() > > >>> print db().select(sum).first()[sum] > > > > Actually, when you do row[mysum], the __getitem__ method of the Row > object > > automatically does row[str(mysum)]. Since mysum is a DAL Expression > object, > > its __str__ method converts it to the SQL syntax for the particular > > database adapter you are using, which in this case is > "(SUM(t_appointment.miles_to) > > + SUM(t_appointment.miles_from))". The Row __getitem__ method first > looks > > for that key in its _extra dict and returns the value if found. So, when > > you do: > > > > row[mysum] > > > > This is essentially what is happening behind the scenes: > > > > 1. row[str(mysum)] > > 2. row['(SUM(t_appointment.miles_to) + > SUM(t_appointment.miles_from))'] > > 3. row['_extra']['(SUM(t_appointment.miles_to) + > > SUM(t_appointment.miles_from))'] > > > > Anthony