Esempio n. 1
0
    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)
Esempio n. 2
0
    def test_complex_queries(self):
        dao = Dao(DAO_URL, sql_logging=SQL_LOG)
        dao.load_gtfs(DUMMY_GTFS)

        # Get the list of departures:
        # 1) from "Porte de Bourgogne"
        # 2) on 4th July
        # 3) between 10:00 and 14:00
        # 4) on route type BUS
        # 5) not the last of trip (only departing)
        porte_bourgogne = dao.stop("BBG")
        july4 = CalendarDate.ymd(2016, 7, 4)
        from_time = gtfstime(10, 00)
        to_time = gtfstime(14, 00)
        departures = dao.stoptimes(
                    fltr=(StopTime.stop == porte_bourgogne) & (StopTime.departure_time >= from_time) & (StopTime.departure_time <= to_time)
                    & (Route.route_type == Route.TYPE_BUS) & (func.date(CalendarDate.date) == july4.date),
                    prefetch_trips=True)

        n = 0
        for dep in departures:
            self.assertTrue(dep.stop == porte_bourgogne)
            self.assertTrue(july4 in dep.trip.calendar.dates)
            self.assertTrue(dep.trip.route.route_type == Route.TYPE_BUS)
            self.assertTrue(dep.departure_time >= from_time and dep.departure_time <= to_time)
            n += 1
        self.assertTrue(n > 10)

        # Plage is a stop that is used only in summer (hence the name!)
        plage = dao.stop("BPG")
        # Get the list of stops used by some route:
        # 1) All-year round
        route_red = dao.route("BR")
        stoplist_all = list(dao.stops(fltr=Trip.route == route_red))
        # 2) Only in january
        from_date = CalendarDate.ymd(2016, 1, 1)
        to_date = CalendarDate.ymd(2016, 1, 31)
        stoplist_jan = list(dao.stops(
                    fltr=(Trip.route == route_red) & (func.date(CalendarDate.date) >= from_date.date) & (func.date(CalendarDate.date) <= to_date.date)))
        # Now, make some tests
        self.assertTrue(len(stoplist_all) > 5)
        self.assertTrue(plage in stoplist_all)
        self.assertFalse(plage in stoplist_jan)
        stoplist = list(stoplist_all)
        stoplist.remove(plage)
        self.assertTrue(set(stoplist) == set(stoplist_jan))

        # Get all routes passing by the set of stops
        routes = dao.routes(fltr=or_(StopTime.stop == stop for stop in stoplist_jan))
        stopset = set()
        for route in routes:
            for trip in route.trips:
                for stoptime in trip.stop_times:
                    stopset.add(stoptime.stop)
        self.assertTrue(set(stoplist_jan).issubset(stopset))
Esempio n. 3
0
def calculate_weekly():
    now = datetime.utcnow()
    last_week = datetime.utcnow() - timedelta(days=7)
    events_by_date = (db.session.query(Event.created_at, Event.event_type,
                                       func.count(Event.event_type)).group_by(
                                           Event.event_type,
                                           func.date(Event.created_at)).filter(
                                               Event.created_at.between(
                                                   last_week, now)).all())

    def filter_events(events, type):
        return [[event.created_at.strftime("%Y-%m-%d"), event[2]]
                for event in events if event.event_type == type]

    return {
        EventTypes.GAME_SINGLEPLAYER_START:
        filter_events(events_by_date, EventTypes.GAME_SINGLEPLAYER_START),
        EventTypes.GAME_SINGLEPLAYER_WIN:
        filter_events(events_by_date, EventTypes.GAME_SINGLEPLAYER_WIN),
        EventTypes.GAME_SINGLEPLAYER_LOSE:
        filter_events(events_by_date, EventTypes.GAME_SINGLEPLAYER_LOSE),
    }
Esempio n. 4
0
    def test_demo(self):
        dao = Dao(DAO_URL, sql_logging=False)
        dao.load_gtfs(DUMMY_GTFS)

        print("List of stops named '...Bordeaux...':")
        stops_bordeaux = list(
            dao.stops(fltr=(Stop.stop_name.ilike('%Bordeaux%'))
                      & (Stop.location_type == Stop.TYPE_STOP)))
        for stop in stops_bordeaux:
            print(stop.stop_name)

        print("List of routes passing by those stops:")
        routes_bordeaux = dao.routes(fltr=or_(StopTime.stop == stop
                                              for stop in stops_bordeaux))
        for route in routes_bordeaux:
            print("%s - %s" % (route.route_short_name, route.route_long_name))

        july4 = CalendarDate.ymd(2016, 7, 4)
        print("All departures from those stops on %s:" % (july4.as_date()))
        departures = list(
            dao.stoptimes(fltr=(or_(StopTime.stop == stop
                                    for stop in stops_bordeaux))
                          & (StopTime.departure_time != None)
                          & (func.date(CalendarDate.date) == july4.date)))
        print("There is %d departures" % (len(departures)))
        for departure in departures:
            print("%30.30s %10.10s %-20.20s > %s" %
                  (departure.stop.stop_name, fmttime(departure.departure_time),
                   departure.trip.route.route_long_name,
                   departure.trip.trip_headsign))

        print("Number of departures and time range per stop on %s:" %
              (july4.as_date()))
        departure_by_stop = defaultdict(list)
        for departure in departures:
            departure_by_stop[departure.stop].append(departure)
        for stop, deps in departure_by_stop.items():
            min_dep = min(d.departure_time for d in deps)
            max_dep = max(d.departure_time for d in deps)
            print("%30.30s %3d departures (from %s to %s)" %
                  (stop.stop_name, len(deps), fmttime(min_dep),
                   fmttime(max_dep)))

        # Compute the average distance and time to next stop by route type
        ntd = [[0, 0, 0.0] for type in range(0, Route.TYPE_FUNICULAR + 1)]
        for departure in departures:
            # The following is guaranteed to succeed as we have departure_time == Null for last stop time in trip
            next_arrival = departure.trip.stop_times[departure.stop_sequence +
                                                     1]
            hop_dist = next_arrival.shape_dist_traveled - departure.shape_dist_traveled
            hop_time = next_arrival.arrival_time - departure.departure_time
            route_type = departure.trip.route.route_type
            ntd[route_type][0] += 1
            ntd[route_type][1] += hop_time
            ntd[route_type][2] += hop_dist
        for route_type in range(0, len(ntd)):
            n, t, d = ntd[route_type]
            if n > 0:
                print(
                    "The average distance to the next stop on those departures for route type %d is %.2f meters"
                    % (route_type, d / n))
                print(
                    "The average time in sec to the next stop on those departures for route type %d is %s"
                    % (route_type, fmttime(t / n)))
Esempio n. 5
0
    def test_gtfs_data(self):
        dao = Dao(DAO_URL, sql_logging=False)
        dao.load_gtfs(DUMMY_GTFS)

        # Check feed
        feed = dao.feed()
        self.assertTrue(feed.feed_id == "")
        self.assertTrue(feed.feed_publisher_name == "Mecatran")
        self.assertTrue(feed.feed_publisher_url == "http://www.mecatran.com/")
        self.assertTrue(feed.feed_contact_email == "*****@*****.**")
        self.assertTrue(feed.feed_lang == "fr")
        self.assertTrue(len(dao.agencies()) == 2)
        self.assertTrue(len(dao.routes()) == 3)
        self.assertTrue(len(feed.agencies) == 2)
        self.assertTrue(len(feed.routes) == 3)

        # Check agencies
        at = dao.agency("AT")
        self.assertTrue(at.agency_name == "Agency Train")
        self.assertTrue(len(at.routes) == 1)
        ab = dao.agency("AB")
        self.assertTrue(ab.agency_name == "Agency Bus")
        self.assertTrue(len(ab.routes) == 2)

        # Check calendars
        week = dao.calendar("WEEK")
        self.assertTrue(len(week.dates) == 253)
        summer = dao.calendar("SUMMER")
        self.assertTrue(len(summer.dates) == 42)
        mon = dao.calendar("MONDAY")
        self.assertTrue(len(mon.dates) == 49)
        sat = dao.calendar("SAT")
        self.assertTrue(len(sat.dates) == 53)
        for date in mon.dates:
            self.assertTrue(date.dow() == 0)
        for date in sat.dates:
            self.assertTrue(date.dow() == 5)
        for date in week.dates:
            self.assertTrue(date.dow() >= 0 and date.dow() <= 4)
        for date in summer.dates:
            self.assertTrue(date >= CalendarDate.ymd(2016, 7, 1) and date <= CalendarDate.ymd(2016, 8, 31))
        empty = dao.calendars(func.date(CalendarDate.date) == datetime.date(2016, 5, 1))
        # OR USE: empty = dao.calendars(CalendarDate.date == "2016-05-01")
        self.assertTrue(len(empty) == 0)
        july4 = CalendarDate.ymd(2016, 7, 4)
        summer_mon = dao.calendars(func.date(CalendarDate.date) == july4.date)
        n = 0
        for cal in summer_mon:
            self.assertTrue(july4 in cal.dates)
            n += 1
        self.assertTrue(n == 3)

        # Check stops
        sbq = dao.stop("BQ")
        self.assertAlmostEqual(sbq.stop_lat, 44.844, places=2)
        self.assertAlmostEqual(sbq.stop_lon, -0.573, places=2)
        self.assertTrue(sbq.stop_name == "Bordeaux Quinconces")
        n = 0
        for stop in dao.stops(Stop.stop_name.like("Gare%")):
            self.assertTrue(stop.stop_name.startswith("Gare"))
            n += 1
        self.assertTrue(n == 7)
        n = 0
        for stop in dao.stops(fltr=dao.in_area(RectangularArea(44.7, -0.6, 44.9, -0.4))):
            self.assertTrue(stop.stop_lat >= 44.7 and stop.stop_lat <= 44.9 and stop.stop_lon >= -0.6 and stop.stop_lon <= -0.4)
            n += 1
        self.assertTrue(n == 16)
        for station in dao.stops(Stop.location_type == Stop.TYPE_STATION):
            self.assertTrue(station.location_type == Stop.TYPE_STATION)
            self.assertTrue(len(station.sub_stops) >= 2)
            for stop in station.sub_stops:
                self.assertTrue(stop.parent_station == station)

        # Check zones
        z_inexistant = dao.zone("ZX")
        self.assertTrue(z_inexistant is None)
        z1 = dao.zone("Z1")
        self.assertEquals(16, len(z1.stops))
        z2 = dao.zone("Z2")
        self.assertEquals(4, len(z2.stops))

        # Check transfers
        transfers = dao.transfers()
        self.assertTrue(len(transfers) == 3)
        transfers = dao.transfers(fltr=(dao.transfer_from_stop().stop_id == 'GBSJB'))
        self.assertTrue(len(transfers) == 1)
        self.assertTrue(transfers[0].from_stop.stop_id == 'GBSJB')

        # Check routes
        tgv = dao.route("TGVBP")
        self.assertTrue(tgv.agency == at)
        self.assertTrue(tgv.route_type == 2)
        r1 = dao.route("BR")
        self.assertTrue(r1.route_short_name == "R1")
        self.assertTrue(r1.route_long_name == "Bus Red")
        n = 0
        for route in dao.routes(Route.route_type == 3):
            self.assertTrue(route.route_type == 3)
            n += 1
        self.assertTrue(n == 2)

        # Check trip for route
        n = 0
        trips = dao.trips(fltr=Route.route_type == Route.TYPE_BUS)
        for trip in trips:
            self.assertTrue(trip.route.route_type == Route.TYPE_BUS)
            n += 1
        self.assertTrue(n > 20)

        # Check trips on date
        trips = dao.trips(fltr=func.date(CalendarDate.date) == july4.date, prefetch_calendars=True)
        n = 0
        for trip in trips:
            self.assertTrue(july4 in trip.calendar.dates)
            n += 1
        self.assertTrue(n > 30)
Esempio n. 6
0
    def test_demo(self):
        dao = Dao(DAO_URL, sql_logging=False)
        dao.load_gtfs(DUMMY_GTFS)

        print("List of stops named '...Bordeaux...':")
        stops_bordeaux = list(
            dao.stops(fltr=(Stop.stop_name.ilike("%Bordeaux%")) & (Stop.location_type == Stop.TYPE_STOP))
        )
        for stop in stops_bordeaux:
            print(stop.stop_name)

        print("List of routes passing by those stops:")
        routes_bordeaux = dao.routes(fltr=or_(StopTime.stop == stop for stop in stops_bordeaux))
        for route in routes_bordeaux:
            print("%s - %s" % (route.route_short_name, route.route_long_name))

        july4 = CalendarDate.ymd(2016, 7, 4)
        print("All departures from those stops on %s:" % (july4.as_date()))
        departures = list(
            dao.stoptimes(
                fltr=(or_(StopTime.stop == stop for stop in stops_bordeaux))
                & (StopTime.departure_time != None)
                & (func.date(CalendarDate.date) == july4.date)
            )
        )
        print("There is %d departures" % (len(departures)))
        for departure in departures:
            print(
                "%30.30s %10.10s %-20.20s > %s"
                % (
                    departure.stop.stop_name,
                    fmttime(departure.departure_time),
                    departure.trip.route.route_long_name,
                    departure.trip.trip_headsign,
                )
            )

        print("Number of departures and time range per stop on %s:" % (july4.as_date()))
        departure_by_stop = defaultdict(list)
        for departure in departures:
            departure_by_stop[departure.stop].append(departure)
        for stop, deps in departure_by_stop.items():
            min_dep = min(d.departure_time for d in deps)
            max_dep = max(d.departure_time for d in deps)
            print(
                "%30.30s %3d departures (from %s to %s)"
                % (stop.stop_name, len(deps), fmttime(min_dep), fmttime(max_dep))
            )

        # Compute the average distance and time to next stop by route type
        ntd = [[0, 0, 0.0] for type in range(0, Route.TYPE_FUNICULAR + 1)]
        for departure in departures:
            # The following is guaranteed to succeed as we have departure_time == Null for last stop time in trip
            next_arrival = departure.trip.stop_times[departure.stop_sequence + 1]
            hop_dist = next_arrival.shape_dist_traveled - departure.shape_dist_traveled
            hop_time = next_arrival.arrival_time - departure.departure_time
            route_type = departure.trip.route.route_type
            ntd[route_type][0] += 1
            ntd[route_type][1] += hop_time
            ntd[route_type][2] += hop_dist
        for route_type in range(0, len(ntd)):
            n, t, d = ntd[route_type]
            if n > 0:
                print(
                    "The average distance to the next stop on those departures for route type %d is %.2f meters"
                    % (route_type, d / n)
                )
                print(
                    "The average time in sec to the next stop on those departures for route type %d is %s"
                    % (route_type, fmttime(t / n))
                )
Esempio n. 7
0
def main():
    if flask.request.method == 'GET':
        return flask.render_template('main.html')
    try:
        prompt = flask.request.form['message'][-MAX_PROMPT_LENGTH:]
        #cfg = botocore.config.Config(retries={'max_attempts': 0}, read_timeout=360, connect_timeout=360, region_name="eu-central-1" )
        if flask.request.form['requestid']:
            submitted = flask.request.form['submitted']
            requestid = flask.request.form['requestid']
            sqs = boto3.resource('sqs',
                                 region_name='eu-central-1',
                                 aws_access_key_id=aws_key,
                                 aws_secret_access_key=aws_secret_key)
            queue = sqs.get_queue_by_name(QueueName='TabaquiQueue')
            for message in queue.receive_messages(
                    MessageAttributeNames=['RequestId']):
                if message.message_attributes is not None:
                    mrequestid = message.message_attributes.get(
                        'RequestId').get('StringValue')
                    print(mrequestid + " " + requestid)
                    if mrequestid == requestid:
                        res = message.body
                        message.delete()
                        return flask.render_template('main.html',
                                                     result=submitted +
                                                     message.body)
            return flask.render_template('main.html',
                                         result=prompt + '... ',
                                         submitted=submitted,
                                         requestid=requestid)
        db.session.query(Journal).filter(
            func.date(Journal.request) <= datetime.date.today() -
            datetime.timedelta(days=1)).delete(synchronize_session='fetch')
        db.session.commit()
        q = db.session.query(Journal.id)  #.filter(...).order_by(...)
        if get_count(q) > MAX_REQUESTS_TO_AWS:
            return flask.render_template('main.html',
                                         result='Please try tomorrow')
        journal = Journal()
        db.session.add(journal)
        db.session.commit()
        db.session.refresh(journal)
        requestid = str(journal.id)
        print('Request id: ' + requestid)
        #boto3.setup_default_session(region_name='us-east-1')
        client = boto3.client(
            'lambda',
            region_name='eu-central-1',
            aws_access_key_id=aws_key,
            aws_secret_access_key=aws_secret_key)  #config = cfg
        prompt = ''.join([
            (s.strip() if s.find(':') > 0 and s.find('"') > 0 else
             names[random.randrange(0, len(names))] + ': "' + s.strip() + ' "')
            + '\n' for s in prompt.split('\n') if s.strip()
        ])
        if prompt.endswith(': "\n'):
            prompt = prompt[:-1]
        else:
            prompt = prompt + names[random.randrange(0, len(names))] + ': "'
        payload = {
            "RequestId": requestid,
            "Prompt": prompt,
            "Temperature": 0.9
        }  #, "NQuotes": 1}
        response = client.invoke(FunctionName='tabaqui_response',
                                 InvocationType='Event',
                                 LogType='Tail',
                                 Payload=json.dumps(payload))
        #dictj = json.loads(response['Payload'].read().decode())
        return flask.render_template('main.html',
                                     result=prompt + "\n" +
                                     str(response['StatusCode']) +
                                     "\nHave to wait for request " + requestid,
                                     submitted=prompt,
                                     requestid=requestid)
    except:
        return flask.render_template('main.html',
                                     result=str(sys.exc_info()))  #[0]