Thread: [SQLObject] Trying to use HAVING clause
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Richard J. <rj...@ek...> - 2008-01-24 08:18:18
|
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) against a table: Name Null? Type ----------------------------------------- -------- ------------ ICCID NOT NULL VARCHAR2(30) ACTIVATED NOT NULL NUMBER ACTIVITY NOT NULL DATE 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). Feeding the sqlbuilder.Select directly to the SQLObject.select() doesn't work, obviously :) Could I please have some additional hints as to how I might get my desired SQL to execute? Thanks, Richard |
From: Oleg B. <ph...@ph...> - 2008-01-24 13:58:36
|
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/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Richard J. <rj...@ek...> - 2008-01-24 21:01:30
|
On Fri, 25 Jan 2008, Oleg Broytmann wrote: > On Thu, Jan 24, 2008 at 07:17:55PM +1100, Richard Jones wrote: > > 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. Hmm, so I could just use the SQL statement string I've already written? > 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() Thanks for your explanation, now how do I get a connection? :) I'm using TurboGears (for the first time). I guess I need to sign up to a TG mailing list now... Richard |
From: Oleg B. <ph...@ph...> - 2008-01-24 21:09:18
|
On Fri, Jan 25, 2008 at 08:01:13AM +1100, Richard Jones wrote: > On Fri, 25 Jan 2008, Oleg Broytmann wrote: > > On Thu, Jan 24, 2008 at 07:17:55PM +1100, Richard Jones wrote: > > 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. > > Hmm, so I could just use the SQL statement string I've already written? You can, if you don't want for SQLObject to generate it. > Thanks for your explanation, now how do I get a connection? :) > I'm using TurboGears No idea how to get it from TG. SQLObject creates a connection if you declare DB URI, or you can create a connection from DB URI yourself calling connectionForURI(URI). Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: <pet...@tp...> - 2008-01-24 22:21:34
|
> > Thanks for your explanation, now how do I get a connection? :) > > I'm using TurboGears (for the first time). I guess I need to sign up to a > TG > mailing list now... > Hi, in my case, I am doing following: ======= snip ======================== if os.name == 'posix': db_filename = '/opt/firebird/examples/empbuild/automat.fdb' else: db_filename = 'c|\\Program Files\\Firebird\\Firebird_2_0\\examples\\empbuild\\automat.fdb' connection_string = 'firebird://127.0.0.1:3050/' + db_filename connection = connectionForURI(connection_string) trans = connection.transaction() sqlhub.processConnection = connection ======= snip end ===================== maybe you don't need the row:trans = connection.transaction() HTH Petr |
From: Richard J. <rj...@ek...> - 2008-01-24 22:42:44
|
On Friday 25 January 2008 08:01:13 Richard Jones wrote: > Thanks for your explanation, now how do I get a connection? :) > > I'm using TurboGears (for the first time). I guess I need to sign up to a > TG mailing list now... FWIW the magic is: from turbogears import database database.PackageHub('PACKAGE NAME').getConnection() Where 'PACKAGE NAME' is obviously your TurboGears package name. Thanks for the help! Richard |