On Thu, Jan 24, 2008 at 07:17:55PM +1100, Richard Jones wrote:
> I have the following SQL that I'd like to run:
>
> SELECT activated, max(activity) FROM account_status WHERE iccid=?
> GROUP BY iccid HAVING activity=max(activity)
>
> I've searched around and found hints towards the sqlbuilder.Select class, and
> have looked for further information about it but found none. I've looked into
> the source, but that's not really helped since the "standard"
> SQLObject.select() call seems to (eventually) result in SQL being constructed
> by something other than the sqlbuilder.Select class (ie. queryForSelect in
> dbconnection.py).
First, ideology. SQLObject.select() returns a list of rows of the table
(class) it is called upon; i.e. MyTable.select() returns a list of MyTable
instances with attributes in the instances filled from the table's columns:
for row in MyTable.select():
print row.id, row.column1, row.column2...
Because of this .select() cannot accept GROUP BY or HAVING - they return
a list of rows not from the table (columns are calculated in GROUP BY
queries).
Only sqlbuilder.Select() can be use to construct GROUP BY statements.
> Feeding the sqlbuilder.Select directly to the
> SQLObject.select() doesn't work, obviously :)
After you have created an instance of the Select class you need to
render it to an SQL query, run the query and get the results. Rendering is
done by connection's method .sqlrepr(), querying by methods .query(),
.queryAll():
for row in connection.queryAll(
connection.sqlrepr(
sqlbuilder.Select(...)
)
):
print row # every row is a tuple of columns in the order you have passed
# to Select()
PS. In SQLObject 0.10 SQLObject.select() and dbconnection.queryForSelect()
are implemented using sqlbuilder.Select(). But that's only an
implementation detail; SQLObject.select() never will accept GROUP BY.
Oleg.
--
Oleg Broytmann http://phd.pp.ru/ [EMAIL PROTECTED]
Programmers don't die, they just GOSUB without RETURN.
-------------------------------------------------------------------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2008.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
