On Nov 19, 2013, at 7:10 AM, Chris Withers <ch...@simplistix.co.uk> wrote:
> 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()
we don’t support query(Cls.relationshipname) as of yet, it’s spitting out the
join condition into the columns clause, and in any case you’d need to specify a
real join() in order to get Part and Instrument into the same SELECT:
query(Instrument,
func.sum(Part.quantity)).select_from(Part).join(Part.instrument)…
>
> ...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.
signature.asc
Description: Message signed with OpenPGP using GPGMail
