I am using sqlalchemy on a database of Google Transit Feed data. The
models are from pygtfs:
https://github.com/jarondl/pygtfs/blob/master/pygtfs/gtfs_entities.py
Basically, StopTime is related both to Stop and Trip. I want all of the
stoptimes of all of the trips which have one StopTime that has a particular
stop.
I believe this SQL does that:
stoptimes1 = session.execute('''
SELECT * FROM stop_times
JOIN trips ON stop_times.trip_id=trips.trip_id
JOIN stop_times AS st2 ON st2.trip_id=trips.trip_id
WHERE stop_times.stop_id=635 ORDER BY st2.stop_sequence
''').fetchall()
And I thought that this would be a translation of that into sqlalchemy:
from sqlalchemy.orm import aliased
st2 = aliased(StopTime)
stoptimes2 =
session.query(StopTime).join(StopTime.trip).filter(StopTime.stop_id ==
u'635').join(st2,Trip).order_by(st2.stop_sequence)
But it is not:
len(stoptimes1)
2848
len(stoptimes2)
109
Where 109 is how many trips have a StopTime with stop_id == 635, and 2848
is a much more reasonable number because each of those trips has many of
stop times.
I would really appreciate getting some insight into how to do in sqlalchemy
what was done with the SQL.
Thanks,
Elliot
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
