#!"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, ctid (caretaker id) and sid (swimmer id)
form = cgi.FieldStorage()
eid = form.getfirst('eid')
if eid is None:
# No HTTP parameter eid: show all events according to event titles
print('Kinds of events in meets.
')
query = '''
SELECT DISTINCT e.Title, COUNT(e.EventId) AS numEvents,
GROUP_CONCAT(CONCAT("'", m.Title, "' at ", v.name, ".\n") SEPARATOR "")
AS events
FROM swim.event AS e INNER JOIN swim.meet AS m USING (meetId)
INNER JOIN swim.venue AS v USING (venueId)
GROUP BY e.Title;
'''
cursor.execute(query)
for (title, numEvents, events) in cursor:
print("Event (" + title + ") in # of meets: " + str(numEvents) + ".
\n")
print("" + events + "
\n")
print('')
cursor.close()
cnx.close()
quit()
if eid is not None: # This will always be satisfied at this point.
# Show swimmer information.
# The query is the same as HW #7.
query = '''
WITH temp AS
((SELECT DISTINCT s.swimmerId AS swimmerId,
CONCAT(c.fName, ' ', c.Lname) AS caretaker,
'(primary)' AS status
FROM swim.Swimmer s INNER JOIN swim.caretaker c ON (s.main_ct_id = c.ct_id))
UNION
(SELECT distinct o.swimmerId AS swimmerId,
CONCAT(c.fName, ' ', c.Lname) AS caretaker,
'(alternate)' AS status
FROM swim.OtherCaretaker o INNER JOIN swim.caretaker c USING (ct_id)))
SELECT s.swimmerId,
CONCAT(s.FName, ' ', s.LName) AS swimmer,
COUNT(temp.caretaker) AS ct_count,
GROUP_CONCAT(CONCAT("", temp.caretaker, ' ', temp.status, "") separator '')
AS caretakers
FROM temp
INNER JOIN swim.swimmer s USING (swimmerId)
INNER JOIN swim.participation p USING (swimmerId)
WHERE p.eventId = %s
GROUP by s.swimmerId, swimmer;
'''
# Show swimmers in the event.
print("Swimmers in event #" + str(eid) + "
")
print ("")
cursor.execute(query,(int(eid),))
for (sid, swimmer, count, caretakers) in cursor:
print(" - " + swimmer + " has " + str(count) + " caretakers:
" +
caretakers + "
")
print ("
")
cursor.close()
cnx.close()
print ('''