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)