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.
