[sqlalchemy] Exists/Correlate newbie question

[dan]

I've got a SQL query I am trying to convert over into sqlalchemy,
however I just can't suss out the correlate and exists part of
sqlalchemy. My query is:

SELECT m_date,sensor_id,m_value,d_report_hour
  FROM multi_obs mo
  WHERE
    m_date >= '2011-03-23T00:00:00' and m_date < '2011-03-23T24:00:00'
AND
    sensor_id = 4644 AND
    EXISTS (SELECT d_report_hour
      FROM multi_obs
      WHERE
       m_date >= '2011-03-23T00:00:00' AND m_date <
'2011-03-23T24:00:00' AND
       sensor_id=518 and
       mo.d_report_hour=d_report_hour)
  ORDER BY sensor_id ASC, d_report_hour ASC;

I've got my multi_obs table already in an object in sqlalchemy. I am
not sure how to get the "mo" correlation working inside the EXISTS
subquery. I cobbled up this:

matchDatesQ = qaqc.db.session.query(multi_obs).\
                filter(multi_obs.m_date >= beginDate).\
                filter(multi_obs.m_date < endDate).\
                filter(multi_obs.sensor_id == nnSensorId).\
                filter(multi_obs.d_report_hour == mo.d_report_hour).\
                filter(multi_obs.d_top_of_hour == 1).\
                correlate(multi_obs).\
                subquery()
recs = qaqc.db.session.query(multi_obs).\
                  correlate('mo').\
                  filter(multi_obs.m_date >= beginDate).\
                  filter(multi_obs.m_date < endDate).\
                  filter(multi_obs.sensor_id == sensorId).\
                  filter(multi_obs.d_top_of_hour == 1).\
                  filter(exists(matchDatesQ)).\
                  order_by(multi_obs.m_date.asc()).all()

however, I get errors in the matchesDateQ query about the "mo" not
being defined. I understand why that is, I just am not sure how to go
about getting this to work.

Any tips/hints are greatly appreciated.

Dan

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.