From: Olivier R. <oli...@un...> - 2006-10-17 16:26:55
|
Hi, I use SQLObject for my project and I want to use a GROUP BY query. But the folowing line does not work : Item.select(groupBy='name') How can I make a GROUP BY query ? Thanks. Olivier |
From: Oleg B. <ph...@ph...> - 2006-10-17 17:03:25
|
On Tue, Oct 17, 2006 at 06:26:53PM +0200, Olivier Ravard wrote: > I use SQLObject for my project and I want to use a GROUP BY query. > But the folowing line does not work : > > Item.select(groupBy='name') Think for a minute about the underlying SQL. Item.select() runs the query SELECT * FROM item and fills the list of object returning by the call with values of attributes from the table, according to the description of columns you have gave in the Item class: class Item(SQLObject): name = StringCol() Right? Well, now think about Item.select(groupBy='name') It should generate the query SELECT COUNT(*) FROM item GROUP BY name. Oops. Where from should come that COUNT()? May be you want AVG()? Or MAX()? Where to put the resulting values? The Item class doesn't have any description for it. > How can I make a GROUP BY query ? using lower-level module SQLBuilder: from sqlobject.sqlbuilder import Select select = Select([Item.q.name], groupBy="name") results = connection.query(connection.sqlrepr(select)) Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Olivier R. <oli...@un...> - 2006-10-17 17:41:59
|
Oleg Broytmann wrote: > On Tue, Oct 17, 2006 at 06:26:53PM +0200, Olivier Ravard wrote: > >> I use SQLObject for my project and I want to use a GROUP BY query. >> But the folowing line does not work : >> >> Item.select(groupBy='name') >> > > Think for a minute about the underlying SQL. > Item.select() > runs the query > SELECT * FROM item > and fills the list of object returning by the call with values of > attributes from the table, according to the description of columns you have > gave in the Item class: > > class Item(SQLObject): > name = StringCol() > > Right? Well, now think about > Item.select(groupBy='name') > It should generate the query > SELECT COUNT(*) FROM item GROUP BY name. > Oops. Where from should come that COUNT()? May be you want AVG()? Or > MAX()? > Where to put the resulting values? The Item class doesn't have any > description for it. > > >> How can I make a GROUP BY query ? >> > > using lower-level module SQLBuilder: > > from sqlobject.sqlbuilder import Select > select = Select([Item.q.name], groupBy="name") > results = connection.query(connection.sqlrepr(select)) > Thanks. It was what I am searching... But when I run this part of code, it returns None ! > Oleg. > |
From: Oleg B. <ph...@ph...> - 2006-10-17 19:38:38
|
On Tue, Oct 17, 2006 at 07:41:51PM +0200, Olivier Ravard wrote: > > from sqlobject.sqlbuilder import Select > > select = Select([Item.q.name], groupBy="name") > > results = connection.query(connection.sqlrepr(select)) > > > Thanks. It was what I am searching... > But when I run this part of code, it returns None ! Sorry. connection.queryAll(...). .query() is for queries without results - CREATE/INSERT/UPDATE/DELETE. There is also .queryOne() to fetch one row for a query like SELECT COUNT(*) that returns exactly one row. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Olivier R. <oli...@un...> - 2006-10-18 07:47:14
|
Oleg Broytmann wrote: > On Tue, Oct 17, 2006 at 07:41:51PM +0200, Olivier Ravard wrote: > >>> from sqlobject.sqlbuilder import Select >>> select = Select([Item.q.name], groupBy="name") >>> results = connection.query(connection.sqlrepr(select)) >>> >>> >> Thanks. It was what I am searching... >> But when I run this part of code, it returns None ! >> > > Sorry. connection.queryAll(...). .query() is for queries without results > - CREATE/INSERT/UPDATE/DELETE. There is also .queryOne() to fetch one row > for a query like SELECT COUNT(*) that returns exactly one row. > > Oleg. > Fine. It works. Thanks. Olivier |
From: Guillermo H. <gh...@gm...> - 2007-12-17 17:52:26
|
Hi: I am doing this to get pooled data in my application In [183]: class Person(SQLObject): _connection = connection nombre = StringCol() .....: .....: In [191]: personas = Person.select() In [192]: for person in personas: .....: print person.nombre .....: .....: Juan Pedro Luis Pedro In [193]: select = Select([Person.q.nombre], groupBy="nombre") In [194]: results = connection.queryAll(connection.sqlrepr(select)) In [195]: for person in results: .....: print person .....: .....: ('Juan',) In [196]: 1. Which may be the mistake? 2. It may be implemented in sqlobject GROUP BY + ORDER BY Thanks! [1] http://www.mail-archive.com/sqlobject-discuss% 0lists.sourceforge.net/msg01712.html -- +--------------------------------- | Heizenreder Guillermo | http://gheize.wordpress.com/ | http://code.google.com/u/gheize/ | http://tipslinux.blogspot.com/ +--------------------------------- |
From: Oleg B. <ph...@ph...> - 2007-12-17 18:18:00
|
On Mon, Dec 17, 2007 at 02:50:33PM -0300, Guillermo Heizenreder wrote: > In [195]: for person in results: > .....: print person > .....: > .....: > ('Juan',) > > In [196]: > > 1. Which may be the mistake? Why do you think you have a mistake? Did you expect "person" to be a string instead of tuple? > 2. It may be implemented in sqlobject GROUP BY + ORDER BY Select([Person.q.nombre], groupBy="nombre", orderBy="nombre") Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: <pet...@tp...> - 2008-01-19 12:08:40
|
Hi, trying to select ALL rows from the table with the "max" value in a specified field I can go: tmax=Prodcats.select().max('tStamp') for row in Prodcats.selectBy('tStamp' == tmax): print row Just wander if there is some other way how to get rows with max (min....) values in specified field from the table Regards Petr Jakes |
From: Oleg B. <ph...@ph...> - 2008-01-19 20:20:57
|
On Sat, Jan 19, 2008 at 01:08:38PM +0100, Petr Jake?? wrote: > trying to select ALL rows from the table with the "max" value in a specified > field > I can go: > > tmax=Prodcats.select().max('tStamp') > for row in Prodcats.selectBy('tStamp' == tmax): > print row > > Just wander if there is some other way how to get rows with max (min....) > values in specified field from the table This is the best you can do. It possible to use HAVING clause (not in .select(), of course, but in sqlbuilder.Select()) or a subquery, but they are more complex solutions. PS. You know now that the syntax of .selectBy() is different. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Simon C. <hod...@gm...> - 2008-01-19 20:37:03
|
On Jan 19, 2008 10:20 PM, Oleg Broytmann <ph...@ph...> wrote: > On Sat, Jan 19, 2008 at 01:08:38PM +0100, Petr Jake?? wrote: > > trying to select ALL rows from the table with the "max" value in a specified > > field > > I can go: > > > > tmax=Prodcats.select().max('tStamp') > > for row in Prodcats.selectBy('tStamp' == tmax): > > print row > > > > Just wander if there is some other way how to get rows with max (min....) > > values in specified field from the table > > This is the best you can do. It possible to use HAVING clause (not in > .select(), of course, but in sqlbuilder.Select()) or a subquery, but they > are more complex solutions. One can also you subselects: from sqlobject import IN from sqlobject.sqlbuilder import Select for row in Prodcats.select(IN(Prodcats.q.tStamp,Select(Prodcats.q.tStamp,orderBy=Prodcats.q.tStamp,limit=1))): print row I haven't actually tested this, but I think the theory is sound. There may be a better way to phrase this subselect (e.g. without using IN or limit) but I'll leave that as an exercise for the reader. :P Schiavo Simon |
From: Oleg B. <ph...@ph...> - 2008-01-19 21:25:15
|
On Sat, Jan 19, 2008 at 10:36:59PM +0200, Simon Cross wrote: > > This is the best you can do. It possible to use HAVING clause (not in > > .select(), of course, but in sqlbuilder.Select()) or a subquery > > One can also you subselects: Yes, I called them subqueries. :) Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |