Thread: [SQLObject] sqlobject and group by
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Fred C <bs...@gm...> - 2006-05-08 17:04:12
|
Hello I would like to execute a request with a GROUP BY and I understand that can be done directly with SQLObject and has to be done using SQLBuilder. But how ? I get weird errors. Can someone post an example on this list and may be put en entry in the FAQ. I think it would be useful for everyone new who try to learn and understant SQLObject. Thanks -fred- |
From: Oleg B. <ph...@ma...> - 2006-05-08 17:30:30
|
On Mon, May 08, 2006 at 01:03:53PM -0400, Fred C wrote: > I would like to execute a request with a GROUP BY and I understand > that can be done directly with SQLObject and has to be done using > SQLBuilder. But how ? connection.query(connection.sqrepr(Select("table", ["SUM(price)"], groupBy="shop"))) Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Robert S. <rsz...@mu...> - 2006-05-09 10:21:39
|
Oleg Broytmann wrote: > On Mon, May 08, 2006 at 01:03:53PM -0400, Fred C wrote: > >>I would like to execute a request with a GROUP BY and I understand >>that can be done directly with SQLObject and has to be done using >>SQLBuilder. But how ? > > > connection.query(connection.sqrepr(Select("table", ["SUM(price)"], groupBy="shop"))) Ouch - second parameter is expected to be the where clause, right? My sepcific problem is: SELECT COUNT(*) FROM table WHERE MONTH(dateAdded)=5 GROUP BY DAY(dateAdded) Now, how do you (wrestle to) express that in SQLObject? |
From: Fred C <bs...@gm...> - 2006-05-09 11:08:35
|
On May 9, 2006, at 6:21 AM, Robert Szefler wrote: > Oleg Broytmann wrote: >> On Mon, May 08, 2006 at 01:03:53PM -0400, Fred C wrote: >>> I would like to execute a request with a GROUP BY and I >>> understand that can be done directly with SQLObject and has to >>> be done using SQLBuilder. But how ? >> connection.query(connection.sqrepr(Select("table", ["SUM(price)"], >> groupBy="shop"))) > > Ouch - second parameter is expected to be the where clause, right? > > My sepcific problem is: > > SELECT COUNT(*) FROM table WHERE MONTH(dateAdded)=5 GROUP BY DAY > (dateAdded) > > Now, how do you (wrestle to) express that in SQLObject? I did that and that works fine. conn = searchTags._connection sel = conn.sqlrepr(Select((func.COUNT('*'), searchTags.q.tag), groupBy=searchTags.q.tag, orderBy=DESC(func.COUNT('*')), limit='100')) tags = list(conn.queryAll(sel)) -fred- |
From: Robert S. <rsz...@mu...> - 2006-05-09 11:15:54
|
Fred C wrote: > > On May 9, 2006, at 6:21 AM, Robert Szefler wrote: > >> Oleg Broytmann wrote: >> >>> On Mon, May 08, 2006 at 01:03:53PM -0400, Fred C wrote: >>> >>>> I would like to execute a request with a GROUP BY and I understand >>>> that can be done directly with SQLObject and has to be done using >>>> SQLBuilder. But how ? >>> >>> connection.query(connection.sqrepr(Select("table", ["SUM(price)"], >>> groupBy="shop"))) >> >> >> Ouch - second parameter is expected to be the where clause, right? >> >> My sepcific problem is: >> >> SELECT COUNT(*) FROM table WHERE MONTH(dateAdded)=5 GROUP BY DAY >> (dateAdded) >> >> Now, how do you (wrestle to) express that in SQLObject? > > > I did that and that works fine. > > conn = searchTags._connection > sel = conn.sqlrepr(Select((func.COUNT('*'), searchTags.q.tag), > groupBy=searchTags.q.tag, > orderBy=DESC(func.COUNT('*')), > limit='100')) > tags = list(conn.queryAll(sel)) > > -fred- OK, already found the solution (the query is a bit more complicated): conn.sqlrepr(Select( [func.DAY(Wydarzenia.q.data),func.COUNT(Wydarzenia.q.id)], where=(AND( func.MONTH(Wydarzenia.q.data)==miesiac, func.YEAR(Wydarzenia.q.data)==rok )), groupBy=func.DAY(Wydarzenia.q.data) )) I've had to change COUNT(*) to COUNT(id), though. func.COUNT('*') does not work in general as it generates COUNT('*') in SQL. (Well, actually it works by a leap of luck here, but relying on a hack is bad). Any clue on how to get COUNT(*) itself? |
From: Oleg B. <ph...@ma...> - 2006-05-09 11:22:16
|
On Tue, May 09, 2006 at 01:15:40PM +0200, Robert Szefler wrote: > Any clue on how to get COUNT(*) itself? Untested: from sqlobjec.sqlbuilder import func, const star = getattr(const, '*') func.COUNT(star) Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Robert S. <rsz...@mu...> - 2006-05-09 11:33:40
|
Oleg Broytmann wrote: > On Tue, May 09, 2006 at 01:15:40PM +0200, Robert Szefler wrote: > >>Any clue on how to get COUNT(*) itself? > > > Untested: > > from sqlobjec.sqlbuilder import func, const > star = getattr(const, '*') > > func.COUNT(star) > > Oleg. Works for me. But man, is this ugggly. |
From: Oleg B. <ph...@ma...> - 2006-05-09 11:45:51
|
On Tue, May 09, 2006 at 01:33:10PM +0200, Robert Szefler wrote: > >from sqlobjec.sqlbuilder import func, const > >star = getattr(const, '*') > > > >func.COUNT(star) > > > Works for me. But man, is this ugggly. What would be a better API in this case? Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Robert S. <rsz...@mu...> - 2006-05-09 11:55:50
|
Oleg Broytmann wrote: > On Tue, May 09, 2006 at 01:33:10PM +0200, Robert Szefler wrote: > >>>from sqlobjec.sqlbuilder import func, const >> >>>star = getattr(const, '*') >>> >>>func.COUNT(star) >>> >> >>Works for me. But man, is this ugggly. > > > What would be a better API in this case? Maybe const.STAR. Maybe make func.COUNT() generate COUNT(*). I don't know exactly... |
From: Robert S. <rsz...@mu...> - 2006-05-09 12:11:28
|
Robert Szefler wrote: > Oleg Broytmann wrote: > >> On Tue, May 09, 2006 at 01:33:10PM +0200, Robert Szefler wrote: >> >>>> from sqlobjec.sqlbuilder import func, const >>> >>> >>>> star = getattr(const, '*') >>>> >>>> func.COUNT(star) >>>> >>> >>> Works for me. But man, is this ugggly. >> >> >> >> What would be a better API in this case? > > > Maybe const.STAR. Maybe make func.COUNT() generate COUNT(*). I don't > know exactly... On a second thought making func.COUNT() generate COUNT(*) sounds just right. It has a touch of mathematical elegance. Looking at COUNT(col1, ..., coln) as a composition of projection and the cardinality operator it makes sense to consider COUNT(*) itself as a composition of identity projection and cardinality operator. Thus, the '*' means identity projection, or no projection at all: COUNT(*) corresponds to both card(id(.)) and card(.) - looking that way, func.COUNT() is exactly right. |
From: Oleg B. <ph...@ma...> - 2006-05-09 11:20:42
|
On Tue, May 09, 2006 at 12:21:23PM +0200, Robert Szefler wrote: > >connection.query(connection.sqrepr(Select("table", ["SUM(price)"], > >groupBy="shop"))) > > Ouch - second parameter is expected to be the where clause, right? No, the second parameter for Select() (not for .select()) is a list of columns to select. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Robert S. <rsz...@mu...> - 2006-05-09 11:40:10
|
Oleg Broytmann wrote: > On Tue, May 09, 2006 at 12:21:23PM +0200, Robert Szefler wrote: > >>>connection.query(connection.sqrepr(Select("table", ["SUM(price)"], >>>groupBy="shop"))) >> >>Ouch - second parameter is expected to be the where clause, right? > > > No, the second parameter for Select() (not for .select()) is a list of > columns to select. I get TypeError trying to create Select objects that way: Select(tab, [...], where=..., groupBy=...) --> TypeError: __init__() got multiple values for keyword argument 'where' Omitting the table name first arg makes it work. We have a dispute here then, don't we? ;) BTW. My SQLObject is 0.7rc1. It would be quite unappetizing if Select() semantics changed in post-0.7. |
From: Oleg B. <ph...@ma...> - 2006-05-09 11:48:22
|
On Tue, May 09, 2006 at 01:36:55PM +0200, Robert Szefler wrote: > I get TypeError trying to create Select objects that way: > > Select(tab, [...], where=..., groupBy=...) > > --> TypeError: __init__() got multiple values for keyword argument 'where' > > Omitting the table name first arg makes it work. We have a dispute here > then, don't we? ;) No, I just found I have forgotten how to use Select(). Unlike Insert/Update/Delete Select() doesn't accept table name, but instead accepts a list of q-magic items: Select([MyTable.q.column1, Table2.q.column2]) and derives a list of tables from them. My fault. Sorry. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |