def get_stu_course_by_stu_id(stu_id): with app.app_context(): try: db = dbconnect.get_conn() cursor = db.cursor() sql = "select c.id, c.name, c.credit, sc.score \ from course c \ join student_course sc on c.id = sc.course_id\ join student s on sc.student_id = s.id\ where s.id = '%s'" % (stu_id) cursor.execute(sql) res = cursor.fetchall() stu_course_list = [] for row in res: list_item = {} list_item['course_id'] = row[0] list_item['course_name'] = row[1] list_item['credit'] = row[2] list_item['score'] = row[3] stu_course_list.append(list_item) db.commit() return stu_course_list except IndexError as ie: print(ie) return []
def get_stu_list_by_class_id(class_id): with app.app_context(): try: db = dbconnect.get_conn() cursor = db.cursor() cursor.execute( "select * from student where class_id = '%s'" % (class_id)) res = cursor.fetchall() student_list = [] for row in res: stu = { 'id': row[0], 'name': row[1], 'sex': row[3], 'birthdate': str(row[4]), 'entrance_date': str(row[5]), 'class_id': str(row[6]) } student_list.append(stu) return student_list except IndexError as ie: print(ie) return None except: db.rollback() return None
def get_class_stu_course(teacher_id, class_id, course_id): with app.app_context(): try: db = dbconnect.get_conn() cursor = db.cursor() sql = "select s.id,s.name,sc.score from student s \ join student_course sc on s.id = sc.student_id \ join course c on c.id = sc.course_id \ join teacher_course_class tcc on c.id = tcc.course_id \ join class cl on cl.id = tcc.class_id \ where tcc.teacher_id = '%s' and tcc.course_id = '%s' \ and tcc.class_id = '%s' and s.class_id = cl.id" % ( teacher_id, course_id, class_id) cursor.execute(sql) res = cursor.fetchall() class_course_list = [] for row in res: stu_course = {} stu_course['student_id'] = row[0] stu_course['student_name'] = row[1] stu_course['score'] = row[2] class_course_list.append(stu_course) db.commit() return class_course_list except IndexError as ie: print(ie) return None
def get_tea_by_id(id): with app.app_context(): try: db = dbconnect.get_conn() cursor = db.cursor() cursor.execute("select * from teacher where id= '%s'" % (id)) res = cursor.fetchall()[0] teacher = { 'id': id, 'name': res[1], 'password': res[2], 'course_list': [] } cursor.execute( "select tcc.course_id,c.name,tcc.class_id from teacher_course_class tcc join course c on tcc.course_id = c.id where teacher_id = '%s'" % (id)) res = cursor.fetchall() for row in res: tc = { 'course_id': row[0], 'course_name': row[1], 'class_id': row[2] } teacher['course_list'].append(tc) db.commit() return teacher except IndexError as ie: print(ie) return None
def insert_class(class_id): with app.app_context(): try: db = dbconnect.get_conn() cursor = db.cursor() cursor.execute("insert into class(id) values('%s')" % (class_id)) db.commit() return True except: db.rollback() return False
def delete_teacher(teacher_id): with app.app_context(): try: db = dbconnect.get_conn() cursor = db.cursor() sql = "delete from teacher where id = '%s'" % (teacher_id) cursor.execute(sql) db.commit() return True except: db.rollback() return False
def insert_course(course_id, course_name, credit): with app.app_context(): try: db = dbconnect.get_conn() cursor = db.cursor() cursor.execute("insert into course(id,name,credit) values('%s','%s',%s)" % ( course_id, course_name, credit)) db.commit() return True except: db.rollback() return False
def update_course(course_id, course_name, credit): with app.app_context(): try: db = dbconnect.get_conn() cursor = db.cursor() cursor.execute("update course set name = '%s', credit = '%s' where id = '%s'" % ( course_name, credit, course_id)) db.commit() return True except: db.rollback() return False
def set_score(stu_id, course_id, score): with app.app_context(): try: db = dbconnect.get_conn() cursor = db.cursor() cursor.execute( "update student_course set score = %s where student_id = '%s' and course_id = '%s'" % (score, stu_id, course_id)) db.commit() except: return False return True
def insert_teacher(teacher_id, name): with app.app_context(): try: db = dbconnect.get_conn() cursor = db.cursor() sql = "insert into teacher(id,password,name) values(\ '%s','%s','%s')" % (teacher_id, teacher_id, name) cursor.execute(sql) db.commit() return True except: db.rollback() return False
def update_teacher(teacher_id, name): with app.app_context(): try: db = dbconnect.get_conn() cursor = db.cursor() sql = "update teacher set name = '%s' where id = '%s'" % ( name, teacher_id) cursor.execute(sql) db.commit() return True except: db.rollback() return False
def add_stu(stu_id,name,sex,birthdate,entrance_date,class_id): with app.app_context(): try: db = dbconnect.get_conn() cursor = db.cursor() sql = "insert into student(id,password,name,sex,birthdate,entrance_date,class_id) values(\ '%s','%s','%s',%s,'%s','%s','%s')" % (stu_id,stu_id,name,sex,birthdate,entrance_date,class_id) print(sql) cursor.execute(sql) db.commit() return True except: db.rollback() return False
def delete_course(course_id): with app.app_context(): try: db = dbconnect.get_conn() cursor = db.cursor() sql = "delete from course where id = '%s'" % ( course_id) print(sql) cursor.execute(sql) db.commit() return True except: db.rollback() return False
def update_stu(stu_id,name,sex,birthdate,entrance_date,class_id): with app.app_context(): try: db = dbconnect.get_conn() cursor = db.cursor() sql = "update student set name = '%s', sex = %s, \ birthdate = '%s', entrance_date='%s', class_id = '%s'\ where id = '%s'" % (name,sex,birthdate,entrance_date,class_id,stu_id) print(sql) cursor.execute(sql) db.commit() return True except: db.rollback() return False
def get_adm_by_id(id): with app.app_context(): try: db = dbconnect.get_conn() cursor = db.cursor() cursor.execute("select * from admin where id= '%s'" % (id)) res = cursor.fetchall()[0] admin = { 'id': id, 'name': res[1], 'password': res[2], } return admin except IndexError as ie: print(ie) return None
def get_course_by_id(course_id): with app.app_context(): try: db = dbconnect.get_conn() cursor = db.cursor() cursor.execute("select * from course where id= '%s'" % (course_id)) res = cursor.fetchall()[0] course = { 'id': course_id, 'name': res[1], 'credit': res[2], } return course except IndexError as ie: print(ie) return None
def get_class_list(): with app.app_context(): try: db = dbconnect.get_conn() cursor = db.cursor() cursor.execute("select * from class") res = cursor.fetchall() class_list = [] for row in res: cl = {} cl['id'] = row[0] class_list.append(cl) db.commit() return class_list except: db.rollback() return None
def get_course_list(): with app.app_context(): try: db = dbconnect.get_conn() cursor = db.cursor() cursor.execute("select id,name from course") res = cursor.fetchall() course_list = [] for row in res: course = {} course['id'] = row[0] course['name'] = row[1] course_list.append(course) return course_list except IndexError as ie: print(ie) return None
def get_tea_list(): with app.app_context(): try: db = dbconnect.get_conn() cursor = db.cursor() cursor.execute("select id,name from teacher") res = cursor.fetchall() teacher_list = [] for row in res: teacher = {} teacher['id'] = row[0] teacher['name'] = row[1] teacher_list.append(teacher) return teacher_list except IndexError as ie: print(ie) return None
def get_seudent_course_bu_course_id(course_id): with app.app_context(): try: db = dbconnect.get_conn() cursor = db.cursor() cursor.execute( "select * from student_course where course_id = '%s'" % (course_id)) res = cursor.fetchall() stu_course_list = [] for row in res: list_item = {} list_item['student_id'] = row[0] list_item['course_id'] = row[1] list_item['score'] = row[2] stu_course_list.append(list_item) db.commit() return stu_course_list except: db.rollback() return []
def get_stu_by_id(id): with app.app_context(): try: db = dbconnect.get_conn() cursor = db.cursor() cursor.execute("select * from student where id= '%s'" % (id)) res = cursor.fetchall()[0] student = { 'id': id, 'name': res[1], 'password': res[2], 'sex': res[3], 'birthdate': str(res[4]), 'entrance_date': str(res[5]), 'class_id': str(res[6]) } db.commit() return student except IndexError as ie: print(ie) return None
def get_tea_course_class_by_course_id(course_id): with app.app_context(): try: db = dbconnect.get_conn() cursor = db.cursor() sql = "select t.id,t.name,tcc.class_id from teacher_course_class tcc\ join teacher t on tcc.teacher_id = t.id\ where tcc.course_id = '%s'" % (course_id) cursor.execute(sql) res = cursor.fetchall() tea_course_list = [] for row in res: list_item = {} list_item['tea_id'] = row[0] list_item['tea_name'] = row[1] list_item['class_id'] = row[2] tea_course_list.append(list_item) db.commit() return tea_course_list except: db.rollback() return []
""" This script runs the URP_Server application using a development server. """ from os import environ from URP_Server import app from URP_Server.db import dbconnect if __name__ == '__main__': HOST = environ.get('SERVER_HOST', 'localhost') try: PORT = int(environ.get('SERVER_PORT', '5555')) except ValueError: PORT = 5555 with app.app_context(): dbconnect.get_conn() app.run(HOST, PORT, debug=True)