Esempio n. 1
0
    def get(self, country_id):
        """
        Gather specified country from the database with its data
        country_id a non-zero, positive int
        return a json object representing the country
        """
        session = db.loadSession()

        assert type(country_id) == int

        # Make the sql query
        result = session.query(
            # What to select
            # outerjoin defaults to a LEFT outer join, NOT full outer join
            db.Country.id,
            db.Country.name,
            func.array_agg_cust(array([cast(db.Olympics.id, String), cast(db.Olympics.year, String), db.Olympics.season, db.City.name]))
            )\
            .select_from(db.Country)\
            .outerjoin(db.City)\
            .outerjoin(db.Olympics)\
            .filter(
                # What to filter by (where clause)
                db.Country.id==country_id)\
            .group_by(db.Country.id,
            db.Country.name)\
            .first() # Actually executes the query and returns a tuple
        
        session.close()
        
        keys = ('id', 'name', ('olympics-hosted', ('id', 'year', 'season', 'city')))

        country_dict = add_keys(keys, result)

        return jsonify(country_dict)
Esempio n. 2
0
    def get(self):
        """
        Gathers all events from the database with their data
        return a json object representing the events
        """
        
        session = db.loadSession()

        # Make the sql query
        result = session.query(
            # What to select
            # distinct because of multiple medals per event
            distinct(db.Event.id),
            db.Event.name,
            db.Sport.name,
            func.array_agg_cust(distinct(array([cast(db.Olympics.id, String), cast(db.Olympics.year, String), db.Olympics.season])))
            )\
            .select_from(db.Event)\
            .join(db.Sport)\
            .join(db.Medal)\
            .join(db.Olympics)\
            .group_by(db.Event.id,
            db.Event.name,
            db.Sport.name)\
            .all() # Actually executes the query and returns a list of tuples
        
        session.close()
        
        keys = ('id', 'name', 'sport', ('olympics', ('id', 'year', 'season')))
        
        all_events_dict = list_of_dict_to_dict_of_dict(add_keys(keys, row) for row in result)
        
        return jsonify(all_events_dict)
Esempio n. 3
0
    def get(self):
        """
        Gathers all events from the database with their data
        return a json object representing the events
        """

        session = db.loadSession()

        # Make the sql query
        result = session.query(
            # What to select
            # distinct because of multiple medals per event
            distinct(db.Event.id),
            db.Event.name,
            db.Sport.name,
            func.array_agg_cust(distinct(array([cast(db.Olympics.id, String), cast(db.Olympics.year, String), db.Olympics.season])))
            )\
            .select_from(db.Event)\
            .join(db.Sport)\
            .join(db.Medal)\
            .join(db.Olympics)\
            .group_by(db.Event.id,
            db.Event.name,
            db.Sport.name)\
            .all() # Actually executes the query and returns a list of tuples

        session.close()

        keys = ('id', 'name', 'sport', ('olympics', ('id', 'year', 'season')))

        all_events_dict = list_of_dict_to_dict_of_dict(
            add_keys(keys, row) for row in result)

        return jsonify(all_events_dict)
Esempio n. 4
0
    def get(self):
        """
        Gathers all countries from the database with their data
        return a json object representing the countries
        """
        
        session = db.loadSession()

        # Make the sql query
        result = session.query(
            # What to select
            # outerjoin defaults to a LEFT outer join, NOT full outer join
            db.Country.id,
            db.Country.name,
            func.array_agg_cust(array([cast(db.Olympics.id, String), cast(db.Olympics.year, String), db.Olympics.season, db.City.name]))
            )\
            .select_from(db.Country)\
            .outerjoin(db.City)\
            .outerjoin(db.Olympics)\
            .group_by(db.Country.id,
            db.Country.name)\
            .all() # Actually executes the query and returns a list of tuples
        
        session.close()
        
        keys = ('id', 'name', ('olympics-hosted', ('id', 'year', 'season', 'city')))
        
        all_countries_dict = list_of_dict_to_dict_of_dict(add_keys(keys, row) for row in result)
        
        return jsonify(all_countries_dict)
Esempio n. 5
0
    def get(self):
        """
        Gathers all countries from the database with their data
        return a json object representing the countries
        """

        session = db.loadSession()

        # Make the sql query
        result = session.query(
            # What to select
            # outerjoin defaults to a LEFT outer join, NOT full outer join
            db.Country.id,
            db.Country.name,
            func.array_agg_cust(array([cast(db.Olympics.id, String), cast(db.Olympics.year, String), db.Olympics.season, db.City.name]))
            )\
            .select_from(db.Country)\
            .outerjoin(db.City)\
            .outerjoin(db.Olympics)\
            .group_by(db.Country.id,
            db.Country.name)\
            .all() # Actually executes the query and returns a list of tuples

        session.close()

        keys = ('id', 'name', ('olympics-hosted', ('id', 'year', 'season',
                                                   'city')))

        all_countries_dict = list_of_dict_to_dict_of_dict(
            add_keys(keys, row) for row in result)

        return jsonify(all_countries_dict)
Esempio n. 6
0
    def get(self, olympic_id):
        """
        Gather specified olympics from the database with its data
        olympic_id a non-zero, positive int
        return a json object representing the olympic games
        """
        session = db.loadSession()

        assert type(olympic_id) == int

        # Make the sql query
        result = session.query(
            # What to select
            # distinct (because of multiple medals per event) has to go on the first element though we want distinct event ids
            distinct(db.Olympics.id),
            db.Olympics.year,
            db.Olympics.season,
            db.City.name,
            db.Country.name,
            # array_agg_cust so that each now will be an INDIVIDUAL olympic games
            func.array_agg_cust(distinct(array([cast(db.Event.id, String), db.Event.name, db.Sport.name])))
            )\
            .select_from(db.Olympics)\
            .join(db.City)\
            .join(db.Country)\
            .join(db.Medal,             db.Medal.olympic_id==db.Olympics.id)\
            .join(db.Event)\
            .join(db.Sport)\
            .filter(
                # What to filter by (where clause)
                db.Olympics.id==olympic_id)\
            .group_by(db.Olympics.id,
            db.Olympics.year,
            db.Olympics.season,
            db.City.name,
            db.Country.name)\
            .first() # Actually executes the query and returns a tuple

        session.close()

        keys = ('id', 'year', 'season', 'city', 'country',
                ('events', ('id', 'name', 'sport')))

        olympics_dict = add_keys(keys, result)

        return jsonify(olympics_dict)
Esempio n. 7
0
    def get(self, olympic_id):
        """
        Gather specified olympics from the database with its data
        olympic_id a non-zero, positive int
        return a json object representing the olympic games
        """
        session = db.loadSession()

        assert type(olympic_id) == int

        # Make the sql query
        result = session.query(
            # What to select
            # distinct (because of multiple medals per event) has to go on the first element though we want distinct event ids
            distinct(db.Olympics.id),
            db.Olympics.year,
            db.Olympics.season,
            db.City.name,
            db.Country.name,
            # array_agg_cust so that each now will be an INDIVIDUAL olympic games
            func.array_agg_cust(distinct(array([cast(db.Event.id, String), db.Event.name, db.Sport.name])))
            )\
            .select_from(db.Olympics)\
            .join(db.City)\
            .join(db.Country)\
            .join(db.Medal,             db.Medal.olympic_id==db.Olympics.id)\
            .join(db.Event)\
            .join(db.Sport)\
            .filter(
                # What to filter by (where clause)
                db.Olympics.id==olympic_id)\
            .group_by(db.Olympics.id,
            db.Olympics.year,
            db.Olympics.season,
            db.City.name,
            db.Country.name)\
            .first() # Actually executes the query and returns a tuple
        
        session.close()
        
        keys = ('id', 'year', 'season', 'city', 'country', ('events', ('id', 'name', 'sport')))

        olympics_dict = add_keys(keys, result)

        return jsonify(olympics_dict)
Esempio n. 8
0
    def get(self):
        """
        Gathers all olympics from the database with their data
        return a json object representing the olympics
        """

        session = db.loadSession()

        # Make the sql query
        result = session.query(
            # What to select
            # distinct (because of multiple medals) has to go on the first element though we want distinct event ids
            distinct(db.Olympics.id),
            db.Olympics.year,
            db.Olympics.season,
            db.City.name,
            db.Country.name,
            # array_agg_cust so that each now will be an INDIVIDUAL olympic games
            func.array_agg_cust(distinct(array([cast(db.Event.id, String), db.Event.name, db.Sport.name])))
            )\
            .select_from(db.Olympics)\
            .join(db.City)\
            .join(db.Country)\
            .join(db.Medal,             db.Medal.olympic_id==db.Olympics.id)\
            .join(db.Event)\
            .join(db.Sport)\
            .group_by(db.Olympics.id,
            db.Olympics.year,
            db.Olympics.season,
            db.City.name,
            db.Country.name)\
            .all() # Actually executes the query and returns a list of tuples

        session.close()

        keys = ('id', 'year', 'season', 'city', 'country',
                ('events', ('id', 'name', 'sport')))

        all_olympics_dict = list_of_dict_to_dict_of_dict(
            add_keys(keys, row) for row in result)

        return jsonify(all_olympics_dict)
Esempio n. 9
0
    def get(self, athlete_id):
        """
        Gather specified athlete from the database with its data
        athlete_id a non-zero, positive int
        return a json object representing the athlete
        """
        session = db.loadSession()

        # Make the sql query
        result = session.query(
            # What to select
            db.Athlete.id,
            db.Athlete.first_name,
            db.Athlete.last_name,
            db.Athlete.gender,
            func.array_agg_cust(array([cast(db.Medal.id, String), db.Medal.rank, db.Event.name, db.Sport.name, db.Olympics.season, cast(db.Olympics.year, String), db.Country.name]))
            )\
            .select_from(db.Athlete)\
            .join(db.Medal)\
            .join(db.Event)\
            .join(db.Sport)\
            .join(db.Olympics)\
            .join(db.Country)\
            .filter(
                # What to filter by (where clause)
                db.Athlete.id==athlete_id)\
            .group_by(db.Athlete.id,
            db.Athlete.first_name,
            db.Athlete.last_name,
            db.Athlete.gender)\
            .first() # Actually executes the query and returns a tuple

        session.close()

        keys = ('id', 'first', 'last', 'gender',
                ('medals', ('id', 'rank', 'event', 'sport', 'season', 'year',
                            'repr')))

        athlete_dict = add_keys(keys, result)

        return jsonify(athlete_dict)
Esempio n. 10
0
    def get(self):
        """
        Gathers all olympics from the database with their data
        return a json object representing the olympics
        """

        session = db.loadSession()

        # Make the sql query
        result = session.query(
            # What to select
            # distinct (because of multiple medals) has to go on the first element though we want distinct event ids
            distinct(db.Olympics.id),
            db.Olympics.year,
            db.Olympics.season,
            db.City.name,
            db.Country.name,
            # array_agg_cust so that each now will be an INDIVIDUAL olympic games
            func.array_agg_cust(distinct(array([cast(db.Event.id, String), db.Event.name, db.Sport.name])))
            )\
            .select_from(db.Olympics)\
            .join(db.City)\
            .join(db.Country)\
            .join(db.Medal,             db.Medal.olympic_id==db.Olympics.id)\
            .join(db.Event)\
            .join(db.Sport)\
            .group_by(db.Olympics.id,
            db.Olympics.year,
            db.Olympics.season,
            db.City.name,
            db.Country.name)\
            .all() # Actually executes the query and returns a list of tuples
        
        session.close()
        
        keys = ('id', 'year', 'season', 'city', 'country', ('events', ('id', 'name', 'sport')))
        
        all_olympics_dict = list_of_dict_to_dict_of_dict(add_keys(keys, row) for row in result)
        
        return jsonify(all_olympics_dict)
Esempio n. 11
0
    def get(self, athlete_id):
        """
        Gather specified athlete from the database with its data
        athlete_id a non-zero, positive int
        return a json object representing the athlete
        """
        session = db.loadSession()

        
        # Make the sql query
        result = session.query(
            # What to select
            db.Athlete.id,
            db.Athlete.first_name,
            db.Athlete.last_name,
            db.Athlete.gender,
            func.array_agg_cust(array([cast(db.Medal.id, String), db.Medal.rank, db.Event.name, db.Sport.name, db.Olympics.season, cast(db.Olympics.year, String), db.Country.name]))
            )\
            .select_from(db.Athlete)\
            .join(db.Medal)\
            .join(db.Event)\
            .join(db.Sport)\
            .join(db.Olympics)\
            .join(db.Country)\
            .filter(
                # What to filter by (where clause)
                db.Athlete.id==athlete_id)\
            .group_by(db.Athlete.id,
            db.Athlete.first_name,
            db.Athlete.last_name,
            db.Athlete.gender)\
            .first() # Actually executes the query and returns a tuple
        
        session.close()
        
        keys = ('id', 'first', 'last', 'gender', ('medals', ('id', 'rank', 'event', 'sport', 'season', 'year', 'repr')))
        
        athlete_dict = add_keys(keys, result)

        return jsonify(athlete_dict)
Esempio n. 12
0
    def get(self):
        """
        Gathers all athletes from the database with their data
        return a json object representing the athletes
        """

        session = db.loadSession()

        # Make the sql query
        result = session.query(
            # What to select
            db.Athlete.id,
            db.Athlete.first_name,
            db.Athlete.last_name,
            db.Athlete.gender,
            func.array_agg_cust(array([cast(db.Medal.id, String), db.Medal.rank, db.Event.name, db.Sport.name, db.Olympics.season, cast(db.Olympics.year, String), db.Country.name]))
            )\
            .select_from(db.Athlete)\
            .join(db.Medal)\
            .join(db.Event)\
            .join(db.Sport)\
            .join(db.Olympics)\
            .join(db.Country)\
            .group_by(db.Athlete.id,
            db.Athlete.first_name,
            db.Athlete.last_name,
            db.Athlete.gender)\
            .all() # Actually executes the query and returns a list of tuples

        session.close()

        keys = ('id', 'first', 'last', 'gender',
                ('medals', ('id', 'rank', 'event', 'sport', 'season', 'year',
                            'repr')))

        all_athletes_dict = list_of_dict_to_dict_of_dict(
            add_keys(keys, row) for row in result)

        return jsonify(all_athletes_dict)
Esempio n. 13
0
    def get(self, event_id):
        """
        Gather specified event from the database with its data
        event_id a non-zero, positive int
        return a json object representing the event
        """
        session = db.loadSession()

        assert type(event_id) == int

        # Make the sql query
        result = session.query(
            # What to select
            # distinct because of multiple medals per event
            distinct(db.Event.id),
            db.Event.name,
            db.Sport.name,
            func.array_agg_cust(distinct(array([cast(db.Olympics.id, String), cast(db.Olympics.year, String), db.Olympics.season])))
            )\
            .select_from(db.Event)\
            .join(db.Sport)\
            .join(db.Medal)\
            .join(db.Olympics)\
            .filter(
                # What to filter by (where clause)
                db.Event.id==event_id)\
            .group_by(db.Event.id,
            db.Event.name,
            db.Sport.name)\
            .first() # Actually executes the query and returns a tuple
        
        session.close()
        
        keys = ('id', 'name', 'sport', ('olympics', ('id', 'year', 'season')))
        
        event_dict = add_keys(keys, result)

        return jsonify(event_dict)
Esempio n. 14
0
    def get(self, event_id):
        """
        Gather specified event from the database with its data
        event_id a non-zero, positive int
        return a json object representing the event
        """
        session = db.loadSession()

        assert type(event_id) == int

        # Make the sql query
        result = session.query(
            # What to select
            # distinct because of multiple medals per event
            distinct(db.Event.id),
            db.Event.name,
            db.Sport.name,
            func.array_agg_cust(distinct(array([cast(db.Olympics.id, String), cast(db.Olympics.year, String), db.Olympics.season])))
            )\
            .select_from(db.Event)\
            .join(db.Sport)\
            .join(db.Medal)\
            .join(db.Olympics)\
            .filter(
                # What to filter by (where clause)
                db.Event.id==event_id)\
            .group_by(db.Event.id,
            db.Event.name,
            db.Sport.name)\
            .first() # Actually executes the query and returns a tuple

        session.close()

        keys = ('id', 'name', 'sport', ('olympics', ('id', 'year', 'season')))

        event_dict = add_keys(keys, result)

        return jsonify(event_dict)
Esempio n. 15
0
    def get(self):
        """
        Gathers all athletes from the database with their data
        return a json object representing the athletes
        """
        
        session = db.loadSession()

        # Make the sql query
        result = session.query(
            # What to select
            db.Athlete.id,
            db.Athlete.first_name,
            db.Athlete.last_name,
            db.Athlete.gender,
            func.array_agg_cust(array([cast(db.Medal.id, String), db.Medal.rank, db.Event.name, db.Sport.name, db.Olympics.season, cast(db.Olympics.year, String), db.Country.name]))
            )\
            .select_from(db.Athlete)\
            .join(db.Medal)\
            .join(db.Event)\
            .join(db.Sport)\
            .join(db.Olympics)\
            .join(db.Country)\
            .group_by(db.Athlete.id,
            db.Athlete.first_name,
            db.Athlete.last_name,
            db.Athlete.gender)\
            .all() # Actually executes the query and returns a list of tuples
        
        session.close()
        
        keys = ('id', 'first', 'last', 'gender', ('medals', ('id', 'rank', 'event', 'sport', 'season', 'year', 'repr')))
        
        all_athletes_dict = list_of_dict_to_dict_of_dict(add_keys(keys, row) for row in result)
        
        return jsonify(all_athletes_dict)
Esempio n. 16
0
    def get(self, country_id):
        """
        Gather specified country from the database with its data
        country_id a non-zero, positive int
        return a json object representing the country
        """
        session = db.loadSession()

        assert type(country_id) == int

        # Make the sql query
        result = session.query(
            # What to select
            # outerjoin defaults to a LEFT outer join, NOT full outer join
            db.Country.id,
            db.Country.name,
            func.array_agg_cust(array([cast(db.Olympics.id, String), cast(db.Olympics.year, String), db.Olympics.season, db.City.name]))
            )\
            .select_from(db.Country)\
            .outerjoin(db.City)\
            .outerjoin(db.Olympics)\
            .filter(
                # What to filter by (where clause)
                db.Country.id==country_id)\
            .group_by(db.Country.id,
            db.Country.name)\
            .first() # Actually executes the query and returns a tuple

        session.close()

        keys = ('id', 'name', ('olympics-hosted', ('id', 'year', 'season',
                                                   'city')))

        country_dict = add_keys(keys, result)

        return jsonify(country_dict)