you want to label your func.sum() in the CTE:
positions = session.query(Instrument,
func.sum(Part.quantity).label("quantity"))\
then refer to quantity and instrument_id using .c. since positions is now a
core CTE element:
session.query(Instrument, positions.c.quantity, Observation)\
.select_from(positions)\
.join(Observation, positions.c.instrument_id==Observation.instrument_id)\
.filter(Observation.period.contains(datetime(2013, 11, 1)))\
.all()
but also I’d probably move “instrument” out of the CTE as a whole, grouping by
a whole table is not great idea…
On Nov 19, 2013, at 6:32 PM, Chris Withers <ch...@simplistix.co.uk> wrote:
> On 19/11/2013 14:49, Michael Bayer wrote:
>>
>>> 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)…
>
> Gotcha, select_from was the bit missing from my brain, so the query becomes:
>
> session.query(Instrument, func.sum(Part.quantity))\
> .select_from(Part)\
> .join(Part.instrument)\
> .filter((Part.account_id=='td') & (Part.timestamp < date(2013, 11, 1)))\
> .group_by(Instrument)\
> .all()
>
> Okay, now I want to turn it into a CTE, outer join that to a pricing table
> and select instrument, sum(quantity) and the Price object (which might be
> None) from the join.
>
> I'd guess something like:
>
> positions = session.query(Instrument, func.sum(Part.quantity))\
> .select_from(Part)\
> .join(Part.instrument)\
> .filter((Part.account_id=='td') & (Part.timestamp < date(2013, 11, 1)))\
> .group_by(Instrument)\
> .cte('positions')
>
> session.query(Instrument, func.sum(Part.quantity), Observation)\
> .select_from(positions)\
> .join(Observation, positions.instrument_id==Observation.instrument_id)\
> .filter(Observation.period.contains(datetime(2013, 11, 1)))\
> .all()
>
> ..but that gives:
>
> AttributeError: 'CTE' object has no attribute 'instrument_id'
>
> What syntax am I looking for?
>
> cheers,
>
> 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
