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, 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):
"""
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, 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):
"""
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, 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 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 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, 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 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()
def setUp(self):
self.session = db.loadSession()
