#!"c:\python310\python.exe"
from dbconfig import *
import pymysql
import cgi
import cgitb
cgitb.enable()
# Establish a cursor for MySQL connection.
db = get_mysql_param()
cnx = pymysql.connect(user=db['user'],
password=db['password'],
host=db['host'],
database=db['database'])
cursor = cnx.cursor()
# Create HTTP response header
print("Content-Type: text/html;charset=utf-8")
print()
# Create a primitive HTML starter
print ('''
''')
# Get HTTP parameter, sid (swimmer id)
form = cgi.FieldStorage()
sid = form.getfirst('sid')
if sid is None:
# No HTTP parameter: show all swimmers
print('
All swimmers
')
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('''
Swimmer (id)
Primary caretaker
Number of other caretakers
Number of events
''')
for (sid, swimmer, primary, n_other, n_events) in cursor:
print(f"
')
print('')
cursor.close()
cnx.close()
quit()
else:
# There is a sid.
# Show swimmer information.
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;
'''
# 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"