On 19/11/2013 23:36, Michael Bayer wrote:
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…
Yep, in fact ended up with:
positions = session.query(Part.instrument_id,
func.sum(Part.quantity).label("quantity"))\
.filter((Part.account_id=='td') & (Part.timestamp < date(2013,
11, 1)))\
.group_by(Part.instrument_id)\
.having(func.sum(Part.quantity)!=0).cte('positions')
...which left me wondering, if I had the query asL
query = """
select instrument_id, sum(quantity) as quantity
from transaction where
account_id = :account_id and
timestamp < :dt and
group by instrument_id
having sum(quantity) != 0
"""
How could I combine that string with values for account_id and dt into a
cte? I'm expecting something involving text(), but I can't figure out
the right incantation...
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.
