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 <[email protected]> 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
