[web2py] Re: Query with postgres
Hi Massimo,
thanks for the suggestions, at this point I have to understand what to do:
- use your last proposal but in this case the date is no longer a data
object but three different columns, and this will lead to a more difficult
logic
- put a date field in the table definition, this may simplify all the
things, isn't it?
I will check it later this week-end.
Thanks
Paolo
On Wednesday, October 17, 2012 7:25:59 PM UTC+2, Massimo Di Pierro wrote:
It should work with python 2.7 but not previous version. You can also do:
s=db.club.created_on.year() | db.club.created_on.month() |
db.club.created_on.day()
dates =
db(query).select(db.club.created_on.year(),db.club.created_on.month(),db.club.created_on.day()
,
count,orderby=~s, limitby=limitby, groupby=s)
On Wednesday, 17 October 2012 11:54:29 UTC-5, Paolo wrote:
Hi Massimo, thanks for the suggested query but unfortunately I got this
error:
Traceback (most recent call last):
File /home/paolo/Dropbox/git/web2py/gluon/restricted.py, line 208, in
restricted
ccode = compile2(code,layer)
File /home/paolo/Dropbox/git/web2py/gluon/restricted.py, line 193, in
compile2
return compile(code.rstrip().replace('\r\n','\n')+'\n', layer, 'exec')
File /home/paolo/Dropbox/git/web2py/applications/bikend/models/clubDB.py
http://127.0.0.1:8000/admin/default/edit/bikend/models/clubDB.py, line 29
dates = db(query).select(*s,count,orderby=~join(s), limitby=limitby,
groupby=join(s))
SyntaxError: only named arguments may follow *expression
If that can help, I've tried without the wildcard but it failed, web2py
was blocked and python took 100% of the cpu.
Paolo
On Wednesday, October 17, 2012 2:35:23 PM UTC+2, Massimo Di Pierro wrote:
s=db.club.created_on.year(),db.club.created_on.month(),db.club.created_on.day()
def join(s): return reduce(lambda a,b:a|b,s)
dates = db(query).select(*s,count,orderby=~join(s), limitby=limitby,
groupby=join(s))
On Wednesday, 17 October 2012 01:31:55 UTC-5, Paolo wrote:
Hi Cliff,
I got the reasons of postgres, but I don't know how to fix it. The
query is actually very simple, I have several post, I want to group them
by
s, and get the number of post for each s. Where s is:
s=db.club.created_on.year() | db.club.created_on.month() |
db.club.created_on.day()
In the select I may created_on and use s instead, something like that:
dates = db(query).select(s,count,orderby=~s, limitby=limitby, groupby=s)
but doing that I got this error:
2012-10-17 08:27:59,210 - web2py - ERROR - Traceback (most recent call
last):
File /home/paolo/Dropbox/git/web2py/gluon/restricted.py, line 209,
in restricted
exec ccode in environment
File
/home/paolo/Dropbox/git/web2py/applications/bikend/controllers/club.py,
line 140, in module
File /home/paolo/Dropbox/git/web2py/gluon/globals.py, line 184, in
lambda
self._caller = lambda f: f()
File
/home/paolo/Dropbox/git/web2py/applications/bikend/controllers/club.py,
line 3, in index
d= dict(clubs = get_clubs())
File
/home/paolo/Dropbox/git/web2py/applications/bikend/models/clubDB.py,
line
25, in get_clubs
dates = db(query).select(s,count,orderby=~s, limitby=limitby,
groupby=s)
File /home/paolo/Dropbox/git/web2py/gluon/dal.py, line 8787, in
select
return adapter.select(self.query,fields,attributes)
File /home/paolo/Dropbox/git/web2py/gluon/dal.py, line 2127, in
select
return super(SQLiteAdapter, self).select(query, fields, attributes)
File /home/paolo/Dropbox/git/web2py/gluon/dal.py, line 1615, in
select
return self._select_aux(sql,fields,attributes)
File /home/paolo/Dropbox/git/web2py/gluon/dal.py, line 1596, in
_select_aux
return processor(rows,fields,self._colnames,cacheable=cacheable)
File /home/paolo/Dropbox/git/web2py/gluon/dal.py, line 1974, in
parse
fields[j].type,blob_decode)
IndexError: list index out of range
Paolo
On Wednesday, October 17, 2012 4:45:35 AM UTC+2, Cliff Kachinske wrote:
I don't know how it possibly worked in sqlite, but this is an
aggregate query combined with a non-aggregate query.
In other words, the count is a property of an aggregation of rows in
the database, whereas created_on is a property of individual rows.
This confuses Postgres. It doesn't know if you want the aggregate
result (count) or the result for individual rows (created_on). It cannot
deliver both from the same query.
What are you trying to find out in your query?
On Tuesday, October 16, 2012 4:59:36 PM UTC-4, Paolo wrote:
Dear all,
I've just switched from sqlite to postgres, and now I have problems
with few queries.
One query that works correctly on sqlite and fails on postgres is the
following:
s=db.club.created_on.year() | db.club.created_on.month() |
db.club.created_on.day()
count = db.club.id.count()
dates =
[web2py] Re: Query with postgres
Hi Cliff, I got the reasons of postgres, but I don't know how to fix it. The query is actually very simple, I have several post, I want to group them by s, and get the number of post for each s. Where s is: s=db.club.created_on.year() | db.club.created_on.month() | db.club.created_on.day() In the select I may created_on and use s instead, something like that: dates = db(query).select(s,count,orderby=~s, limitby=limitby, groupby=s) but doing that I got this error: 2012-10-17 08:27:59,210 - web2py - ERROR - Traceback (most recent call last): File /home/paolo/Dropbox/git/web2py/gluon/restricted.py, line 209, in restricted exec ccode in environment File /home/paolo/Dropbox/git/web2py/applications/bikend/controllers/club.py, line 140, in module File /home/paolo/Dropbox/git/web2py/gluon/globals.py, line 184, in lambda self._caller = lambda f: f() File /home/paolo/Dropbox/git/web2py/applications/bikend/controllers/club.py, line 3, in index d= dict(clubs = get_clubs()) File /home/paolo/Dropbox/git/web2py/applications/bikend/models/clubDB.py, line 25, in get_clubs dates = db(query).select(s,count,orderby=~s, limitby=limitby, groupby=s) File /home/paolo/Dropbox/git/web2py/gluon/dal.py, line 8787, in select return adapter.select(self.query,fields,attributes) File /home/paolo/Dropbox/git/web2py/gluon/dal.py, line 2127, in select return super(SQLiteAdapter, self).select(query, fields, attributes) File /home/paolo/Dropbox/git/web2py/gluon/dal.py, line 1615, in select return self._select_aux(sql,fields,attributes) File /home/paolo/Dropbox/git/web2py/gluon/dal.py, line 1596, in _select_aux return processor(rows,fields,self._colnames,cacheable=cacheable) File /home/paolo/Dropbox/git/web2py/gluon/dal.py, line 1974, in parse fields[j].type,blob_decode) IndexError: list index out of range Paolo On Wednesday, October 17, 2012 4:45:35 AM UTC+2, Cliff Kachinske wrote: I don't know how it possibly worked in sqlite, but this is an aggregate query combined with a non-aggregate query. In other words, the count is a property of an aggregation of rows in the database, whereas created_on is a property of individual rows. This confuses Postgres. It doesn't know if you want the aggregate result (count) or the result for individual rows (created_on). It cannot deliver both from the same query. What are you trying to find out in your query? On Tuesday, October 16, 2012 4:59:36 PM UTC-4, Paolo wrote: Dear all, I've just switched from sqlite to postgres, and now I have problems with few queries. One query that works correctly on sqlite and fails on postgres is the following: s=db.club.created_on.year() | db.club.created_on.month() | db.club.created_on.day() count = db.club.id.count() dates = db(query).select(db.club.created_on,count,orderby=~db.club.created_on, limitby=limitby, groupby=s) Now on postgres, it raises the following error: ProgrammingError: column club.created_on must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT club.created_on, COUNT(club.id) FROM club WHERE (((c... I read online that the fields in the select must be on the groupby as well. The problem is that by grouping even by club.created_on (by adding groupby=s | club.created_on) the result is totally different. What can I do to tackle this problem? Best, Paolo --
[web2py] Re: Query with postgres
s=db.club.created_on.year(),db.club.created_on.month(),db.club.created_on.day() def join(s): return reduce(lambda a,b:a|b,s) dates = db(query).select(*s,count,orderby=~join(s), limitby=limitby, groupby=join(s)) On Wednesday, 17 October 2012 01:31:55 UTC-5, Paolo wrote: Hi Cliff, I got the reasons of postgres, but I don't know how to fix it. The query is actually very simple, I have several post, I want to group them by s, and get the number of post for each s. Where s is: s=db.club.created_on.year() | db.club.created_on.month() | db.club.created_on.day() In the select I may created_on and use s instead, something like that: dates = db(query).select(s,count,orderby=~s, limitby=limitby, groupby=s) but doing that I got this error: 2012-10-17 08:27:59,210 - web2py - ERROR - Traceback (most recent call last): File /home/paolo/Dropbox/git/web2py/gluon/restricted.py, line 209, in restricted exec ccode in environment File /home/paolo/Dropbox/git/web2py/applications/bikend/controllers/club.py, line 140, in module File /home/paolo/Dropbox/git/web2py/gluon/globals.py, line 184, in lambda self._caller = lambda f: f() File /home/paolo/Dropbox/git/web2py/applications/bikend/controllers/club.py, line 3, in index d= dict(clubs = get_clubs()) File /home/paolo/Dropbox/git/web2py/applications/bikend/models/clubDB.py, line 25, in get_clubs dates = db(query).select(s,count,orderby=~s, limitby=limitby, groupby=s) File /home/paolo/Dropbox/git/web2py/gluon/dal.py, line 8787, in select return adapter.select(self.query,fields,attributes) File /home/paolo/Dropbox/git/web2py/gluon/dal.py, line 2127, in select return super(SQLiteAdapter, self).select(query, fields, attributes) File /home/paolo/Dropbox/git/web2py/gluon/dal.py, line 1615, in select return self._select_aux(sql,fields,attributes) File /home/paolo/Dropbox/git/web2py/gluon/dal.py, line 1596, in _select_aux return processor(rows,fields,self._colnames,cacheable=cacheable) File /home/paolo/Dropbox/git/web2py/gluon/dal.py, line 1974, in parse fields[j].type,blob_decode) IndexError: list index out of range Paolo On Wednesday, October 17, 2012 4:45:35 AM UTC+2, Cliff Kachinske wrote: I don't know how it possibly worked in sqlite, but this is an aggregate query combined with a non-aggregate query. In other words, the count is a property of an aggregation of rows in the database, whereas created_on is a property of individual rows. This confuses Postgres. It doesn't know if you want the aggregate result (count) or the result for individual rows (created_on). It cannot deliver both from the same query. What are you trying to find out in your query? On Tuesday, October 16, 2012 4:59:36 PM UTC-4, Paolo wrote: Dear all, I've just switched from sqlite to postgres, and now I have problems with few queries. One query that works correctly on sqlite and fails on postgres is the following: s=db.club.created_on.year() | db.club.created_on.month() | db.club.created_on.day() count = db.club.id.count() dates = db(query).select(db.club.created_on,count,orderby=~db.club.created_on, limitby=limitby, groupby=s) Now on postgres, it raises the following error: ProgrammingError: column club.created_on must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT club.created_on, COUNT(club.id) FROM club WHERE (((c... I read online that the fields in the select must be on the groupby as well. The problem is that by grouping even by club.created_on (by adding groupby=s | club.created_on) the result is totally different. What can I do to tackle this problem? Best, Paolo --
[web2py] Re: Query with postgres
Hi Massimo, thanks for the suggested query but unfortunately I got this
error:
Traceback (most recent call last):
File /home/paolo/Dropbox/git/web2py/gluon/restricted.py, line 208, in
restricted
ccode = compile2(code,layer)
File /home/paolo/Dropbox/git/web2py/gluon/restricted.py, line 193, in
compile2
return compile(code.rstrip().replace('\r\n','\n')+'\n', layer, 'exec')
File /home/paolo/Dropbox/git/web2py/applications/bikend/models/clubDB.py
http://127.0.0.1:8000/admin/default/edit/bikend/models/clubDB.py, line 29
dates = db(query).select(*s,count,orderby=~join(s), limitby=limitby,
groupby=join(s))
SyntaxError: only named arguments may follow *expression
If that can help, I've tried without the wildcard but it failed, web2py was
blocked and python took 100% of the cpu.
Paolo
On Wednesday, October 17, 2012 2:35:23 PM UTC+2, Massimo Di Pierro wrote:
s=db.club.created_on.year(),db.club.created_on.month(),db.club.created_on.day()
def join(s): return reduce(lambda a,b:a|b,s)
dates = db(query).select(*s,count,orderby=~join(s), limitby=limitby,
groupby=join(s))
On Wednesday, 17 October 2012 01:31:55 UTC-5, Paolo wrote:
Hi Cliff,
I got the reasons of postgres, but I don't know how to fix it. The query
is actually very simple, I have several post, I want to group them by s,
and get the number of post for each s. Where s is:
s=db.club.created_on.year() | db.club.created_on.month() |
db.club.created_on.day()
In the select I may created_on and use s instead, something like that:
dates = db(query).select(s,count,orderby=~s, limitby=limitby, groupby=s)
but doing that I got this error:
2012-10-17 08:27:59,210 - web2py - ERROR - Traceback (most recent call
last):
File /home/paolo/Dropbox/git/web2py/gluon/restricted.py, line 209, in
restricted
exec ccode in environment
File
/home/paolo/Dropbox/git/web2py/applications/bikend/controllers/club.py,
line 140, in module
File /home/paolo/Dropbox/git/web2py/gluon/globals.py, line 184, in
lambda
self._caller = lambda f: f()
File
/home/paolo/Dropbox/git/web2py/applications/bikend/controllers/club.py,
line 3, in index
d= dict(clubs = get_clubs())
File
/home/paolo/Dropbox/git/web2py/applications/bikend/models/clubDB.py, line
25, in get_clubs
dates = db(query).select(s,count,orderby=~s, limitby=limitby,
groupby=s)
File /home/paolo/Dropbox/git/web2py/gluon/dal.py, line 8787, in select
return adapter.select(self.query,fields,attributes)
File /home/paolo/Dropbox/git/web2py/gluon/dal.py, line 2127, in select
return super(SQLiteAdapter, self).select(query, fields, attributes)
File /home/paolo/Dropbox/git/web2py/gluon/dal.py, line 1615, in select
return self._select_aux(sql,fields,attributes)
File /home/paolo/Dropbox/git/web2py/gluon/dal.py, line 1596, in
_select_aux
return processor(rows,fields,self._colnames,cacheable=cacheable)
File /home/paolo/Dropbox/git/web2py/gluon/dal.py, line 1974, in parse
fields[j].type,blob_decode)
IndexError: list index out of range
Paolo
On Wednesday, October 17, 2012 4:45:35 AM UTC+2, Cliff Kachinske wrote:
I don't know how it possibly worked in sqlite, but this is an aggregate
query combined with a non-aggregate query.
In other words, the count is a property of an aggregation of rows in the
database, whereas created_on is a property of individual rows.
This confuses Postgres. It doesn't know if you want the aggregate
result (count) or the result for individual rows (created_on). It cannot
deliver both from the same query.
What are you trying to find out in your query?
On Tuesday, October 16, 2012 4:59:36 PM UTC-4, Paolo wrote:
Dear all,
I've just switched from sqlite to postgres, and now I have problems
with few queries.
One query that works correctly on sqlite and fails on postgres is the
following:
s=db.club.created_on.year() | db.club.created_on.month() |
db.club.created_on.day()
count = db.club.id.count()
dates =
db(query).select(db.club.created_on,count,orderby=~db.club.created_on,
limitby=limitby, groupby=s)
Now on postgres, it raises the following error:
ProgrammingError: column club.created_on must appear in the GROUP BY
clause or be used in an aggregate function
LINE 1: SELECT club.created_on, COUNT(club.id) FROM club WHERE (((c...
I read online that the fields in the select must be on the groupby as
well. The problem is that by grouping even by club.created_on (by adding
groupby=s | club.created_on) the result is totally different. What can I
do to tackle this problem?
Best,
Paolo
--
[web2py] Re: Query with postgres
It should work with python 2.7 but not previous version. You can also do:
s=db.club.created_on.year() | db.club.created_on.month() |
db.club.created_on.day()
dates =
db(query).select(db.club.created_on.year(),db.club.created_on.month(),db.club.created_on.day()
,
count,orderby=~s, limitby=limitby, groupby=s)
On Wednesday, 17 October 2012 11:54:29 UTC-5, Paolo wrote:
Hi Massimo, thanks for the suggested query but unfortunately I got this
error:
Traceback (most recent call last):
File /home/paolo/Dropbox/git/web2py/gluon/restricted.py, line 208, in
restricted
ccode = compile2(code,layer)
File /home/paolo/Dropbox/git/web2py/gluon/restricted.py, line 193, in
compile2
return compile(code.rstrip().replace('\r\n','\n')+'\n', layer, 'exec')
File /home/paolo/Dropbox/git/web2py/applications/bikend/models/clubDB.py
http://127.0.0.1:8000/admin/default/edit/bikend/models/clubDB.py, line 29
dates = db(query).select(*s,count,orderby=~join(s), limitby=limitby,
groupby=join(s))
SyntaxError: only named arguments may follow *expression
If that can help, I've tried without the wildcard but it failed, web2py
was blocked and python took 100% of the cpu.
Paolo
On Wednesday, October 17, 2012 2:35:23 PM UTC+2, Massimo Di Pierro wrote:
s=db.club.created_on.year(),db.club.created_on.month(),db.club.created_on.day()
def join(s): return reduce(lambda a,b:a|b,s)
dates = db(query).select(*s,count,orderby=~join(s), limitby=limitby,
groupby=join(s))
On Wednesday, 17 October 2012 01:31:55 UTC-5, Paolo wrote:
Hi Cliff,
I got the reasons of postgres, but I don't know how to fix it. The query
is actually very simple, I have several post, I want to group them by s,
and get the number of post for each s. Where s is:
s=db.club.created_on.year() | db.club.created_on.month() |
db.club.created_on.day()
In the select I may created_on and use s instead, something like that:
dates = db(query).select(s,count,orderby=~s, limitby=limitby, groupby=s)
but doing that I got this error:
2012-10-17 08:27:59,210 - web2py - ERROR - Traceback (most recent call
last):
File /home/paolo/Dropbox/git/web2py/gluon/restricted.py, line 209,
in restricted
exec ccode in environment
File
/home/paolo/Dropbox/git/web2py/applications/bikend/controllers/club.py,
line 140, in module
File /home/paolo/Dropbox/git/web2py/gluon/globals.py, line 184, in
lambda
self._caller = lambda f: f()
File
/home/paolo/Dropbox/git/web2py/applications/bikend/controllers/club.py,
line 3, in index
d= dict(clubs = get_clubs())
File
/home/paolo/Dropbox/git/web2py/applications/bikend/models/clubDB.py, line
25, in get_clubs
dates = db(query).select(s,count,orderby=~s, limitby=limitby,
groupby=s)
File /home/paolo/Dropbox/git/web2py/gluon/dal.py, line 8787, in
select
return adapter.select(self.query,fields,attributes)
File /home/paolo/Dropbox/git/web2py/gluon/dal.py, line 2127, in
select
return super(SQLiteAdapter, self).select(query, fields, attributes)
File /home/paolo/Dropbox/git/web2py/gluon/dal.py, line 1615, in
select
return self._select_aux(sql,fields,attributes)
File /home/paolo/Dropbox/git/web2py/gluon/dal.py, line 1596, in
_select_aux
return processor(rows,fields,self._colnames,cacheable=cacheable)
File /home/paolo/Dropbox/git/web2py/gluon/dal.py, line 1974, in parse
fields[j].type,blob_decode)
IndexError: list index out of range
Paolo
On Wednesday, October 17, 2012 4:45:35 AM UTC+2, Cliff Kachinske wrote:
I don't know how it possibly worked in sqlite, but this is an aggregate
query combined with a non-aggregate query.
In other words, the count is a property of an aggregation of rows in
the database, whereas created_on is a property of individual rows.
This confuses Postgres. It doesn't know if you want the aggregate
result (count) or the result for individual rows (created_on). It cannot
deliver both from the same query.
What are you trying to find out in your query?
On Tuesday, October 16, 2012 4:59:36 PM UTC-4, Paolo wrote:
Dear all,
I've just switched from sqlite to postgres, and now I have problems
with few queries.
One query that works correctly on sqlite and fails on postgres is the
following:
s=db.club.created_on.year() | db.club.created_on.month() |
db.club.created_on.day()
count = db.club.id.count()
dates =
db(query).select(db.club.created_on,count,orderby=~db.club.created_on,
limitby=limitby, groupby=s)
Now on postgres, it raises the following error:
ProgrammingError: column club.created_on must appear in the GROUP BY
clause or be used in an aggregate function
LINE 1: SELECT club.created_on, COUNT(club.id) FROM club WHERE
(((c...
I read online that the fields in the select must be on the groupby as
well. The problem is that by grouping even by club.created_on (by adding
groupby=s | club.created_on) the result is
[web2py] Re: Query with postgres
I don't know how it possibly worked in sqlite, but this is an aggregate query combined with a non-aggregate query. In other words, the count is a property of an aggregation of rows in the database, whereas created_on is a property of individual rows. This confuses Postgres. It doesn't know if you want the aggregate result (count) or the result for individual rows (created_on). It cannot deliver both from the same query. What are you trying to find out in your query? On Tuesday, October 16, 2012 4:59:36 PM UTC-4, Paolo wrote: Dear all, I've just switched from sqlite to postgres, and now I have problems with few queries. One query that works correctly on sqlite and fails on postgres is the following: s=db.club.created_on.year() | db.club.created_on.month() | db.club.created_on.day() count = db.club.id.count() dates = db(query).select(db.club.created_on,count,orderby=~db.club.created_on, limitby=limitby, groupby=s) Now on postgres, it raises the following error: ProgrammingError: column club.created_on must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT club.created_on, COUNT(club.id) FROM club WHERE (((c... I read online that the fields in the select must be on the groupby as well. The problem is that by grouping even by club.created_on (by adding groupby=s | club.created_on) the result is totally different. What can I do to tackle this problem? Best, Paolo --
