SQLObject

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/            phd@...
           Programmers don't die, they just GOSUB without RETURN.

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?

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-

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?

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/            phd@...
           Programmers don't die, they just GOSUB without RETURN.

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/            phd@...
           Programmers don't die, they just GOSUB without RETURN.

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.

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.

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/            phd@...
           Programmers don't die, they just GOSUB without RETURN.

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/            phd@...
           Programmers don't die, they just GOSUB without RETURN.

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...

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.