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)
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)
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)
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)
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)
def get(self, rank): """ Gathers all medals from the database with their data return a json object representing the medals """ rank = rank.lower() rank = rank.capitalize() if not (rank == 'Gold' or rank == 'Silver' or rank == 'Bronze'): abort(404, message= "rank must be one of the following: Gold, Silver, or Bronze". format(rank)) session = db.loadSession() # Make the sql query result = session.query( # What to select db.Medal.id, db.Medal.rank, db.Athlete.first_name + ' ' + db.Athlete.last_name, db.Event.name, db.Sport.name, db.Olympics.year, db.City.name, db.Country.name )\ .select_from(db.Medal)\ .join(db.Athlete)\ .join(db.Event)\ .join(db.Sport)\ .join(db.Olympics)\ .join(db.City)\ .join(db.Country)\ .filter( # What to filter by (where clause) db.Medal.rank==rank)\ .all() # Actually executes the query and returns a list of tuples session.close() keys = ('id', 'rank', 'athlete', 'event', 'sport', 'year', 'city', 'country') all_medals_dict = list_of_dict_to_dict_of_dict( add_keys(keys, row) for row in result) return jsonify(all_medals_dict)
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)
def get(self, rank): """ Gathers all medals from the database with their data return a json object representing the medals """ rank=rank.lower() rank=rank.capitalize() if not(rank=='Gold' or rank=='Silver' or rank=='Bronze'): abort(404, message="rank must be one of the following: Gold, Silver, or Bronze".format(rank)) session = db.loadSession() # Make the sql query result = session.query( # What to select db.Medal.id, db.Medal.rank, db.Athlete.first_name + ' ' + db.Athlete.last_name, db.Event.name, db.Sport.name, db.Olympics.year, db.City.name, db.Country.name )\ .select_from(db.Medal)\ .join(db.Athlete)\ .join(db.Event)\ .join(db.Sport)\ .join(db.Olympics)\ .join(db.City)\ .join(db.Country)\ .filter( # What to filter by (where clause) db.Medal.rank==rank)\ .all() # Actually executes the query and returns a list of tuples session.close() keys = ('id', 'rank', 'athlete', 'event', 'sport', 'year', 'city', 'country') all_medals_dict = list_of_dict_to_dict_of_dict(add_keys(keys, row) for row in result) return jsonify(all_medals_dict)
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)
def get(self, medal_id): """ Gather specified medal from the database with its data medal_id a non-zero, positive int return a json object representing the medal """ session = db.loadSession() assert type(medal_id) == int # Make the sql query result = session.query( # What to select db.Medal.id, db.Medal.rank, db.Athlete.first_name + ' ' + db.Athlete.last_name, db.Event.name, db.Sport.name, db.Olympics.year, db.City.name, db.Country.name )\ .select_from(db.Medal)\ .join(db.Athlete)\ .join(db.Event)\ .join(db.Sport)\ .join(db.Olympics)\ .join(db.City)\ .join(db.Country)\ .filter( # What to filter by (where clause) db.Medal.id==medal_id)\ .first() # Actually executes the query and returns a tuple session.close() keys = ('id', 'rank', 'athlete', 'event', 'sport', 'year', 'city', 'country') medal_dict = add_keys(keys, result) return jsonify(medal_dict)
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)
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)
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)
def get(self): """ Gathers all medals from the database with their data return a json object representing the medals """ session = db.loadSession() # Make the sql query result = session.query( # What to select db.Medal.id, db.Medal.rank, db.Athlete.first_name + ' ' + db.Athlete.last_name, db.Event.name, db.Sport.name, db.Olympics.year, db.City.name, db.Country.name )\ .select_from(db.Medal)\ .join(db.Athlete)\ .join(db.Event)\ .join(db.Sport)\ .join(db.Olympics)\ .join(db.City)\ .join(db.Country)\ .all() # Actually executes the query and returns a list of tuples session.close() keys = ('id', 'rank', 'athlete', 'event', 'sport', 'year', 'city', 'country') all_medals_dict = list_of_dict_to_dict_of_dict( add_keys(keys, row) for row in result) return jsonify(all_medals_dict)
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)
def __init__(self): self.app = Flask(__name__, static_url_path="/static", static_folder="./static") # add a logging handler to the app self.app.logger = TZLogger(__name__, LOG_FILE).getLogger() # collect all legit apps under "apps" directory and create a dictionary having the endpoint (eg. /devel/xxx) as the key. self.endpoint_app_dict, self.all_apps_endpoints = self.__find_app_modules( ) self.app.logger.info("endpoints: " + str(self.endpoint_app_dict)) self.app.wsgi_app = DispatcherMiddleware(self.app.wsgi_app, self.endpoint_app_dict) self.auth = Auth(self.app) self.app.config[ "SQLALCHEMY_DATABASE_URI"] = "mysql+pymysql://{}:{}@{}/{}".format( Auth.MYSQL_USERNAME, Auth.MYSQL_PASSWORD, Auth.MYSQL_IP, Auth.MYSQL_DB) self.app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False self.db = SQLAlchemy(self.app) self.db.init_app(self.app) self.app.config.from_object(__name__) self.app.secret_key = Auth.SECRET_KEY self.app.debug = True # SESSION_TYPE='filesystem' # SESSION_PERMANENT=False # Session(self.app) #supports for Server-side Session. Optional self.dbsession = loadSession( self.app.config["SQLALCHEMY_DATABASE_URI"])
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)
def get(self): """ Gathers all medals from the database with their data return a json object representing the medals """ session = db.loadSession() # Make the sql query result = session.query( # What to select db.Medal.id, db.Medal.rank, db.Athlete.first_name + ' ' + db.Athlete.last_name, db.Event.name, db.Sport.name, db.Olympics.year, db.City.name, db.Country.name )\ .select_from(db.Medal)\ .join(db.Athlete)\ .join(db.Event)\ .join(db.Sport)\ .join(db.Olympics)\ .join(db.City)\ .join(db.Country)\ .all() # Actually executes the query and returns a list of tuples session.close() keys = ('id', 'rank', 'athlete', 'event', 'sport', 'year', 'city', 'country') all_medals_dict = list_of_dict_to_dict_of_dict(add_keys(keys, row) for row in result) return jsonify(all_medals_dict)
def setUp(self): self.session = db.loadSession()