sql - How to generate this query in sqlalchemy? -

i want generate query in sqlalchemy. table 'demande' exists in database. there subquery generates timesteps generate_series function.

select     timesteps.timestep timestep, d.count count     (select         date_trunc('hour',date_demande) timestep,         count(id) count              demande      group         timestep     ) d  right outer join     (select         timestep              generate_series('2010-01-01 00:00:00'::timestamp,                         '2010-01-01 23:59:59'::timestamp,                         '1 hour'::interval) timestep      ) timesteps   on d.timestep = timesteps.timestep  order timestep; 

i've tried :

stmt = session.query(         func.             generate_series(                 datetime.datetime(2010,1,1,0,0,0),                 datetime.datetime(2010,1,1,23,59,59),                 cast('1 hour',interval())).             label('timestep')         ).subquery() print stmt q = session.query(         stmt.c.timestep,         func.count(demande.id)).     outerjoin((demande, grouped==stmt.c.timestep)).     group_by(stmt.c.timestep) print q 

but complains invalidrequeserror: not find clause join from. guess caused subquery.

if try "invert" query, works 'left outer join' :

q = session.query(         func.count(demande.id),         stmt.c.timestep).     outerjoin((stmt, grouped==stmt.c.timestep)).     group_by(stmt.c.timestep) 

as there no right outer join in sqlalchemy, want find way take subquery first table , 'demande' table second one. way able use left outer join

the following example should give clue (assuming correctly guessed demande declarative model):

joined = stmt.outerjoin(demande.__table__, demande.grouped==stmt.c.timestep) q = session.query(stmt.c.timestep, func.count(demande.id)).\         select_from(joined).\         group_by(stmt.c.timestep)