On Mar 24, 2011, at 2:19 PM, dan wrote:
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.
so you want to reference multi_obs in two ways, one is aliased. For that you
use the aliased() construct:
mo = aliased(multi_obs)
that's where you get 'mo' from - you then construct the outer query using the
'mo' object, i.e. mo.m_date = beginDate etc.. The inner query then can
reference mo. Its all replacing string identifiers in the SQL with Python
identities (i.e. objects) in your program.The above shouldn't need any
explicit calls to correlate as it is automatic for basic scenarios (it also
generally is given a selectable object, not a string).
--
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.
