#!"c:\python310\python.exe"
# Program url: skipped.
# Suggested solution for HW #6, Fall 2025
import cgi
import pymysql
from dbconfig import get_mysql_param
def connect_db():
params = get_mysql_param()
return pymysql.connect(
host=params['host'],
user=params['user'],
password=params['password'],
database=params['database']
)
def print_response_header():
print("Content-Type: text/html\n")
def get_http_param(param):
if not hasattr(get_http_param, "params"):
get_http_param.params = cgi.FieldStorage()
return get_http_param.params.getfirst(param)
def display_swimmers_html(connection):
with connection.cursor() as cursor:
query = '''
WITH o AS
(SELECT DISTINCT s.SwimmerId,
COUNT(oc.ct_id) AS numOC
FROM swim.Swimmer AS s LEFT JOIN swim.OtherCaretaker AS oc ON (s.SwimmerId = oc.SwimmerId)
GROUP BY s.SwimmerId),
e AS (SELECT DISTINCT s.SwimmerId,
COUNT(p.eventId) AS numEvents
FROM swim.Swimmer AS s LEFT JOIN swim.Participation AS p ON (s.SwimmerId = p.SwimmerId)
GROUP BY s.SwimmerId)
SELECT DISTINCT s.swimmerId,
CONCAT(s.fName, ' ', s.LName) AS swimmer,
CONCAT(c.fName, ' ', c.LName) AS primary_ct,
o.numOC,
e.numEvents
FROM swim.swimmer s LEFT JOIN swim.Caretaker AS c ON (s.Main_CT_Id = c.ct_id)
LEFT JOIN o ON (s.swimmerId = o.swimmerId)
LEFT JOIN e ON (s.swimmerId = e.swimmerId)
'''
cursor.execute(query)
print ('''
''')
print('
All swimmers
')
print('''
Swimmer (id)
Primary caretaker
Number of other caretakers
Number of events
''')
for (sid, swimmer, primary, n_other, n_events) in cursor:
print(f"
')
print('')
def display_swimmer_info_html(connection, sid):
with connection.cursor() as cursor:
query = '''
SELECT e.eventId,
e.title,
IFNULL(p.result,'no comment') AS result,
m.meetId,
m.title
FROM swim.swimmer s LEFT JOIN swim.participation p USING (swimmerId)
LEFT JOIN swim.event e USING (eventId)
LEFT JOIN swim.meet m USING (meetId)
WHERE s.swimmerId = %s
ORDER BY m.meetId ASC, e.eventId ASC;
'''
print ('''
''')
# Show swimmers in the event.
print(f"
Meets and events participated by swimmer (id: {sid}):
")
print ("")
cursor.execute(query,(int(sid),))
prevMid = 0
for (eid, etitle, result, mid, mtitle) in cursor:
if (prevMid != mid):
if (prevMid != 0):
print('')
prevMid = mid
print(f"
Meet #{mid} {mtitle}:")
print('')
print(f"
{etitle}: {result}
")
print ("")
print('')
print('')
def main():
print_response_header()
sid = get_http_param("sid")
cnx = connect_db()
with cnx:
if sid:
display_swimmer_info_html(cnx,sid)
else:
display_swimmers_html(cnx)
if __name__ == "__main__":
main()