def test_custom_queries(self):
dao = Dao(DAO_URL, sql_logging=SQL_LOG)
dao.load_gtfs(DUMMY_GTFS)
# A simple custom query: count the number of stops per type (stop/station)
# SQL equivalent: SELECT stop.location_type, count(stop.location_type) FROM stop GROUP BY stop.location_type
for type, stop_count in dao.session() \
.query(Stop.location_type, func.count(Stop.location_type)) \
.group_by(Stop.location_type) \
.all():
# print("type %d : %d stops" % (type, stop_count))
if type == Stop.TYPE_STATION:
self.assertTrue(stop_count == 3)
if type == Stop.TYPE_STOP:
self.assertTrue(stop_count > 15 and stop_count < 30)
# A more complex custom query: count the number of trips per calendar date per route on june/july
from_date = CalendarDate.ymd(2016, 6, 1)
to_date = CalendarDate.ymd(2016, 7, 31)
for date, route, trip_count in dao.session() \
.query(CalendarDate.date, Route, func.count(Trip.trip_id)) \
.join(Calendar).join(Trip).join(Route) \
.filter((func.date(CalendarDate.date) >= from_date.date) & (func.date(CalendarDate.date) <= to_date.date)) \
.group_by(CalendarDate.date, Route.route_short_name) \
.all():
# print("%s / %20s : %d trips" % (date, route.route_short_name + " " + route.route_long_name, trip_count))
self.assertTrue(date >= from_date.as_date())
self.assertTrue(date <= to_date.as_date())
self.assertTrue(trip_count > 0)
def test_autojoin(self):
dao = Dao()
query = _AutoJoiner(dao._orm, dao.session().query(Agency), Stop.stop_name == 'FOOBAR').autojoin()
self._check(query, ['routes', 'trips', 'stop_times', 'stops'])
query = _AutoJoiner(dao._orm, dao.session().query(Agency), CalendarDate.date == '2016-01-01').autojoin()
self._check(query, ['routes', 'trips', 'calendar', 'calendar_dates'])
query = _AutoJoiner(dao._orm, dao.session().query(CalendarDate), Stop.stop_name == 'FOOBAR').autojoin()
self._check(query, ['calendar', 'trips', 'stop_times', 'stops'])
query = _AutoJoiner(dao._orm, dao.session().query(CalendarDate), Agency.agency_name == 'FOOBAR').autojoin()
self._check(query, ['calendar', 'trips', 'routes', 'agency'])
query = _AutoJoiner(dao._orm, dao.session().query(Stop), CalendarDate.date == '2016-01-01').autojoin()
self._check(query, ['stop_times', 'trips', 'calendar', 'calendar_dates'])
query = _AutoJoiner(dao._orm, dao.session().query(Stop), Agency.agency_name == 'FOOBAR').autojoin()
self._check(query, ['stop_times', 'trips', 'routes', 'agency'])
query = _AutoJoiner(dao._orm, dao.session().query(Agency), (Stop.stop_name == 'FOOBAR') & (CalendarDate.date == '2016-01-01')).autojoin()
self._check(query, ['routes', 'trips', 'calendar', 'calendar_dates', 'stop_times', 'stops'])
query = _AutoJoiner(dao._orm, dao.session().query(Trip), (Route.route_long_name == 'FOOBAR') & (StopTime.departure_time > 2000) & (CalendarDate.date == '2016-01-01')).autojoin()
self._check(query, ['calendar', 'calendar_dates', 'routes', 'stop_times'])
query = _AutoJoiner(dao._orm, dao.session().query(Trip), Agency.agency_name == 'FOOBAR').autojoin()
self._check(query, ['routes', 'agency'])
query = _AutoJoiner(dao._orm, dao.session().query(Trip), Stop.stop_name == 'FOOBAR').autojoin()
self._check(query, ['stop_times', 'stops'])
query = _AutoJoiner(dao._orm, dao.session().query(Trip), CalendarDate.date == '2016-01-01').autojoin()
self._check(query, ['calendar', 'calendar_dates'])
query = _AutoJoiner(dao._orm, dao.session().query(Route), Agency.agency_name == 'FOOBAR').autojoin()
self._check(query, ['agency'])
query = _AutoJoiner(dao._orm, dao.session().query(Route), Stop.stop_name == 'FOOBAR').autojoin()
self._check(query, ['trips', 'stop_times', 'stops'])
query = _AutoJoiner(dao._orm, dao.session().query(Shape), Agency.agency_name == 'FOOBAR').autojoin()
self._check(query, ['trips', 'routes', 'agency'])
query = _AutoJoiner(dao._orm, dao.session().query(FareAttribute), FareRule.contains_id == 'Z1').autojoin()
self._check(query, ['fare_rules'])
def test_autojoin(self):
dao = Dao()
query = _AutoJoiner(dao._orm,
dao.session().query(Agency),
Stop.stop_name == 'FOOBAR').autojoin()
self._check(query, ['routes', 'trips', 'stop_times', 'stops'])
query = _AutoJoiner(dao._orm,
dao.session().query(Agency),
CalendarDate.date == '2016-01-01').autojoin()
self._check(query, ['routes', 'trips', 'calendar', 'calendar_dates'])
query = _AutoJoiner(dao._orm,
dao.session().query(CalendarDate),
Stop.stop_name == 'FOOBAR').autojoin()
self._check(query, ['calendar', 'trips', 'stop_times', 'stops'])
query = _AutoJoiner(dao._orm,
dao.session().query(CalendarDate),
Agency.agency_name == 'FOOBAR').autojoin()
self._check(query, ['calendar', 'trips', 'routes', 'agency'])
query = _AutoJoiner(dao._orm,
dao.session().query(Stop),
CalendarDate.date == '2016-01-01').autojoin()
self._check(query,
['stop_times', 'trips', 'calendar', 'calendar_dates'])
query = _AutoJoiner(dao._orm,
dao.session().query(Stop),
Agency.agency_name == 'FOOBAR').autojoin()
self._check(query, ['stop_times', 'trips', 'routes', 'agency'])
query = _AutoJoiner(dao._orm,
dao.session().query(Agency),
(Stop.stop_name == 'FOOBAR') &
(CalendarDate.date == '2016-01-01')).autojoin()
self._check(query, [
'routes', 'trips', 'calendar', 'calendar_dates', 'stop_times',
'stops'
])
query = _AutoJoiner(dao._orm,
dao.session().query(Trip),
(Route.route_long_name == 'FOOBAR') &
(StopTime.departure_time > 2000) &
(CalendarDate.date == '2016-01-01')).autojoin()
self._check(query,
['calendar', 'calendar_dates', 'routes', 'stop_times'])
query = _AutoJoiner(dao._orm,
dao.session().query(Trip),
Agency.agency_name == 'FOOBAR').autojoin()
self._check(query, ['routes', 'agency'])
query = _AutoJoiner(dao._orm,
dao.session().query(Trip),
Stop.stop_name == 'FOOBAR').autojoin()
self._check(query, ['stop_times', 'stops'])
query = _AutoJoiner(dao._orm,
dao.session().query(Trip),
CalendarDate.date == '2016-01-01').autojoin()
self._check(query, ['calendar', 'calendar_dates'])
query = _AutoJoiner(dao._orm,
dao.session().query(Route),
Agency.agency_name == 'FOOBAR').autojoin()
self._check(query, ['agency'])
query = _AutoJoiner(dao._orm,
dao.session().query(Route),
Stop.stop_name == 'FOOBAR').autojoin()
self._check(query, ['trips', 'stop_times', 'stops'])
query = _AutoJoiner(dao._orm,
dao.session().query(Shape),
Agency.agency_name == 'FOOBAR').autojoin()
self._check(query, ['trips', 'routes', 'agency'])
query = _AutoJoiner(dao._orm,
dao.session().query(FareAttribute),
FareRule.contains_id == 'Z1').autojoin()
self._check(query, ['fare_rules'])
