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)
