def read_major_course():
connection = connect_to_sql()
data = {}
try:
with connection.cursor() as cursor:
sql = 'select m.major_id, major_name, c.course_id, course_name ' \
'from major_course as mc, major as m, course as c ' \
'where mc.major_id = m.major_id and mc.course_id = c.course_id ' \
'order by m.major_id, c.course_id;'
cursor.execute(sql)
result = cursor.fetchall()
data['major_courses'] = []
for row in result:
tmp = {
'major_id': row[0],
'major_name': row[1],
'course_id': row[2],
'course_name': row[3]
}
data['major_courses'].append(tmp)
except Exception as e:
data['error'] = str(e)
finally:
connection.close()
return data
def read_major():
connection = connect_to_sql()
data = {}
try:
with connection.cursor() as cursor:
sql = 'select m.major_id, major_name, count(student_id) as number_of_students ' \
'from major as m left outer join student as s ' \
'on m.major_id = s.major_id ' \
'group by m.major_id ' \
'order by m.major_id;'
cursor.execute(sql)
result = cursor.fetchall()
data['majors'] = []
for row in result:
tmp = {
'major_id': row[0],
'major_name': row[1],
'number_of_students': row[2]
}
data['majors'].append(tmp)
except Exception as e:
data['error'] = str(e)
finally:
connection.close()
return data
def read_course():
connection = connect_to_sql()
data = {}
try:
with connection.cursor() as cursor:
sql = 'select tmp.course_id, tmp.course_name, tmp.year, tmp.semester, tmp.teacher_id, ' \
'tmp.credit, tmp.number_of_students, tmp.average_grade, t.teacher_name from ( ' \
'select c.course_id, course_name, year, semester, teacher_id, credit, count(student_id), avg(grade) ' \
'from course as c left outer join student_course as sc ' \
'on c.course_id = sc.course_id ' \
'group by c.course_id ' \
') as tmp(course_id, course_name, year, semester, teacher_id, ' \
'credit, number_of_students, average_grade), teacher as t ' \
'where tmp.teacher_id = t.teacher_id ' \
'order by tmp.course_id; '
cursor.execute(sql)
result = cursor.fetchall()
data['courses'] = []
for row in result:
average_grade = row[7]
if average_grade is None:
average_grade = 0
tmp = {'course_id': row[0], 'course_name': row[1], 'year': row[2], 'semester': row[3],
'teacher_id': row[4], 'credit': row[5], 'number_of_students': row[6],
'average_grade': round(float(average_grade), 2), 'teacher_name': row[8]}
data['courses'].append(tmp)
except Exception as e:
data['error'] = str(e)
finally:
connection.close()
return data
def read_course_statistic(course_id):
connection = connect_to_sql()
data = {}
try:
with connection.cursor() as cursor:
# 查询某课程统计信息
sql = 'select ' \
'count(case when grade between 0 and 59 then grade end) as \'0-59\',' \
'count(case when grade between 60 and 69 then grade end) as \'60-69\',' \
'count(case when grade between 70 and 79 then grade end) as \'70-79\',' \
'count(case when grade between 80 and 89 then grade end) as \'80-89\',' \
'count(case when grade between 90 and 100 then grade end) as \'90-100\' ' \
'from student_course ' \
'where course_id = %s;' % course_id
cursor.execute(sql)
result = cursor.fetchone()
if result:
data['0-59'] = int(result[0])
data['60-69'] = int(result[1])
data['70-79'] = int(result[2])
data['80-89'] = int(result[3])
data['90-100'] = int(result[4])
else:
data['error'] = 'course_id ' + course_id + ' not found in database'
except Exception as e:
data['error'] = str(e)
finally:
connection.close()
return data
def read_selected_courses(student_id):
connection = connect_to_sql()
data = {}
try:
with connection.cursor() as cursor:
sql = 'select c.course_id, c.course_name, c.year, c.semester, t.teacher_name, c.credit, sc.grade ' \
'from course as c, student_course as sc, teacher as t ' \
'where sc.student_id = %s and sc.course_id = c.course_id ' \
'and t.teacher_id = c.teacher_id;' % student_id
cursor.execute(sql)
result = cursor.fetchall()
data['courses'] = []
for row in result:
tmp = {
'course_id': row[0],
'course_name': row[1],
'year': row[2],
'semester': row[3],
'teacher_name': row[4],
'credit': row[5],
'grade': row[6]
}
data['courses'].append(tmp)
except Exception as e:
data['error'] = str(e)
finally:
connection.close()
return data
def read_base_information(student_id):
connection = connect_to_sql()
data = {}
try:
with connection.cursor() as cursor:
# 查询学生基本信息
sql = 'select student_id, student_name, sex, birth_year, province, enter_year, major_name, s.major_id ' \
'from student as s, major as m ' \
'where s.student_id = %s and s.major_id = m.major_id;' % student_id
cursor.execute(sql)
result = cursor.fetchone()
if result:
data['student_id'] = result[0]
data['student_name'] = result[1]
data['sex'] = result[2]
data['birth_year'] = result[3]
data['province'] = result[4]
data['enter_year'] = result[5]
data['major_name'] = result[6]
data['major_id'] = result[7]
else:
data[
'error'] = 'student id ' + student_id + ' not found in database'
except Exception as e:
data['error'] = str(e)
finally:
connection.close()
return data
def read_teacher_list():
connection = connect_to_sql()
data = {}
try:
with connection.cursor() as cursor:
sql = 'select t.teacher_id, teacher_name, sex, birth_year, count(course_id) as number_of_courses ' \
'from teacher as t left outer join course as c ' \
'on c.teacher_id = t.teacher_id ' \
'group by t.teacher_id ' \
'order by t.teacher_id;'
cursor.execute(sql)
result = cursor.fetchall()
data['teachers'] = []
for row in result:
tmp = {
'teacher_id': row[0],
'teacher_name': row[1],
'sex': row[2],
'birth_year': row[3],
'number_of_courses': row[4]
}
data['teachers'].append(tmp)
except Exception as e:
data['error'] = str(e)
finally:
connection.close()
return data
def read_average_grade(student_id):
connection = connect_to_sql()
data = {}
try:
with connection.cursor() as cursor:
sql = 'select credit, grade from student_course as sc, course as c ' \
'where sc.student_id = %s and sc.course_id = c.course_id ' \
'and grade is not null;' % student_id
cursor.execute(sql)
result = cursor.fetchall()
if result:
total_credit = 0
total_grade = 0
for row in result:
total_credit += row[0]
total_grade += row[0] * row[1]
data['average_grade'] = round(
float(1.0 * total_grade / total_credit), 2)
else:
data['average_grade'] = 0
except Exception as e:
data['error'] = str(e)
finally:
connection.close()
return data
def delete_course(course_id):
connection = connect_to_sql()
data = {}
try:
with connection.cursor() as cursor:
sql = 'delete from course where course_id = \'%s\'; ' % course_id
cursor.execute(sql)
connection.commit()
except Exception as e:
data['error'] = str(e)
finally:
connection.close()
return data
def delete_teacher(teacher_id):
connection = connect_to_sql()
data = {}
try:
with connection.cursor() as cursor:
sql = 'delete from teacher where teacher_id = \'%s\'; ' % teacher_id
cursor.execute(sql)
connection.commit()
except Exception as e:
data['error'] = str(e)
connection.rollback()
finally:
connection.close()
return data
def update_major(major_id, major_name):
connection = connect_to_sql()
data = {}
try:
with connection.cursor() as cursor:
sql = 'update major set major_name = \'%s\' where major_id = \'%s\';' % (
major_name, major_id)
cursor.execute(sql)
connection.commit()
except Exception as e:
data['error'] = str(e)
connection.rollback()
finally:
connection.close()
return data
def read_teacher_id():
connection = connect_to_sql()
data = {'teacher_ids': []}
try:
with connection.cursor() as cursor:
sql = 'select teacher_id from teacher'
cursor.execute(sql)
result = cursor.fetchall()
for row in result:
data['teacher_ids'].append(row[0])
except Exception as e:
data['error'] = str(e)
finally:
connection.close()
return data
def read_single_major(major_id):
connection = connect_to_sql()
data = {}
try:
with connection.cursor() as cursor:
sql = 'select major_id, major_name from major where major_id = \'%s\';' % major_id
cursor.execute(sql)
result = cursor.fetchone()
data['major_id'] = result[0]
data['major_name'] = result[1]
except Exception as e:
data['error'] = str(e)
finally:
connection.close()
return data
def update_course(course_id, course_name, year, semester, teacher_id, credit):
connection = connect_to_sql()
data = {}
try:
with connection.cursor() as cursor:
sql = 'update course set course_name = \'%s\', year = \'%s\', semester = \'%s\',' \
'teacher_id = \'%s\', credit = %d where course_id = \'%s\';' \
% (course_name, year, semester, teacher_id, credit, course_id)
cursor.execute(sql)
connection.commit()
except Exception as e:
data['error'] = str(e)
connection.rollback()
finally:
connection.close()
return data
def update_teacher(teacher_id, teacher_name, sex, birth_year):
connection = connect_to_sql()
data = {}
try:
with connection.cursor() as cursor:
sql = 'update teacher set teacher_name = \'%s\', sex = \'%s\', birth_year = \'%s\' ' \
'where teacher_id = \'%s\'; ' % \
(teacher_name, sex, birth_year, teacher_id)
cursor.execute(sql)
connection.commit()
except Exception as e:
data['error'] = str(e)
connection.rollback()
finally:
connection.close()
return data
def update_student_grade(course_id, student_id, grade):
connection = connect_to_sql()
data = {}
try:
with connection.cursor() as cursor:
# 更新学生某课程成绩
sql = 'update student_course ' \
'set grade = %s ' \
'where course_id = \'%s\' and student_id = \'%s\';' % (grade, course_id, student_id)
cursor.execute(sql)
connection.commit()
except Exception as e:
data['error'] = str(e)
connection.rollback()
finally:
connection.close()
return data
def get_teacher_of_course(course_id):
connection = connect_to_sql()
data = {}
try:
with connection.cursor() as cursor:
sql = 'select teacher_id from course where course_id = \'%s\';' % course_id
cursor.execute(sql)
result = cursor.fetchone()
if result is None:
data['error'] = '课程编号不存在'
return data
data['teacher_id'] = result[0]
except Exception as e:
data['error'] = str(e)
finally:
connection.close()
return data
def read_number_of_courses(student_id):
connection = connect_to_sql()
data = {}
try:
with connection.cursor() as cursor:
sql = 'select count(course_id) from student_course ' \
'where student_id = %s;' % student_id
cursor.execute(sql)
result = cursor.fetchone()
if result:
data['number_of_courses'] = int(result[0])
else:
data['number_of_courses'] = 0
except Exception as e:
data['error'] = str(e)
finally:
connection.close()
return data
def read_total_credits(student_id):
connection = connect_to_sql()
data = {}
try:
with connection.cursor() as cursor:
sql = 'select if(sum(credit) is null, 0, sum(credit)) from student as s, course as c, student_course as sc ' \
'where s.student_id = %s and s.student_id = sc.student_id ' \
'and c.course_id = sc.course_id and grade >= 60;' % student_id
cursor.execute(sql)
result = cursor.fetchone()
if result:
data['credits'] = int(result[0])
else:
data['credits'] = 0
except Exception as e:
data['error'] = str(e)
finally:
connection.close()
return data
def get_new_teacher_id():
connection = connect_to_sql()
data = {}
try:
with connection.cursor() as cursor:
sql = 'select max(convert(teacher_id, unsigned integer))+1 from teacher order by teacher_id'
cursor.execute(sql)
tmp = cursor.fetchone()[0]
if tmp is None:
data['teacher_id'] = '00001'
else:
data['teacher_id'] = str(tmp)
while len(data['teacher_id']) < 5:
data['teacher_id'] = '0' + data['teacher_id']
except Exception as e:
data['error'] = str(e)
finally:
connection.close()
return data
def insert_course(course_name, year, semester, teacher_id, credit):
connection = connect_to_sql()
data = {}
try:
with connection.cursor() as cursor:
course_id = get_new_course_id()
if course_id.get('error'):
data['error'] = course_id['error']
return data
sql = 'insert into course values(\'%s\', \'%s\', \'%s\', \'%s\', \'%s\', %d);' \
% (course_id['course_id'], course_name, year, semester, teacher_id, credit)
cursor.execute(sql)
connection.commit()
except Exception as e:
data['error'] = str(e)
connection.rollback()
finally:
connection.close()
return data
def insert_major(major_name):
connection = connect_to_sql()
data = {}
try:
with connection.cursor() as cursor:
major_id = get_new_major_id()
if major_id.get('error'):
data['error'] = major_id['error']
return data
sql = 'insert into major values(\'%s\', \'%s\');' % (
major_id['major_id'], major_name)
cursor.execute(sql)
connection.commit()
except Exception as e:
data['error'] = str(e)
connection.rollback()
finally:
connection.close()
return data
def insert_major_course(major_id, course_id):
connection = connect_to_sql()
data = {}
try:
with connection.cursor() as cursor:
sql = 'insert into major_course values(\'%s\', \'%s\');' % (
major_id, course_id)
cursor.execute(sql)
"""
sql = 'insert into student_course ( select student_id, \'%s\', ' \
'null from student where major_id = \'%s\');' % (course_id, major_id)
cursor.execute(sql)
"""
connection.commit()
except Exception as e:
data['error'] = str(e)
connection.rollback()
finally:
connection.close()
return data
def insert_teacher(teacher_name, sex, birth_year):
connection = connect_to_sql()
data = {}
try:
with connection.cursor() as cursor:
teacher_id = get_new_teacher_id()
password = generate_password_hash(teacher_id['teacher_id'])
if teacher_id.get('error'):
data['error'] = teacher_id['error']
return data
sql = 'insert into teacher values(\'%s\', \'%s\', \'%s\', \'%s\', \'%s\');' \
% (teacher_id['teacher_id'], teacher_name, sex, birth_year, password)
cursor.execute(sql)
connection.commit()
except Exception as e:
data['error'] = str(e)
connection.rollback()
finally:
connection.close()
return data
def read_single_course(course_id):
connection = connect_to_sql()
data = {}
try:
with connection.cursor() as cursor:
sql = 'select course_id, course_name, year, semester, teacher_id, credit ' \
'from course where course_id = \'%s\';' % course_id
cursor.execute(sql)
result = cursor.fetchone()
data['course_id'] = result[0]
data['course_name'] = result[1]
data['year'] = result[2]
data['semester'] = result[3]
data['teacher_id'] = result[4]
data['credit'] = result[5]
except Exception as e:
data['error'] = str(e)
finally:
connection.close()
return data
def teacher_change_password(teacher_id, old_password, new_password):
connection = connect_to_sql()
data = {}
try:
with connection.cursor() as cursor:
sql = 'select password from teacher where teacher_id = \'%s\'; ' % teacher_id
cursor.execute(sql)
password_hash = cursor.fetchone()[0]
if not check_password_hash(password_hash, old_password):
data['error'] = '旧密码错误，修改密码失败'
return data
sql = 'update teacher set password = \'%s\' where teacher_id = \'%s\'; ' \
% (generate_password_hash(new_password), teacher_id)
cursor.execute(sql)
connection.commit()
except Exception as e:
data['error'] = str(e)
connection.rollback()
finally:
connection.close()
return data
def teacher_login_check(teacher_id, password):
connection = connect_to_sql()
data = {}
try:
with connection.cursor() as cursor:
sql = 'select password from teacher where teacher_id = \'%s\'; ' % teacher_id
cursor.execute(sql)
password_hash = cursor.fetchone()
if password_hash is None:
data['error'] = '教师不存在，请检查教师编号'
return data
password_hash = password_hash[0]
if not check_password_hash(password_hash, password):
data['error'] = '密码错误，登录失败'
return data
cursor.execute(sql)
except Exception as e:
data['error'] = str(e)
finally:
connection.close()
return data
def read_taught_courses(teacher_id):
connection = connect_to_sql()
data = {}
try:
with connection.cursor() as cursor:
# 查询教师授课列表
sql = 'select course_id, course_name, year, semester, credit ' \
'from course ' \
'where teacher_id = %s ' % teacher_id
cursor.execute(sql)
result = cursor.fetchall()
data['courses'] = []
if result:
for row in result:
tmp = {'course_id': row[0], 'course_name': row[1], 'year': row[2], 'semester': row[3],
'credit': row[4]}
data['courses'].append(tmp)
except Exception as e:
data['error'] = str(e)
finally:
connection.close()
return data
def read_selected_students(course_id):
connection = connect_to_sql()
data = {}
try:
with connection.cursor() as cursor:
# 查询选课学生列表
sql = 'select s.student_id, s.student_name, s.sex, m.major_name, sc.grade ' \
'from student_course as sc, student as s, major as m ' \
'where sc.student_id = s.student_id and s.major_id = m.major_id ' \
'and course_id = %s ' % course_id
cursor.execute(sql)
result = cursor.fetchall()
data['students'] = []
if result:
for row in result:
tmp = {'student_id': row[0], 'student_name': row[1], 'sex': row[2], 'major_name': row[3],
'grade': row[4]}
data['students'].append(tmp)
except Exception as e:
data['error'] = str(e)
finally:
connection.close()
return data
def read_base_information(teacher_id):
connection = connect_to_sql()
data = {}
try:
with connection.cursor() as cursor:
# 查询教师基本信息
sql = 'select teacher_id, teacher_name, sex, birth_year ' \
'from teacher ' \
'where teacher_id = %s;' % teacher_id
cursor.execute(sql)
result = cursor.fetchone()
if result:
data['teacher_id'] = result[0]
data['teacher_name'] = result[1]
data['sex'] = result[2]
data['birth_year'] = result[3]
else:
data['error'] = 'teacher id ' + teacher_id + ' not found in database'
except Exception as e:
data['error'] = str(e)
finally:
connection.close()
return data
