def avg_coursetype(sid): db = get_db() cur = db.cursor() cur.execute( 'select avg(gpa) gpa, coursetype, sum(coursepoint) coursepoint from course, studentCourse where sid = %s and course.cid = studentCourse.cid group by coursetype', (sid)) avg_coursetype = get_results(cur) return avg_coursetype
def total_point(sid): db = get_db() cur = db.cursor() cur.execute( 'select sum(coursepoint) totalpoint from course, studentCourse where sid = %s and course.cid = studentCourse.cid', (sid)) total_point = get_results(cur) point = total_point return point
def updateScore(): id = session['id'] db = get_db() cur = db.cursor() cur.execute( 'select distinct course.cid,cname,coursetype,coursepoint,courseyear,courseterm,coursevolume,dailyScoreRatioDesc,scoreType,scoreReviewStatus from course,teacher,studentCourse where teacher.id=%s and teacher.id=course.tid and teacher.id=studentCourse.tid and studentCourse.cid=course.cid', (id)) courses = get_results(cur) return render_template('info/updateScore.html', courses=courses)
def updateScore(): id = session['id'] db = get_db() cur = db.cursor() cur.execute( 'select cid,cname,coursetype,coursepoint,coursevolume from course,teacher ' 'where teacher.id=%s and teacher.name=course.tname', (id)) courses = get_results(cur) return render_template('info/updateScore.html', courses=courses)
def load_logged_in_user(): id = session.get('id') if id is None: g.user = None else: cur = get_db().cursor() cur.execute( 'SELECT * FROM user WHERE id = %s', (id) ) g.user = get_results(cur)[0]
def worst_subject(sid): db = get_db() cur = db.cursor() i = cur.execute('select score, gpa, course.cid cid, course.cname cname from course, studentCourse where sid = %s and course.cid = studentCourse.cid and score <= 70 order by gpa limit 3', (sid)) if i != 0: worse_subject = get_results(cur) else: cur.execute('select score, gpa, course.cid cid, course.cname cname from course, studentCourse where sid = %s and course.cid = studentCourse.cid order by gpa limit 1', (sid)) worse_subject = get_results(cur) return worse_subject
def seeScore(cid): tid = session['id'] db = get_db() cur = db.cursor() cur.execute('update studentCourse set score = (dailyScore*dailyScoreRatio/100+finalExamScore*(100-dailyScoreRatio)/100) where cid=%s',(cid)) cur1 = db.cursor() cur1.execute('select sid ,name,dailyScore,finalExamScore,score,status from student,studentCourse where cid=%s and sid=student.id',(cid)) scores=get_results(cur1) db.commit() return render_template('info/seeScore.html', scores=scores)
def importScore(cid): tid = session['id'] CourseId = cid if request.method=="POST": db = get_db() cur = db.cursor() cur.execute('select sid,name from studentCourse,student where cid=%s and sid=student.id',(cid)) Students=get_results(cur) for student in Students: dailyScore = request.form[str(student['sid'])] finalExamScore = request.form[student['name']] db.cursor().execute('update studentCourse set dailyScore=%s,finalExamScore=%s where sid=%s and cid=%s',(dailyScore,finalExamScore,student['sid'],cid)) db.commit() return redirect(url_for('info.seeScore',cid=CourseId)) db = get_db() cur = db.cursor() cur.execute('select sid ,name,dailyScore,finalExamScore from student,studentCourse where cid=%s and sid=student.id', (cid)) students=get_results(cur) return render_template('info/importScore.html', students=students,cid=cid)
def seeScore(cid): tid = session['id'] db = get_db() cur1 = db.cursor() cur1.execute( 'select sid ,name,dailyScore,finalExamScore,score,scoreReviewStatus from student,studentCourse where cid=%s and sid=student.id', (cid)) scores = get_results(cur1) db.commit() return render_template('info/seeScore.html', scores=scores)
def courseterm_rank(sid): db = get_db() cur = db.cursor() sql = ''' select totalrank, courseterm from ( select sid, courseterm, row_number() over(PARTITION by courseterm order by avggpa desc) totalrank from ( select sum(sc)/sum(coursepoint) avggpa, courseterm, sid from ( select gpa*coursepoint sc, coursepoint, courseterm, sid from course, studentCourse where course.cid = studentCourse.cid) as s group by courseterm, sid) as s) as s where sid = %s ''' cur.execute(sql, (sid)) courseterm_rank = get_results(cur) return courseterm_rank
def index(check_author=True): id = session['id'] db = get_db() cur = db.cursor() cur.execute( 'SELECT coursetype, cname, tname, courseyear, courseterm, coursepoint, score, gpa' ' FROM studentCourse JOIN course' ' WHERE sid = %s', (id)) courselist = get_results(cur) if courselist is None: abort(404, "Student id {0} doesn't have Course score.".format(id)) return render_template('info/index.html', courses=courselist)
def reviewProposal(cid, sid): db = get_db() cur = db.cursor() if g.user['auth'] == 1: cur.execute( 'update proposal set is_checked_by_teacher = 1 where cid = %s and sid = %s', (cid, sid)) elif g.user['auth'] == 2: cur.execute( 'update proposal set is_checked_by_dean = 1 where cid = %s and sid = %s', (cid, sid)) db.commit() return redirect(url_for('info.showProposal'))
def myScore(): sid = session['sid'] cate = request.form['cate'] term = request.form['term'] scores = get_db().execute( 'select courseName, score, GPA, render, entryStatus from Performances, Courses where studentNo=?' 'and Performances.courseNo in (select courseNo from Courses where courseCate=? and courseTerm=?) and Performances.courseNo=Courses.courseNo', (sid, cate, term)).fetchall() if scores is None: abort(404, "Student id {0} doesn't have selected score.".format(sid)) results = [] for x in scores: results.append({'courseName': x[0], 'score': x[1], 'GPA': x[2], 'rank': x[3], 'entryStatus': x[4]}) return render_template('info/myScore.html', scores=results)
def load_logged_in_user(): """If a user id is stored in the session, load the user object from the database into ``g.user``.""" id = session.get('id') if id is None: g.user = None else: cur = get_db().cursor() cur.execute( 'SELECT * FROM user WHERE id = %s', (id) ) g.user = get_results(cur)[0]
def score_distribution(sid): db = get_db() cur = db.cursor() cur.execute('select count(*) 小于60 from course, studentCourse where sid = %s and course.cid = studentCourse.cid and score < 60', (sid)) score_distribution = get_results(cur) cur.execute('select count(*) 60至70 from course, studentCourse where sid = %s and course.cid = studentCourse.cid and score >= 60 and score < 70', (sid)) score_distribution.extend(get_results(cur)) cur.execute('select count(*) 70至80 from course, studentCourse where sid = %s and course.cid = studentCourse.cid and score >= 70 and score < 80', (sid)) score_distribution.extend(get_results(cur)) cur.execute('select count(*) 80至90 from course, studentCourse where sid = %s and course.cid = studentCourse.cid and score >= 80 and score < 90', (sid)) score_distribution.extend(get_results(cur)) cur.execute('select count(*) 90至100 from course, studentCourse where sid = %s and course.cid = studentCourse.cid and score >= 90', (sid)) score_distribution.extend(get_results(cur)) return score_distribution
def exportScoreList(cid): db = get_db() cur = db.cursor() sio = BytesIO() workbook = xlsxwriter.Workbook( sio, {'in_memory': True}) # xlwt.Workbook(encoding='ascii') # 写到IO中 cur.execute('select * from course where cid = %s' % cid) course = get_results(cur)[0] filename = '%s_%s_%s' % (course['cname'], course['courseyear'], course['courseterm']) worksheet = workbook.add_worksheet(name=filename) # worksheet.merge_range(0, 0, 0, 5, 'aaa') # 合并单元格 # worksheet.write(11, 2, '=SUM(1:10)') # 增加公式 # worksheet.set_default_row(35) # 设置默认行高 style1 = workbook.add_format({ 'font_size': '11', 'align': 'center', 'valign': 'vcenter', 'bold': True }) # 设置风格 'bg_color': '#34A0FF', style2 = workbook.add_format({ 'font_size': '11', 'align': 'center', 'valign': 'vcenter', 'bold': False }) # 'font_color': '#217346' worksheet.set_column('A:H', None, style2) worksheet.set_column(0, 7, 20) # 设置列宽 title = ['学号', '姓名', '学院', '专业', '平时成绩', '期末成绩', '最终成绩', '成绩状态'] worksheet.write_row('A1', title, style1) cur.execute( 'select sid, name, school, major, dailyScore, finalExamScore, score, studentExamStatus from student, studentCourse ' 'where sid = id and cid = %s', (cid)) scores = get_results(cur) i = 0 for score in scores: data = [ score['sid'], score['name'], score['school'], score['major'], score['dailyScore'], score['finalExamScore'], score['score'], score['studentExamStatus'] ] worksheet.write_row('A' + str(i + 2), data) i = i + 1 workbook.close() sio.seek(0) # 将byte流再从头读取,之前已经写到最后一个byte了 resp = sio.getvalue() # 通过getvalue函数读取IO流 sio.close() # 关闭IO流 orderdata = [resp, filename.encode().decode('latin1')] return orderdata
def scoreMain(cid): id = session['id'] db = get_db() cur = db.cursor() cur.execute( 'SELECT sid,name,school,major,dailyScore,finalExamScore,score,studentExamStatus FROM student,studentCourse WHERE student.id=studentCourse.sid and cid = %s and tid=%s', (cid, id)) courses = get_results(cur) cur.execute( 'select courseyear,courseterm,cname,dailyScoreRatioDesc from course where cid = %s', (cid)) info = get_results(cur) return render_template('info/scoreMain.html', courses=courses, cid=cid, info=info)
def selectAna(): courses = {} sid = session['id'] db = get_db() cur = db.cursor() cc = "" cy = "" ccd = "" cyd = "" if request.method == 'POST': courseclass = request.form.getlist('coursetype') courseyear = request.form.getlist('courseyear') cc = "' or ".join(("courseclass = '" + str(n) for n in courseclass)) cy = "' or ".join(("courseyear = '" + str(n) for n in courseyear)) cc = cc + "'" cy = cy + "'" query = 'CREATE TEMPORARY TABLE scores select sid,ROUND(sum(sc)/sum(coursepoint),2) as avggpa from (select gpa*coursepoint sc, coursepoint,sid from course, studentCourse where course.cid = studentCourse.cid and (' + cc + ') and (' + cy + ')) as s GROUP BY sid ' cur.execute(query) query1 = 'CREATE TEMPORARY TABLE scores1 select sid,ROUND(sum(sc)/sum(coursepoint),2) as avggpa from (select gpa*coursepoint sc, coursepoint,sid from course, studentCourse where course.cid = studentCourse.cid and (' + cc + ') and (' + cy + ')) as s GROUP BY sid ' cur.execute(query1) query2 = 'SELECT avggpa, FIND_IN_SET( avggpa, (SELECT GROUP_CONCAT( avggpa ORDER BY avggpa DESC ) FROM scores )) as rk FROM scores1 where sid = %s' cur.execute(query2, sid) courses = get_results(cur) for i in courseclass: if i == 'gx': ccd = ccd + " 公选 " elif i == 'gb': ccd = ccd + " 公必 " elif i == 'zb': ccd = ccd + " 专必 " elif i == 'zx': ccd = ccd + " 专选 " for i in courseyear: if i == '2018': cyd = cyd + " 2018 " elif i == '2019': cyd = cyd + " 2019 " elif i == '2017': cyd = cyd + " 2017 " return render_template('info/selectAna.html', courses=courses, cc=ccd, cy=cyd)
def index(): id = session['id'] db = get_db() cur = db.cursor() total_rank = {} total_rank['avg_coursetype'] = avg_coursetype(id) total_rank['total_point'] = total_point(id) total_rank['total_avg_gpa'] = total_avg_gpa(id) courseClass= {} courseClass['gx'] = courseclass_gpa_rank(id)[1] courseClass['gb'] = courseclass_gpa_rank(id)[2] courseClass['zx'] = courseclass_gpa_rank(id)[0] courseClass['zb'] = courseclass_gpa_rank(id)[3] sql = 'SELECT coursetype, cname, tname, courseyear, courseterm, coursepoint, score, gpa,`status` st ' sql += 'FROM studentCourse sc JOIN course c where sc.cid = c.cid and ' sql += 'sid = %s' % id if request.method == 'POST': type = request.form['coursetype'] year = request.form['courseyear'] term = request.form['courseterm'] if type == u'' and year == u'' and term == u'': pass if type is not u'': sql += ' and coursetype = %s' % type if year is not u'': sql += ' and courseyear = %s' % year if term is not u'': sql += ' and courseterm = %s' % term cur.execute(sql) courselist = get_results(cur) return render_template('info/index.html', courses=courselist, scores=total_rank, cc=courseClass) cur.execute(sql) courselist = get_results(cur) if len(courselist) is 0: abort(404, "Student id {0} doesn't have Course score.".format(id)) courseClass= {} courseClass['gx'] = courseclass_gpa_rank(id)[1] courseClass['gb'] = courseclass_gpa_rank(id)[2] courseClass['zx'] = courseclass_gpa_rank(id)[0] courseClass['zb'] = courseclass_gpa_rank(id)[3] return render_template('info/index.html', courses=courselist, scores=total_rank,cc=courseClass)
def showProposal(): db = get_db() cur = db.cursor() id = g.user['id'] if g.user['auth'] == 0: cur.execute('select distinct * ' 'from proposal p join course c ' 'where p.cid = c.cid ' 'and p.sid = %s' % id) elif g.user['auth'] == 1: cur.execute( 'select distinct * from proposal p join course c join student s ' 'where p.cid = c.cid and s.id = p.sid and p.cid in (select distinct cid from course where tid = %s)', id) else: cur.execute( 'select distinct * from proposal p join course c join student s ' 'where p.cid = c.cid and s.id = p.sid') proposals = get_results(cur) return render_template('info/showProposal.html', proposals=proposals)
def update(id): """Update a post if the current user is the author.""" post = get_post(id) if request.method == 'POST': title = request.form['title'] body = request.form['body'] error = None if not title: error = 'Title is required.' if error is not None: flash(error) else: db = get_db() db.execute('UPDATE post SET title = ?, body = ? WHERE id = ?', (title, body, id)) db.commit() return redirect(url_for('blog.index')) return render_template('blog/update.html', post=post)
def createCourse(): if request.method == 'POST': cname = request.form['cname'] courseterm = request.form['courseterm'] coursepoint = request.form['coursepoint'] coursetype = request.form['coursetype'] coursevolume = request.form['coursevolume'] courseyear = request.form['courseyear'] courseclass = request.form['courseclass'] dailyScoreRatio = request.form['dailyScoreRatio'] dailyScoreRatioDesc = request.form['dailyScoreRatioDesc'] tid = g.user['id'] db = get_db() cur = db.cursor() cur.execute( 'INSERT INTO course(cname, courseterm, courseyear, coursepoint, coursetype, coursevolume, tid, dailyScoreRatio, dailyScoreRatioDesc, courseclass) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)', (cname, courseterm, courseyear, coursepoint, coursetype, coursevolume, tid, dailyScoreRatio, dailyScoreRatioDesc, courseclass)) db.commit() return redirect(url_for('info.index')) return render_template('info/createCourse.html')
def register(): """Register a new user. Validates that the username is not already taken. Hashes the password for security. Use cursor to execute SQL query. """ if request.method == 'POST': username = request.form['username'] password = request.form['password'] id = request.form['id'] #sname = request.form['sname'] auth = request.form['auth'] if auth == 0: tablename = 'student' else: tablename = 'teacher' is_male = request.form['is_male'] #school = request.form['school'] #enrollyear = request.form['enrollyear'] db = get_db() cur = db.cursor() error = None if cur.execute('SELECT id FROM user WHERE username = %s', (username)) is not 0: error = 'NetID {0} is already registered.'.format(username) if error is None: cur.execute( 'INSERT INTO user (id, username, password, auth, is_male) VALUES (%s, %s, %s, %s, %s)', (id, username, generate_password_hash(password), auth, is_male)) db.commit() return redirect(url_for('auth.login')) flash(error) return render_template('auth/register.html')
def course_score(cid): db = get_db() cur = db.cursor() cur.execute( 'select count(*) 小于60 from studentCourse JOIN student ON id = sid where cid = %s and score < 60', (cid)) score_distribution = get_results(cur) cur.execute( 'select count(*) 60至70 from studentCourse JOIN student ON id = sid where cid = %s and score >= 60 and score < 70', (cid)) score_distribution.extend(get_results(cur)) cur.execute( 'select count(*) 70至80 from studentCourse JOIN student ON id = sid where cid = %s and score >= 70 and score < 80', (cid)) score_distribution.extend(get_results(cur)) cur.execute( 'select count(*) 80至90 from studentCourse JOIN student ON id = sid where cid = %s and score >= 80 and score < 90', (cid)) score_distribution.extend(get_results(cur)) cur.execute( 'select count(*) 90至100 from studentCourse JOIN student ON id = sid where cid = %s and score >= 90', (cid)) score_distribution.extend(get_results(cur)) return score_distribution
def importScore(cid): id = session['id'] db1 = get_db() cur1 = db1.cursor() cur1.execute('select distinct scoreType from studentCourse where cid=%s', (cid)) type = get_results(cur1) db = get_db() cur = db.cursor() cur.execute( 'SELECT sid,name,school,major FROM student,studentCourse WHERE student.id=studentCourse.sid and cid = %s and tid=%s', (cid, id)) students = get_results(cur) cur.execute('select dailyScoreRatio from course where cid =%s', cid) per = get_results(cur) if type[0]['scoreType'] == '百分制': if request.method == "POST": db = get_db() cur = db.cursor() cur.execute( 'select sid,name from studentCourse,student where cid=%s and sid=student.id', (cid)) Students1 = get_results(cur) for student in Students1: str1 = '%s%s' % ( str(student['sid']), student['name'] ) #用+会坑,详见https://blog.csdn.net/zyz511919766/article/details/22072701 StudentExamStatus = request.form[str1] if (StudentExamStatus == '正常'): #disabled之后请求不了dailyScore dailyScore = request.form[str(student['sid'])] finalExamScore = request.form[student['name']] cur.execute( 'select dailyScoreRatio from course where cid = %s' % cid) ratio = int(get_results(cur)[0]['dailyScoreRatio']) if (dailyScore == ''): dailyScore = None else: dailyScore = int(dailyScore) if (finalExamScore == ''): finalExamScore = None else: finalExamScore = int(finalExamScore) if (finalExamScore != None and dailyScore != None): score = (dailyScore * ratio + (100 - ratio) * finalExamScore) / 100 else: score = None db.cursor().execute( 'update studentCourse set dailyScore=%s,finalExamScore=%s,StudentExamStatus=%s,score=%s where sid=%s and cid=%s', (dailyScore, finalExamScore, StudentExamStatus, score, student['sid'], cid)) else: dailyScore = None finalExamScore = None score = None db.cursor().execute( 'update studentCourse set dailyScore=%s,finalExamScore=%s,StudentExamStatus=%s,score=%s where sid=%s and cid=%s', (dailyScore, finalExamScore, StudentExamStatus, score, student['sid'], cid)) db.commit() return redirect(url_for('info.scoreMain', cid=cid)) #cur.execute('SELECT sid,name,school,major FROM student,studentCourse WHERE student.id=studentCourse.sid and cid = %s and tid=%s',(cid,id)) cur.execute( 'SELECT * FROM student,studentCourse WHERE student.id=studentCourse.sid and cid = %s and tid=%s', (cid, id)) students = get_results(cur) return render_template('info/importScore.html', students=students, cid=cid, per=per) else: if request.method == "POST": db = get_db() cur = db.cursor() cur.execute( 'select sid,name from studentCourse,student where cid=%s and sid=student.id', (cid)) Students1 = get_results(cur) dailyScore = None finalExamScore = None for student in Students1: level = request.form['level'] str1 = '%s%s' % ( str(student['sid']), student['name'] ) #用+会坑,详见https://blog.csdn.net/zyz511919766/article/details/22072701 StudentExamStatus = request.form['status'] if level == '优秀': score = 95 elif level == '良好': score = 85 elif level == '一般': score = 75 elif level == '合格': score = 65 elif level == '不合格': score = 55 else: score = None #空键处理 db.cursor().execute( 'update studentCourse set dailyScore=%s,finalExamScore=%s,score=%s,StudentExamStatus=%s where sid=%s and cid=%s', (dailyScore, finalExamScore, score, StudentExamStatus, student['sid'], cid)) db.commit() return redirect(url_for('info.scoreMain', cid=cid)) return render_template('info/importScore0.html', students=students, cid=cid, per=per) #需要等级制页面吗
def myAnalysis(): sid = session['sid'] analysis = get_db().execute( 'select courseTerm, avg(score), max(score), min(score) from Performances, Courses where Performances.courseNo=Courses.courseNo' )
def index(): if session['auth'] == 2: id = session['id'] db = get_db() cur = db.cursor() sql = 'select distinct course.cid,cname,dailyScoreRatioDesc,coursepoint,courseyear,courseterm,scoreType,scoreReviewStatus from course,teacher,studentCourse where teacher.id=%s and teacher.id=course.tid and teacher.id=studentCourse.tid and studentCourse.cid=course.cid' % id if request.method == 'POST': year = request.form['courseyear'] term = request.form['courseterm'] if year == u'' and term == u'': pass if year is not u'': sql += ' and courseyear = %s' % year if term is not u'': sql += ' and courseterm = %s' % term cur.execute(sql) courses = get_results(cur) return render_template('info/index3.html', courses=courses) cur.execute(sql) courses = get_results(cur) return render_template('info/index3.html', courses=courses) if session['auth'] == 1: id = session['id'] db = get_db() cur = db.cursor() sql = 'select distinct course.cid,cname,dailyScoreRatioDesc,coursepoint,courseyear,courseterm,scoreType,scoreReviewStatus from course,teacher,studentCourse where teacher.id=%s and teacher.id=course.tid and teacher.id=studentCourse.tid and studentCourse.cid=course.cid' % id if request.method == 'POST': year = request.form['courseyear'] term = request.form['courseterm'] if year == u'' and term == u'': pass if year is not u'': sql += ' and courseyear = %s' % year if term is not u'': sql += ' and courseterm = %s' % term cur.execute(sql) courses = get_results(cur) return render_template('info/index2.html', courses=courses) cur.execute(sql) courses = get_results(cur) return render_template('info/index2.html', courses=courses) id = session['id'] db = get_db() cur = db.cursor() total_rank = {} total_rank['avg_coursetype'] = avg_coursetype(id) total_rank['total_point'] = total_point(id) total_rank['total_avg_gpa'] = total_avg_gpa(id) courseClass = {} for i in range(len(courseclass_gpa_rank(id))): ctype = courseclass_gpa_rank(id)[i]['courseclass'] courseClass[ctype] = courseclass_gpa_rank(id)[i] sql = 'SELECT coursetype, cname, t.name, courseyear, courseterm, coursepoint, dailyScore, finalExamScore, score, gpa,`scoreReviewStatus` st, studentExamStatus ' sql += 'FROM studentCourse sc JOIN course c JOIN teacher t where sc.tid = t.id and sc.cid = c.cid and ' sql += 'sid = %s' % id if request.method == 'POST': type = request.form['coursetype'] year = request.form['courseyear'] term = request.form['courseterm'] if type == u'' and year == u'' and term == u'': pass if type is not u'': sql += ' and coursetype = \'%s\'' % type if year is not u'': sql += ' and courseyear = %s' % year if term is not u'': sql += ' and courseterm = %s' % term #cur.execute(sql) #courselist = get_results(cur) #return render_template('info/index.html', courses=courselist, scores=total_rank, cc=courseClass) cur.execute(sql) courselist = get_results(cur) if len(courselist) is 0: abort(404, "Student id {0} doesn't have Course score.".format(id)) courseClass = {} for i in range(len(courseclass_gpa_rank(id))): ctype = courseclass_gpa_rank(id)[i]['courseclass'] courseClass[ctype] = courseclass_gpa_rank(id)[i] sql = 'SELECT distinct c.cid, cname, t.name,courseyear,scoreReviewStatus FROM studentCourse sc JOIN course c JOIN teacher t where sc.tid = t.id and sc.cid = c.cid and ' sql += 'sid = %s order by c.cid desc limit 3' % id cur.execute(sql) notices = get_results(cur) return render_template('info/index.html', courses=courselist, scores=total_rank, cc=courseClass, notices=notices)
def course_count(cid): db = get_db() cur = db.cursor() cur.execute('select count(*) count from studentCourse where cid = %s', (cid)) course_count = get_results(cur) return course_count
def course_avg(cid): db = get_db() cur = db.cursor() cur.execute('select avg(score) avg from studentCourse where cid = %s', (cid)) course_avg = get_results(cur) return course_avg
def total_avg_gpa(sid): db = get_db() cur = db.cursor() cur.execute('select sum(sc)/sum(coursepoint) avggpa from (select gpa*coursepoint sc, coursepoint from course, studentCourse where sid = %s and course.cid = studentCourse.cid) as s', (sid)) total_avg_gpa = get_results(cur) return total_avg_gpa