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'])