[web2py] Re: How do you define a column name on summed field (i.e. how to do the equivalent of an sql 'select ..... as colname'

[Paul]

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