select something as other from this

[sebastian]

Hi,

is it possible to run this select using DAL ?

select date,sum(hits) as morehits from report group by date


(see the "as")

thanks

[mdipierro]

yes and no. You can do:

morehits = db.mytable.myhits.sum()
rows=db().select(db.mytable.mydate,morehits,groupby=db.mytable.mydate)
for row in rows:
print row.mytable.mydate, row._extra[morehits]

but the morehits does not appear in the actual generated SQL because
there is no need to.

[Sebastian E. Ovide]

I'm looking for a way to use "AS" in a query so that I can rename columns...

so for example:

rows=db().select(db.mytable.date,db.mytable.clicks.sum(),db.mytable.impressions.sum(),groupby=db.mytable.date)

produces

SELECT mytable.date, SUM(mytable.clicks), SUM(mytable.impressions) FROM mytable GROUP BY mytable.date;

and so I can do

print rows[1].date

but I need to rename the columns...

for example rename SUM(mytable.impressions) as impressions so that I can do

print rows[1].impressions


is there any way to do it without using db.executesql ?

[mdipierro]

These are different problems. Even you could do "as", which you
technically an but it is complicated, would not rename the web2py
column.

You can rename the column without the "as".

rows=db(..).select(...)

rows.colnames is a list with the colnames. You can change any element
in this list as you please. Look what is in there first.

Massimo


On Jun 19, 3:27 am, "Sebastian E. Ovide" <sebastianov...@gmail.com>
wrote:

> I'm looking for a way to use "AS" in a query so that I can rename columns...
>
> so for example:
>
> rows=db().select(db.mytable.date,db.mytable.clicks.sum(),db.mytable.impressions.sum(),groupby=db.mytable.date)
>
> produces
>
> SELECT mytable.date, SUM(mytable.clicks), SUM(mytable.impressions) FROM
> mytable GROUP BY mytable.date;
>
> and so I can do
>
> print rows[1].date
>
> but I need to rename the columns...
>
> for example rename SUM(mytable.impressions) as impressions so that I can do
>
> print rows[1].impressions
>
> is there any way to do it without using db.executesql ?
>

[Sebastian E. Ovide]

how can I access to the renamed column ?

    rows=db().select(db.mytable.date)
    print len(rows)      # > 100
    rows.colnames=["a"]     
    print rows[1]         # EMPTY !!!!
    print rows[1].a      # ERROR !!!!!!
  
if I do not change the column name I cna print rows[1] and rows[1].date

any ideas ?

[mdipierro]

rows[1]._extra['a']


On Jun 19, 11:23 am, "Sebastian E. Ovide" <sebastianov...@gmail.com>
wrote:

> how can I access to the renamed column ?
>
>     rows=db().select(db.mytable.date)
>     print len(rows)      # > 100
>     rows.colnames=["a"]
>     print rows[1]         # EMPTY !!!!
>     print rows[1].a      # ERROR !!!!!!
>
> if I do not change the column name I cna print rows[1] and rows[1].date
>
> any ideas ?
>

[DenesL]

On Jun 19, 8:27 am, mdipierro <mdipie...@cs.depaul.edu> wrote:
> These are different problems. Even you could do "as", which you
> technically an but it is complicated, would not rename the web2py
> column.
>
> You can rename the column without the "as".
>
> rows=db(..).select(...)
>
> rows.colnames is a list with the colnames. You can change any element
> in this list as you please. Look what is in there first.

I believe changing colnames is a no-no.

From http://groups.google.com/group/web2py/msg/b42be1fa72b2c591

<CITE>
Changing row.colnames changes the way to retrieve columns when you
change colnames. Bad.

I take back my suggestion about changing colnames to change the column
names. DO NOT DO IT. There is a better and cleaner way:

Instead of simply printing {{=rows}}, print {{=SQLTABLE
(rows,headers=headers)}} instead where headers is a dictionary you
define that looks like this:

headers={'book.author':'Author'}

the headers names in the dictionary will be renamed accordingly.
</CITE>

[mdipierro]

Changing colnames that have the form "table.field" is a NO NO. That is
what I was referring to.
colnames that refer to aggregates can be changed. I agree it is not a
good idea anyway.

Massimo

On Jun 19, 3:19 pm, DenesL <denes1...@yahoo.ca> wrote:
> On Jun 19, 8:27 am, mdipierro <mdipie...@cs.depaul.edu> wrote:
>
> > These are different problems. Even you could do "as", which you
> > technically an but it is complicated, would not rename the web2py
> > column.
>
> > You can rename the column without the "as".
>
> > rows=db(..).select(...)
>
> > rows.colnames is a list with the colnames. You can change any element
> > in this list as you please. Look what is in there first.
>
> I believe changing colnames is a no-no.
>

> Fromhttp://groups.google.com/group/web2py/msg/b42be1fa72b2c591

[Sebastian E. Ovide]

is there any way to access to the sum column ? in this way we would not need to rename that column....

[mdipierro]

yes

in

rows=db().select(db.mytable.date,db.mytable.clicks.sum
(),db.mytable.impressions.sum(),groupby=db.mytable.date)

for row in rows:
print row._extra[db.mytable.clicks.sum()]

Massimo

On Jul 2, 6:22 am, "Sebastian E. Ovide" <sebastianov...@gmail.com>
wrote:

> is there any way to access to the sum column ? in this way we would not need
> to rename that column....
>