class Schema():
pg = None
schema = 'STUDENTCOURSEDB'
sqlquery = ''
s = Students()
ss = StudentStatus()
c = Courses()
sc = ScheduledCourses()
m = Meeting()
e = Enrolled()
rooms = Rooms()
def __init__(self):
self.pg = PgInterface()
self.__createschema__()
self.s.createtable(self.schema)
self.ss.createtable(self.schema)
self.c.createtable(self.schema)
self.sc.createtable(self.schema)
self.m.createtable(self.schema)
self.e.createtable(self.schema)
self.rooms.createtable(self.schema)
def __createschema__(self):
cur = self.pg.getcursor()
self.sqlquery = ('CREATE SCHEMA IF NOT EXISTS ' + self.schema + ';')
print(self.sqlquery)
cur.execute(self.sqlquery)
self.pg.commit()
def problem3b():
gpalist = []
pg = PgInterface()
cur = pg.getcursor()
for i in range(1, 21):
gpa = getUniqueStudentsUnit(cur, i)
gpalist.append(gpa)
print(str(gpalist))
def __init__(self):
self.pg = PgInterface()
self.__createschema__()
self.s.createtable(self.schema)
self.ss.createtable(self.schema)
self.c.createtable(self.schema)
self.sc.createtable(self.schema)
self.m.createtable(self.schema)
self.e.createtable(self.schema)
self.rooms.createtable(self.schema)
def problem3a():
studentsunits = []
unitratio = []
pg = PgInterface()
cur = pg.getcursor()
tstudents = getUniqueStudents(cur)
for i in range(1,21):
numwithunits = getUniqueStudentsUnit(cur, i)
studentsunits.append(numwithunits)
unitratio = (str(round((numwithunits/tstudents)*100, 3)) + "%")
print("Students with " + str(i) + " units: " + str(numwithunits) + ", " + str(unitratio))
def prob3g():
pg = PgInterface()
q_top5 = """SELECT ss2.major, count(distinct ss1.sid) c
FROM studentcoursedb.student_status ss1, studentcoursedb.student_status ss2
WHERE
ss1.sid = ss2.sid
AND ss1.major LIKE 'ABC%'
AND ss2.term < ss1.term
AND ss1.major <> ss2.major
AND ss2.major NOT LIKE 'ABC%'
group by ss2.major
order by c desc
limit 5;"""
q_gradABC = """SELECT count(distinct ss.sid)
FROM
(SELECT
ss.sid sid,
ss.term term,
ss.major
FROM
studentcoursedb.student_status ss INNER JOIN
(SELECT
sid,
max(term) maxterm
FROM studentcoursedb.student_status
GROUP BY sid) mt
ON mt.maxterm = ss.term AND mt.sid = ss.sid
WHERE ss.major LIKE 'ABC%') grads, studentcoursedb.student_status ss
WHERE grads.sid = ss.sid AND ss.term <> grads.term AND ss.major NOT LIKE 'ABC%';"""
print('Percent of total students to transfer to ABC and graduate as ABC:')
cursor = pg.getcursor()
cursor.execute(q_gradABC)
transf = cursor.fetchone()[0]
cursor.execute(
"select count(distinct sid) from studentcoursedb.student_status where major like 'ABC%';"
)
all = cursor.fetchone()[0]
print('%d/%d*100 = %0.2f%%\n' % (transf, all, transf / all * 100))
print(
'Top 5 majors to transfer into ABC and percent transferred from that major:'
)
cursor = pg.getcursor()
cursor.execute(q_top5)
for row in cursor:
print('%s: %0.2f%%' % (row[0], row[1] / transf * 100))
print('')
from data_importer.pginterface import PgInterface
pg = PgInterface()
cur = pg.getcursor()
cur.execute('select count(*) from freight_data')
data = cur.fetchone()
print(data)
cur.execute('create table test_table (id int);')
pg.commit()
def prob3f():
pg = PgInterface()
q = """SELECT
ss.major,
avg(grade) avgGrade
FROM (
SELECT
sid,
cid,
sec,
term,
(CASE WHEN grade = 'A+'
THEN 4.0
WHEN grade = 'A'
THEN 4.0
WHEN grade = 'A-'
THEN 3.7
WHEN grade = 'B+'
THEN 3.3
WHEN grade = 'B'
THEN 3.0
WHEN grade = 'B-'
THEN 2.7
WHEN grade = 'C+'
THEN 2.3
WHEN grade = 'C'
THEN 2.0
WHEN grade = 'C-'
THEN 1.7
WHEN grade = 'D+'
THEN 1.3
WHEN grade = 'D'
THEN 1.0
WHEN grade = 'D-'
THEN 0.7
WHEN grade = 'F+'
THEN 0
WHEN grade = 'F'
THEN 0
WHEN grade = 'F-'
THEN 0
END) grade
FROM studentcoursedb.enrolled
WHERE grade IN ('A+', 'A', 'A-', 'B+', 'B', 'B-', 'C+', 'C', 'C-', 'D+', 'D', 'D-', 'F+', 'F', 'F-')
) grades
INNER JOIN (
SELECT
cid,
term,
sec
FROM studentcoursedb.scheduled_courses
WHERE subj = 'ABC'
) sc
ON grades.term = sc.term AND grades.cid = sc.cid AND grades.sec = sc.sec
INNER JOIN (
SELECT
sid,
term,
major
FROM studentcoursedb.student_status
) ss
ON grades.sid = ss.sid AND grades.term = ss.term
GROUP BY major
order by avgGrade;"""
cursor = pg.getcursor()
cursor.execute(q)
all = cursor.fetchall()
min = -1
max = 5
print('Worse performing majors:')
for row in all:
if min == -1:
min = row[1]
elif min != row[1]:
break
print('%s: %0.4f' % row)
print('Best performing majors:')
for row in reversed(all):
if max == 5:
max = row[1]
elif max != row[1]:
break
print('%s: %0.4f' % row)
def prob3d():
pg = PgInterface()
q = """SELECT
subj,
crse,
professor,
max(avgGrade) aGrade
FROM
(SELECT
subj,
crse,
professor,
avg(grade) avgGrade
FROM (
SELECT
cid,
sec,
term,
(CASE WHEN grade = 'A+'
THEN 4.0
WHEN grade = 'A'
THEN 4.0
WHEN grade = 'A-'
THEN 3.7
WHEN grade = 'B+'
THEN 3.3
WHEN grade = 'B'
THEN 3.0
WHEN grade = 'B-'
THEN 2.7
WHEN grade = 'C+'
THEN 2.3
WHEN grade = 'C'
THEN 2.0
WHEN grade = 'C-'
THEN 1.7
WHEN grade = 'D+'
THEN 1.3
WHEN grade = 'D'
THEN 1.0
WHEN grade = 'D-'
THEN 0.7
WHEN grade = 'F+'
THEN 0
WHEN grade = 'F'
THEN 0
WHEN grade = 'F-'
THEN 0
END) grade
FROM studentcoursedb.enrolled
WHERE grade IN ('A+', 'A', 'A-', 'B+', 'B', 'B-', 'C+', 'C', 'C-', 'D+', 'D', 'D-', 'F+', 'F', 'F-')
) grades
INNER JOIN (
SELECT
term,
cid,
sec,
professor
FROM studentcoursedb.meeting
WHERE professor IS NOT NULL
GROUP BY term, cid, sec, professor
) m
ON grades.cid = m.cid AND grades.sec = m.sec AND grades.term = m.term
LEFT JOIN studentcoursedb.scheduled_courses schedc
ON grades.cid = schedc.cid AND grades.sec = schedc.sec AND grades.term = schedc.term
WHERE subj = 'ABC' AND crse >= 100 AND crse < 200
GROUP BY subj, crse, professor
ORDER BY avg(grade) DESC
) a
GROUP BY subj, crse, professor"""
minG = {}
minProf = {}
maxG = {}
maxProf = {}
cursor = pg.getcursor()
cursor.execute(q)
for rec in cursor:
if rec[1] in minG:
if minG[rec[1]] > rec[3]:
minG[rec[1]] = rec[3]
minProf[rec[1]] = rec[2]
else:
minG[rec[1]] = rec[3]
minProf[rec[1]] = rec[2]
if rec[1] in maxG:
if maxG[rec[1]] < rec[3]:
maxG[rec[1]] = rec[3]
maxProf[rec[1]] = rec[2]
else:
maxG[rec[1]] = rec[3]
maxProf[rec[1]] = rec[2]
classes = minG.keys()
classes = sorted(classes)
for c in classes:
print('ABC %s:' % c)
print('Easiest instructor: %s ' % maxProf[c])
print('grade: %s' % getGrade(maxG[c]))
print('Hardest instructor: %s ' % minProf[c])
print('grade: %s' % getGrade(minG[c]))
class Model:
pg = None
TABLE_NAME = '' # must define
fields = () # must define
sqlquery = ''
schema = 'studentcoursedb'
data = []
def __init__(self):
self.data = []
self.pg = PgInterface()
def setcustomquery(self, q):
self.sqlquery = q
def load(self):
cur = self.pg.getcursor()
cur.execute(self.sqlquery)
data = []
for row in cur:
data.append(row)
self.pg.closeconnection()
return data
def loadone(self):
cur = self.pg.getcursor()
cur.execute(self.sqlquery)
data = cur.fetchone()
self.pg.closeconnection()
return data
def createtable(self, schema):
cur = self.pg.getcursor()
name = self.TABLE_NAME
attr = self.fields
self.schema = schema
self.sqlquery = 'CREATE TABLE IF NOT EXISTS ' + schema + '.' + name + attr
print("query: " + self.sqlquery)
cur.execute(self.sqlquery)
self.pg.commit()
def enqueuedata(self, item):
self.data.append(item)
def enqueuedata_arr(self, items):
self.data.extend(items)
def insertdata(self):
cur = self.pg.getcursor()
# t = time.time()
self.sqlquery = 'INSERT INTO ' + self.schema + '.' + self.TABLE_NAME + ' VALUES '
for row in self.data:
self.sqlquery += '(' + str(row).strip('[]').replace(
'"', "'").replace("'NULL'", 'NULL').replace("'',",
'NULL,') + '),'
if len(self.data) > 0:
cur.execute(self.sqlquery[0:len(self.sqlquery) - 1])
self.pg.commit()
# print('commmit time: %.20f' % (time.time() - t))
self.sqlquery = ''
self.data = []
def __init__(self):
self.data = []
self.pg = PgInterface()
def prob3c():
pg = PgInterface()
q = """SELECT
professor,
avg(grade)
FROM (
SELECT
cid,
sec,
term,
(CASE WHEN grade = 'A+'
THEN 4.0
WHEN grade = 'A'
THEN 4.0
WHEN grade = 'A-'
THEN 3.7
WHEN grade = 'B+'
THEN 3.3
WHEN grade = 'B'
THEN 3.0
WHEN grade = 'B-'
THEN 2.7
WHEN grade = 'C+'
THEN 2.3
WHEN grade = 'C'
THEN 2.0
WHEN grade = 'C-'
THEN 1.7
WHEN grade = 'D+'
THEN 1.3
WHEN grade = 'D'
THEN 1.0
WHEN grade = 'D-'
THEN 0.7
WHEN grade = 'F+'
THEN 0
WHEN grade = 'F'
THEN 0
WHEN grade = 'F-'
THEN 0
END) grade
FROM studentcoursedb.enrolled
WHERE grade IN ('A+', 'A', 'A-', 'B+', 'B', 'B-', 'C+', 'C', 'C-', 'D+', 'D', 'D-', 'F+', 'F', 'F-')
) grades
LEFT JOIN (
SELECT
term,
cid,
sec,
professor
FROM studentcoursedb.meeting
WHERE professor IS NOT NULL
GROUP BY term, cid, sec, professor
) m
ON grades.cid = m.cid AND grades.sec = m.sec AND grades.term = m.term
GROUP BY professor
ORDER BY avg(grade) DESC;"""
cursor = pg.getcursor()
cursor.execute(q)
all = cursor.fetchall()
min = -1
max = 5
print('Easiest professors:')
for row in all:
if min == -1:
min = row[1]
elif min != row[1]:
break
print('%s: %0.4f' % row)
print('\nHardest professors:')
for row in reversed(all):
if max == 5:
max = row[1]
elif max != row[1]:
break
print('%s: %0.4f' % row)