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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
