sqlobject-discuss

[SQLObject] Elegant way of running custom queries

[Simon W. <cs...@ba...>]

Hi all,

In an application I'm developing, I need to run a query that I don't 
think is supported natively by SQLObject. I have a table of Stories, 
each including the date it was created. I want to find all days within a 
certain month which contain at least one story. Here's the query I need 
to run constructed by hand:

select
   distinct dayofmonth(date)
from
   story
where
   date >= '2003-09-01'
and
   date < '2003-10-01'

Since this isn't natively supported by SQLObject, what would be the most 
elegant / "best practise" method for implementing the query? I'd rather 
not import another Postgres module and use that, so I'm currently 
swinging towards adding an extra method to the Story class which 
executes the query using SQLObject's own query mechanism and returns the 
result. Is there a neater way of doing this?

Thanks,

Simon

Re: [SQLObject] Elegant way of running custom queries

[Ian B. <ia...@co...>]

On Wednesday, September 10, 2003, at 03:54 AM, Simon Willison wrote:
> In an application I'm developing, I need to run a query that I don't 
> think is supported natively by SQLObject. I have a table of Stories, 
> each including the date it was created. I want to find all days within 
> a certain month which contain at least one story. Here's the query I 
> need to run constructed by hand:
>
> select
>   distinct dayofmonth(date)
> from
>   story
> where
>   date >= '2003-09-01'
> and
>   date < '2003-10-01'

Yeah, the distinct part isn't going to work.  Getting SQLObject to 
handle aggregate functions would be... I don't know.  Still not sure 
what that would mean -- some sort of functional style.

So, the SQL is probably the best you will get.  So I'd just do it like:

class Story(SQLObject):
     ...
     def daysWithStories(cls, month, connection=None):
         if connection is None:
             connection = cls._connection
         result = connection.queryOne(...your SQL...)
         return result[0]
     daysWithStories = classmethod(daysWithStories)

At least that way it will look like part of your class, if not part of 
SQLObject.

   Ian