I think I found the problem here. The error message in the traceback is
coming from MySQL. Using conn.sqlrepr(query) I found the exact query that
was being sent to the server. It is:
SELECT CheckLog.account, CheckLog.subAccount, CheckLog.accountName,
Sum(CheckLog.amount) FROM CheckLog GROUP BY (CheckLog.account,
CheckLog.subAccount, CheckLog.accountName) ORDER BY CheckLog.account
MySQL doesn't like the parentheses around the Group By columns. Anyone know
if I can override that somehow in SO?
-Jim
-----Original Message-----
From: [email protected] [mailto:[EMAIL PROTECTED] On
Behalf Of Jim Steil
Sent: Thursday, January 25, 2007 11:32 AM
To: [email protected]
Subject: [TurboGears] Problem with Group By using multiple columns
Hi:
I'm trying to get the SQLObject syntax correct to give me the following SQL
statement.
SELECT account, subAccount, accountName, sum(amount) FROM CheckLog
Group by account, subAccount, accountName, order by account, subAccount,
accountName
So far I've got it working with 1 group by column as follows:
------------
import sqlobect
from sqlobject.sqlbuilder import *
from model import CheckLog
conn = CheckLog._connection
query = Select([CheckLog.q.account, func.Sum(CheckLog.q.amount)],
groupBy=CheckLog.q.account, orderBy=CheckLog.q.account)
x = conn.queryAll(conn.sqlrepr(query))
------------
But when I try the following with multiple group by columns, it doesn't
work:
------------
query = Select([CheckLog.q.account, CheckLog.q.subAccount,
CheckLog.q.accountName, func.Sum(CheckLog.q.amount)],
groupBy=[CheckLog.q.account, CheckLog.q.subAccount,
CheckLog.q.accountNumber], orderBy=CheckLog.q.account)
x = conn.queryAll(conn.sqlrepr(query))
------------
I get the following traceback...
------------
Traceback (most recent call last):
File "<console>", line 1, in ?
File
"c:\python24\lib\site-packages\SQLObject-0.7.2-py2.4.egg\sqlobject\dbconnect
ion.py", line 757, in queryAll
return self._dbConnection._queryAll(self._connection, s)
File
"c:\python24\lib\site-packages\SQLObject-0.7.2-py2.4.egg\sqlobject\dbconnect
ion.py", line 313, in _queryAll
self._executeRetry(conn, c, s)
File
"c:\python24\lib\site-packages\SQLObject-0.7.2-py2.4.egg\sqlobject\mysql\
mysqlconnection.py", line 78, in _executeRetry
return cursor.execute(myquery)
File "C:\Python24\lib\site-packages\MySQLdb\cursors.py", line 163, in
execute
self.errorhandler(self, exc, value)
File "C:\Python24\lib\site-packages\MySQLdb\connections.py", line 35, in
defaulterrorhandler
raise errorclass, errorvalue
OperationalError: (1241, 'Operand should contain 1 column(s)')
------------
Anyone seen this before, or preferably, have a solution to my problem?
-Jim
Jim Steil
IT Manager
Quality Liquid Feeds
(608) 935-2345
(608) 341-9896 cell
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"TurboGears" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/turbogears?hl=en
-~----------~----~----~----~------~----~------~--~---
