Hi All,
I have this simple model/table:
class Part(Common, Base):
__tablename__ = 'transaction'
id = Column(Integer(), primary_key=True) # one per transaction
part = Column(Integer(), primary_key=True) # one per part
timestamp = Column(DateTime(), nullable=False)
source = Column(String(20), primary_key=True)
account_id = account_col()
account = relationship("Account")
instrument_id = instrument_col(primary_key=False)
instrument = relationship("Instrument")
quantity = Column(Numeric())
This query (which seems simple enough to me):
session.query(Part.instrument, func.sum(Part.quantity))\
.filter((Part.account_id=='td') & (Part.timestamp < date(2013,
11, 1)))\
.group_by(Part.instrument)\
.all()
...gives a rather bizarre result:
[(False, Decimal('-71385059.09597850000')),
(True, Decimal('-16737.41127690000'))]
Tweak it slightly and it blows up like this:
session.rollback()
session.query(Part.instrument, func.sum(Part.quantity))\
.filter((Part.account_id=='td') & (Part.timestamp < date(2013,
11, 1)))\
.group_by(Part.instrument_id)\
.all()
<ipython-input-43-c2c5e3817a95> in <module>()
2 session.query(Part.instrument, func.sum(Part.quantity))\
3 .filter((Part.account_id=='td') & (Part.timestamp <
date(2013, 11, 1)))\
----> 4 .group_by(Part.instrument_id)\
5 .all()
/Users/chris/buildout-eggs/SQLAlchemy-0.8.2-py2.7-macosx-10.5-x86_64.egg/sqlalchemy/orm/query.pyc
in all(self)
2235
2236 """
-> 2237 return list(self)
2238
2239 @_generative(_no_clauseelement_condition)
/Users/chris/buildout-eggs/SQLAlchemy-0.8.2-py2.7-macosx-10.5-x86_64.egg/sqlalchemy/orm/query.pyc
in __iter__(self)
2347 if self._autoflush and not self._populate_existing:
2348 self.session._autoflush()
-> 2349 return self._execute_and_instances(context)
2350
2351 def _connection_from_session(self, **kw):
/Users/chris/buildout-eggs/SQLAlchemy-0.8.2-py2.7-macosx-10.5-x86_64.egg/sqlalchemy/orm/query.pyc
in _execute_and_instances(self, querycontext)
2362 close_with_result=True)
2363
-> 2364 result = conn.execute(querycontext.statement, self._params)
2365 return loading.instances(self, result, querycontext)
2366
/Users/chris/buildout-eggs/SQLAlchemy-0.8.2-py2.7-macosx-10.5-x86_64.egg/sqlalchemy/engine/base.pyc
in execute(self, object, *multiparams, **params)
660 object,
661 multiparams,
--> 662 params)
663 else:
664 raise exc.InvalidRequestError(
/Users/chris/buildout-eggs/SQLAlchemy-0.8.2-py2.7-macosx-10.5-x86_64.egg/sqlalchemy/engine/base.pyc
in _execute_clauseelement(self, elem, multiparams, params)
759 compiled_sql,
760 distilled_params,
--> 761 compiled_sql, distilled_params
762 )
763 if self._has_events:
/Users/chris/buildout-eggs/SQLAlchemy-0.8.2-py2.7-macosx-10.5-x86_64.egg/sqlalchemy/engine/base.pyc
in _execute_context(self, dialect, constructor, statement, parameters,
*args)
872 parameters,
873 cursor,
--> 874 context)
875
876 if self._has_events:
/Users/chris/buildout-eggs/SQLAlchemy-0.8.2-py2.7-macosx-10.5-x86_64.egg/sqlalchemy/engine/base.pyc
in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
1022 self.dialect.dbapi.Error,
1023
connection_invalidated=self._is_disconnect),
-> 1024 exc_info
1025 )
1026
/Users/chris/buildout-eggs/SQLAlchemy-0.8.2-py2.7-macosx-10.5-x86_64.egg/sqlalchemy/util/compat.pyc
in raise_from_cause(exception, exc_info)
193 # the code line where the issue occurred
194 exc_type, exc_value, exc_tb = exc_info
--> 195 reraise(type(exception), exception, tb=exc_tb)
196
197
/Users/chris/buildout-eggs/SQLAlchemy-0.8.2-py2.7-macosx-10.5-x86_64.egg/sqlalchemy/engine/base.pyc
in _execute_context(self, dialect, constructor, statement, parameters,
*args)
865 statement,
866 parameters,
--> 867 context)
868 except Exception, e:
869 self._handle_dbapi_exception(
/Users/chris/buildout-eggs/SQLAlchemy-0.8.2-py2.7-macosx-10.5-x86_64.egg/sqlalchemy/engine/default.pyc
in do_execute(self, cursor, statement, parameters, context)
322
323 def do_execute(self, cursor, statement, parameters,
context=None):
--> 324 cursor.execute(statement, parameters)
325
326 def do_execute_no_params(self, cursor, statement,
context=None):
ProgrammingError: (ProgrammingError) column "instrument.id" must appear
in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT instrument.id = transaction.instrument_id AS instrume...
^
'SELECT instrument.id = transaction.instrument_id AS instrument,
sum(transaction.quantity) AS sum_1 \nFROM instrument, transaction
\nWHERE transaction.account_id = %(account_id_1)s AND
transaction.timestamp < %(timestamp_1)s GROUP BY
transaction.instrument_id' {'account_id_1': 'td', 'timestamp_1':
datetime.date(2013, 11, 1)}
What am I doing wrong?
Chris
--
Simplistix - Content Management, Batch Processing & Python Consulting
- http://www.simplistix.co.uk
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
