#!"C:\Python310\python.exe"
# Put your dcm URL here.
from dbconfig import *
import warnings
warnings.filterwarnings('ignore')
import pymysql
import cgi
import cgitb
cgitb.enable()
# A suggested solution for Joint DB course Fall 2024 HW #6.
# There are many possible approaches to an acceptable solution.
# Establish a cursor for MySQL connection.
db = get_mysql_param()
cnx = pymysql.connect(user=db['user'],
password=db['password'],
host=db['host'],
database="swim")
cursor = cnx.cursor()
# Create HTTP response header
print("Content-Type: text/html")
print()
# Create a primitive HTML starter
print ('''
''')
# Get HTTP parameter, ctid (caretaker id) and sid (swimmer id)
form = cgi.FieldStorage()
mid = form.getfirst('mid')
eid = form.getfirst('eid')
if mid is None and eid is None:
# No HTTP parameter mid submitted: show all meets
print('Information about meets
')
query = '''
SELECT DISTINCT m.meetId, m.title, v.name AS venue,
m.date AS meetDate,
COUNT(e.eventId) AS numEvents,
GROUP_CONCAT(" ", e.Title, "\n" SEPARATOR "") AS events
FROM meet AS m LEFT JOIN venue AS v
ON (m.venueId = v.venueId)
INNER JOIN event AS e
ON (e.meetId = m.meetId)
GROUP BY m.meetId, m.title, venue, meetDate;
'''
cursor.execute(query)
print('')
for (mid, mTitle, venue, mDate, numEvents, events) in cursor:
print(" - " + mTitle + ": "
+ str(numEvents) + " events on " + str(mDate) + " at " + venue
+ "\n" + "
" + events + "
\n ")
print('
')
print('')
cursor.close()
cnx.close()
quit()
if mid is not None and eid is None:
# Show meet information.
# The query is the same as HW #7.
query = '''
WITH t AS
(SELECT DISTINCT DISTINCT m.meetId, m.title, l.levelId, l.level AS levelName,
COUNT(e.eventId) AS numEvents ,
GROUP_CONCAT(CONCAT("id: ", e.eventId, "; title: ", e.title , "") SEPARATOR "\n") AS eventDetails
FROM meet AS m LEFT JOIN event AS e
ON (e.meetId = m.meetId)
LEFT JOIN level AS l
ON (e.levelId = l.levelId)
WHERE m.meetId = %s
GROUP BY m.meetId, m.title, levelName
ORDER BY l.levelId ASC)
SELECT DISTINCT t.meetId, t.title,
GROUP_CONCAT(CONCAT("level ", t.levelId, ": ", t.levelName , "; ", t.numEvents,
" events\n\n", t.eventDetails, "\n
\n")
SEPARATOR "\n") AS levelDetails
FROM t
GROUP BY t.meetId, t.title;
'''
# Show more information in the meet
cursor.execute(query,(int(mid),))
(mid, title, meetDetails) = cursor.fetchone()
print("More information on meet #" + str(mid) + ": " + title + "
")
print ("")
print(meetDetails)
print ("
")
else: # eid is not null and assume that mid is None
query = '''
SELECT e.title,
GROUP_CONCAT(CONCAT(" ", s.fname, " ", s.lname, ": (id: ", s.swimmerId, ")\n") SEPARATOR "")
FROM event AS e LEFT JOIN participation AS p USING (eventId)
LEFT JOIN swimmer AS s USING (swimmerId)
WHERE e.eventId = %s;
'''
# Show more information in the meet
cursor.execute(query,(int(eid),))
(title, swimmers) = cursor.fetchone()
print("Swimmers in the event ", title, "(id: ", str(eid), ")
\n")
print ("")
print(swimmers)
print ("
")
cursor.close()
cnx.close()
print ('''