def delete_teacher():
db = pymysql.connect(host="localhost",
user="******",
password="******",
db="face_recognition",
port=3306,
charset='utf8')
cursor = db.cursor()
sql = "SELECT t_id FROM teacher"
cursor.execute(sql)
t = cursor.fetchall()
if request.method == "GET":
return render_template("delete_teacher.html", t=t)
else:
teacher_id = request.form.get('teacher_id')
print(teacher_id)
sql = "DELETE FROM teacher WHERE t_id='%s'" % teacher_id
try:
cursor.execute(sql)
db.commit()
except:
print('error')
db.rollback()
db.close()
message = "删除教师数据成功"
return render_template("index.html", message=message)
def delete_expire():
date_begin = request.args.get()
date_end = request.args.get()
delete_position = Position.query.filter(Position.createTime.between(date_begin,date_end))
for d in delete_position:
db.session.delete(d)
db.commit()
return "success"
def like():
#点赞
if request.method == 'POST':
author = request.form['author']
title = request.form['title']
likes = request.form['like']
sql_update = "update writing set likes = '%s' where author = '%s' and title = '%s'"
try:
cur.execute(sql_update % (likes, author, title)) # 像sql语句传递参数
# 提交
db.commit()
return '点赞成功'
except:
return ("Error: unable to fetch data")
else:
return '404'
def collection():
if request.method == 'POST':
#获得文章网址 收藏ok
xuehao = request.form['xuehao']
collection = request.form['collection']
sql_insert = """INSERT INTO collection(xuehao,collection) VALUES ('%s','%s')"""
try:
# 执行sql语句
cursor.execute(sql_insert % (xuehao, collection))
# 执行sql语句
db.commit()
return '收藏成功'
except:
# 发生错误时回滚
db.rollback()
return 'no'
else:
return '404'
def insert_teacher():
message = "提示信息："
if request.method == 'GET':
return render_template('insert_teacher.html', message=message)
else:
teacher_name = request.form.get('teacher_name')
teacher_id = request.form.get('teacher_id')
teacher_sex = request.form.get('teacher_sex')
teacher_wx = request.form.get('teacher_wx')
print(type(teacher_name))
print(type(teacher_id))
print(type(teacher_sex))
print(type(teacher_wx))
db = pymysql.connect(host="localhost",
user="******",
password="******",
db="face_recognition",
port=3306,
charset='utf8')
cursor = db.cursor()
sql = "SELECT t_id FROM teacher"
cursor.execute(sql)
result = cursor.fetchall()
for i in result:
print(i[0])
if i[0] == teacher_id:
message = message + "该工号教师已存在请重新插入"
return render_template('insert_teacher.html', message=message)
sql = "INSERT INTO teacher(t_name,t_id,t_sex,t_wx) VALUES ('%s','%s','%s','%s')" % (
teacher_name, teacher_id, teacher_sex, teacher_wx)
try:
cursor.execute(sql)
db.commit()
except:
db.rollback()
print("error")
db.close()
message = "数据插入成功！"
return render_template('index.html', message=message)
def alter_teacher():
db = pymysql.connect(host="localhost",
user="******",
password="******",
db="face_recognition",
port=3306,
charset='utf8')
cursor = db.cursor()
sql = "SELECT t_id FROM teacher"
cursor.execute(sql)
t = cursor.fetchall()
if request.method == "GET":
return render_template("alter_teacher.html", t=t)
else:
teacher_id = request.form.get('teacher_id')
alter_information = request.form.get('alter_information')
information = request.form.get('information')
if alter_information == "教师姓名":
sql = "UPDATE teacher SET t_name='%s' WHERE t_id='%s'" % (
information, teacher_id)
elif alter_information == "教师微信id":
sql = "UPDATE teacher SET t_wx='%s' WHERE t_id='%s'" % (
information, teacher_id)
elif alter_information == "教师性别":
sql = "UPDATE teacher SET t_sex='%s' WHERE t_id='%s'" % (
information, teacher_id)
print(sql)
try:
cursor.execute(sql)
db.commit()
except:
print('error')
db.rollback()
db.close()
message = "修改教师信息成功"
return render_template("index.html", message=message)
def upload():
if request.method == 'POST':
#获取文章，加入数据库
xuehao = request.form['xuehao']
title = request.form['title']
article = request.form['article']
# SQL 插入语句
sql_insert = "INSERT INTO opus(xuehao,title,article) VALUES ('%s','%s', '%s')"
try:
# 执行sql语句
cursor.execute(sql_insert % (xuehao, title,article ))
# 执行sql语句
db.commit()
return '上传成功'
except:
# 发生错误时回滚
db.rollback()
return 'no'
else:
return '404'
def scores():
#鉴权
payload_or_error = Auth.identify(Auth, request)
if payload_or_error[0]:
user_info = payload_or_error[1]['data']
user_id = user_info['id']
# print(user_info)
if not user_info['catagory_name'] == "Teacher":
return jsonify(error_response(403, "没有权限"))
else:
return jsonify(error_response(401, payload_or_error[1]))
if request.method == 'GET':
openc_id = request.args.get('openc_id', None)
if not openc_id:
return jsonify(error_response(400, 'lack of params'))
sql = "select * from ((select elective_student_id,id,elevtive_regular_grade,elevtive_final_exam_grade,elevtive_total_grade" \
" from Elective where elective_openc_id = '%s')A " \
"left join (select student_no,student_name,student_sex,student_grade,student_tel,id from Student) B " \
"on B.id = A.elective_student_id) "%openc_id
# print(sql)
try:
mutex.acquire()
cursor.execute(sql)
results = cursor.fetchall()
tmp = []
for i in results:
tmp.append(gen_scores_dict(i))
mutex.release()
return jsonify(success_response('查询成功', {'scores': tmp}))
except Exception as e:
mutex.release()
print(e)
if request.method == 'PUT':
_data = request.get_json()
elective_id_list = _data.get("elective_id", None)
elevtive_regular_grade_list = _data.get('elevtive_regular_grade', None)
elevtive_final_exam_grade_list = _data.get('elevtive_final_exam_grade',
None)
if not (elective_id_list and elevtive_regular_grade_list
and elevtive_final_exam_grade_list):
return jsonify(error_response(400, 'lack of params'))
try:
mutex.acquire()
for i in range(len(elective_id_list)):
#查询该课程是否是自己开的
sql="select openc_teacher_id from OpenC where id = " \
"(SELECT elective_openc_id from Elective where id='%s')"%(elective_id_list[i])
cursor.execute(sql)
results = cursor.fetchone()
# print(results)
if user_id != results[0]:
mutex.release()
return jsonify(error_response(403, '没有权限'))
sql="UPDATE Elective set elevtive_regular_grade=%s,elevtive_final_exam_grade=%s " \
"where id=%s"%(float(elevtive_regular_grade_list[i]),float(elevtive_final_exam_grade_list[i]),float(elective_id_list[i]))
cursor.execute(sql)
db.commit()
mutex.release()
return jsonify(success_response('输入成功'))
except:
mutex.release()
print("Error: unable to fetch data")
return jsonify(error_response('DB发生错误'))
def semester():
#鉴权
payload_or_error = Auth.identify(Auth, request)
if payload_or_error[0]:
user_info = payload_or_error[1]['data']
user_id = user_info['id']
# print(user_info)
if not user_info['catagory_name'] == "Administrator":
return jsonify(error_response(403, "没有权限"))
else:
return jsonify(error_response(401, payload_or_error[1]))
if request.method == 'POST':
semester_name = request.form.get('semester_name', None)
if not semester_name:
return jsonify(error_response(400, 'lack of params'))
try:
mutex.acquire()
sql = "insert into Semester(semester_name, semester_available,semester_able)values ('%s',0,0)" % (
semester_name)
cursor.execute(sql)
db.commit()
mutex.release()
return jsonify(success_response('增加semester成功'))
except Exception as e:
mutex.release()
print(e)
if request.method == 'PUT':
semester_id = request.form.get('semester_id', None)
semester_name = request.form.get('semester_name', '')
semester_available = request.form.get('semester_available', 0)
semester_able = request.form.get('semester_able', 0)
if not semester_id:
return jsonify(error_response(400, 'lack of params'))
try:
mutex.acquire()
sql = "update Semester set semester_name='%s',semester_available=%s,semester_able=%s where id=%s" % (
semester_name, semester_available, semester_able, semester_id)
cursor.execute(sql)
db.commit()
mutex.release()
return jsonify(success_response('修改semester成功'))
except Exception as e:
mutex.release()
print(e)
if request.method == 'DELETE':
semester_id = request.form.get('semester_id', None)
if not semester_id:
return jsonify(error_response(400, 'lack of params'))
try:
sql = "select * from OpenC where openc_semester_id = '%s'" % (
int(semester_id))
mutex.acquire()
cursor.execute(sql)
results = cursor.fetchone()
if results:
mutex.release()
return jsonify(error_response('400', '存在openc，该semester不能删除'))
sql = "delete from Semester where id='%s'" % (semester_id)
cursor.execute(sql)
db.commit()
mutex.release()
return jsonify(success_response('删除semester成功'))
except Exception as e:
mutex.release()
print(e)
return jsonify(error_response(400, 'DB发生错误'))
def course():
#鉴权
payload_or_error = Auth.identify(Auth, request)
if payload_or_error[0]:
user_info = payload_or_error[1]['data']
user_id = user_info['id']
# print(user_info)
if not user_info['catagory_name'] == "Administrator":
return jsonify(error_response(403, "没有权限"))
else:
return jsonify(error_response(401, payload_or_error[1]))
# 获取course
if request.method == 'GET':
course_no = request.args.get('course_no', '') + '%'
try:
mutex.acquire()
sql = "select id,course_name,course_credit,course_hour,course_final_exam_precentage,course_catagory,course_no from Course where course_no like '%s'" % (
course_no)
cursor.execute(sql)
results = cursor.fetchall()
tmp = []
for i in results:
tmp.append(gen_course_info_dict(i))
mutex.release()
return jsonify(success_response('查询成功', {'courseinfo': tmp}))
except Exception as e:
mutex.release()
print(e)
if request.method == 'POST':
course_no = request.form.get('course_no', '')
course_name = request.form.get('course_name', '')
course_credit = request.form.get('course_credit', '')
course_hour = request.form.get('course_hour', '')
department_id = request.form.get('department_id', '')
course_final_exam_precentage = request.form.get(
'course_final_exam_precentage', '')
course_grade = request.form.get('course_grade', '')
course_catagory = request.form.get('course_catagory', '')
try:
mutex.acquire()
sql = "select * from Course where course_no = '%s'" % (course_no)
cursor.execute(sql)
results = cursor.fetchone()
if results:
mutex.release()
return jsonify(error_response('400', '课程号已存在'))
sql = "insert into Course(course_name, course_credit, course_hour, course_department_id, " \
"course_final_exam_precentage, course_grade, course_catagory, course_no) " \
"values ('%s','%s','%s','%s','%s','%s','%s','%s')"\
%(course_name,course_credit,course_hour,department_id,course_final_exam_precentage,course_grade,course_catagory,course_no)
cursor.execute(sql)
db.commit()
mutex.release()
return jsonify(success_response('增加course成功'))
except Exception as e:
mutex.release()
print(e)
if request.method == "DELETE":
course_id = request.form.get('course_id', '')
try:
mutex.acquire()
sql = "select * from OpenC where openc_course_id = '%s'" % (
course_id)
cursor.execute(sql)
results = cursor.fetchone()
if results:
mutex.release()
return jsonify(error_response('400', '存在openc，该course不能删除'))
sql = "delete from Course where id ='%s'" % course_id
cursor.execute(sql)
db.commit()
mutex.release()
return jsonify(success_response('删除course成功'))
except Exception as e:
mutex.release()
print(e)
return jsonify(error_response(400, 'DB发生错误'))
def openc():
#鉴权
payload_or_error = Auth.identify(Auth, request)
if payload_or_error[0]:
user_info = payload_or_error[1]['data']
user_id = user_info['id']
# print(user_info)
if not user_info['catagory_name'] == "Administrator":
return jsonify(error_response(403, "没有权限"))
else:
return jsonify(error_response(401, payload_or_error[1]))
if request.method == 'POST':
course_id = request.form.get("course_id", None)
teacher_id = request.form.get('teacher_id', '')
openc_time = request.form.get('openc_time', '')
openc_address = request.form.get('openc_address', '')
openc_maxnum = int(request.form.get('openc_maxnum', ''))
openc_QA_time = request.form.get('openc_QA_time', '')
openc_QA_address = request.form.get('openc_QA_address', '')
try:
mutex.acquire()
# 获取当前学期id
sql = "SELECT id from Semester where semester_available=TRUE "
cursor.execute(sql)
semester_id = cursor.fetchone()[0]
#开课
sql="INSERT INTO OpenC(openc_course_id, openc_semester_id, openc_teacher_id, " \
"openc_time,openc_address,openc_curnum, openc_maxnum, openc_available," \
" openc_QA_time, openc_QA_address) values ('%s','%s','%s','%s','%s',%s,%s,'%s','%s','%s') "\
%(course_id,semester_id,teacher_id,openc_time,openc_address,0,openc_maxnum,1,openc_QA_time,openc_QA_address)
print(sql)
cursor.execute(sql)
db.commit()
mutex.release()
return jsonify(success_response('开课成功'))
except Exception as e:
mutex.release()
print(e)
if request.method == 'DELETE':
openc_id = request.form.get("openc_id", None)
try:
#删课
sql = "select * from Elective where elective_openc_id='%s'" % openc_id
mutex.acquire()
cursor.execute(sql)
results = cursor.fetchone()
if results:
mutex.release()
return jsonify(error_response('400', '存在已选此课的学生无法删除'))
sql = "DELETE from OpenC where id='%s'" % openc_id
cursor.execute(sql)
db.commit()
mutex.release()
return jsonify(success_response('删课成功'))
except Exception as e:
mutex.release()
print(e)
return jsonify(error_response(400, 'DB发生错误'))
def student_info():
#鉴权
payload_or_error = Auth.identify(Auth, request)
if payload_or_error[0]:
user_info = payload_or_error[1]['data']
user_id = user_info['id']
# print(user_info)
if not user_info['catagory_name'] == "Administrator":
return jsonify(error_response(403, "没有权限"))
else:
return jsonify(error_response(401, payload_or_error[1]))
# 学号工号模糊查询
if request.method == 'GET':
catagory_name = request.args.get('catagory_name', None)
no = request.args.get('no', '') + '%'
if not catagory_name and catagory_name not in ['Student', 'Teacher']:
catagory_name = 'Student'
if catagory_name == 'Student':
name = 'student_no'
else:
name = 'teacher_no'
sql = "SELECT * FROM %s left join (select id,department_name from Department) A " \
"on A.id=%s.department_id where %s.%s like '%s'" % (catagory_name, catagory_name, catagory_name,name, no)
print(sql)
try:
mutex.acquire()
cursor.execute(sql)
results = cursor.fetchall()
tmp = []
if catagory_name == "Student":
for i in results:
user_info_dict = gen_stu_info_dict(i[1:])
tmp.append(user_info_dict)
elif catagory_name == "Teacher":
for i in results:
user_info_dict = gen_teacher_info_dict(i[1:])
tmp.append(user_info_dict)
# print(tmp)
mutex.release()
return jsonify(success_response('查询成功', {'userinfo': tmp}))
except Exception as e:
mutex.release()
print(e)
return jsonify(error_response(400, '发生错误'))
#新增教师学生
if request.method == 'POST':
catagory_id = int(request.form.get('catagory_id'))
if not catagory_id:
return jsonify(error_response(400, 'lack of params'))
elif catagory_id == 2: #插入教师信息
teacher_no = request.form.get("teacher_no", '')
teacher_name = request.form.get("teacher_name", '')
teacher_sex = request.form.get("teacher_sex", '')
teacher_birthtime = request.form.get("teacher_birthtime",
'') #形如1998-1-1
teacher_education = request.form.get("teacher_education", '')
teacher_salary = request.form.get("teacher_salary", '')
department_id = request.form.get("department_id", '')
teacher_workaddress = request.form.get("teacher_workaddress", '')
password = request.form.get("password", '')
try:
# 查询是否有此工号
sql = "SELECT * from Teacher where teacher_no='%s'" % teacher_no
mutex.acquire()
cursor.execute(sql)
results = cursor.fetchone()
if results != None:
return jsonify(error_response('400', '工号已存在'))
sql = "INSERT INTO Teacher(teacher_no,teacher_name,teacher_sex,teacher_birthtime,teacher_education,teacher_salary,department_id,teacher_workaddress) " \
"values ('%s','%s','%s','%s','%s','%s','%s','%s') " % (
teacher_no, teacher_name, teacher_sex, teacher_birthtime,
teacher_education, teacher_salary,department_id,teacher_workaddress)
print(sql)
cursor.execute(sql)
sql="insert into User(user_catagoty_id, user_catagoty_id_class_id, user_name, user_password) " \
"value ('%s','%s','%s','%s')"%(catagory_id,cursor.lastrowid,teacher_no,password)
cursor.execute(sql)
db.commit()
mutex.release()
return jsonify(success_response('新增成功'))
except Exception as e:
mutex.release()
print(e)
elif catagory_id == 3:
student_no = request.form.get("student_no", '')
student_name = request.form.get('student_sex', '')
student_sex = request.form.get('student_sex', '')
student_grade = request.form.get('student_grade', '')
student_class = request.form.get('student_class', '')
student_birthtime = request.form.get('student_birthtime', '')
student_birthaddress = request.form.get('student_birthaddress', '')
student_tel = request.form.get('student_tel', '')
department_id = request.form.get('department_id', '')
password = request.form.get('password', '')
try:
# 查询是否有此工号
sql = "SELECT * from Student where student_no='%s'" % student_no
mutex.acquire()
cursor.execute(sql)
results = cursor.fetchone()
if results != None:
return jsonify(error_response('400', '学号已存在'))
sql = "INSERT INTO Student(student_no,student_name,student_sex,student_grade,student_class,student_birthtime," \
"student_birthaddress,student_tel,department_id) " \
"values ('%s','%s','%s','%s','%s','%s','%s','%s','%s') " % (
student_no, student_name, student_sex, student_grade,
student_class, student_birthtime,student_birthaddress,student_tel,department_id)
print(sql)
cursor.execute(sql)
sql="insert into User(user_catagoty_id, user_catagoty_id_class_id, user_name, user_password) " \
"value ('%s','%s','%s','%s')"%(catagory_id,cursor.lastrowid,student_no,password)
cursor.execute(sql)
db.commit()
mutex.release()
return jsonify(success_response('新增成功'))
except Exception as e:
mutex.release()
print(e)
else:
return jsonify(error_response(400, 'error params'))
#删除用户
if request.method == 'DELETE':
no = request.form.get("no", None)
catagory_id = request.form.get("catagory_id", None)
if not (no and catagory_id):
return jsonify(error_response(400, 'lack of params'))
try:
if catagory_id == 2:
sql = "DELETE from Teacher where teacher_no='%s' " % no
else:
sql = "DELETE from Student where student_no='%s' " % no
mutex.acquire()
cursor.execute(sql)
sql = "delete from User where user_name='%s'" % no
cursor.execute(sql)
db.commit()
mutex.release()
return jsonify(success_response('删除成功'))
except Exception as e:
mutex.release()
print(e)
return jsonify(error_response(400, 'DB发生错误'))
def courses():
payload_or_error = Auth.identify(Auth, request)
if payload_or_error[0]:
user_info = payload_or_error[1]['data']
user_id = user_info['id']
print(user_info)
if not (user_info['auth']['catagoty_elective_authority']
and user_info['catagory_name'] == "Student"):
return jsonify(error_response(403, "没有权限"))
else:
return jsonify(error_response(401, payload_or_error[1]))
if request.method == 'GET':
sql = "select * from ((select OpenC.id,openc_time,openc_address,openc_curnum,openc_maxnum,openc_available,openc_QA_time,course_no,course_name," \
"course_credit,course_hour,openc_teacher_id " \
"from OpenC left join Course on openc_course_id=Course.id " \
"where openc_semester_id=(select id from Semester where semester_available = true) and " \
"OpenC.id in (select elective_openc_id from Elective where elective_student_id='%s'))A left join " \
"(select id,teacher_name from Teacher )B on A.openc_teacher_id=B.id)"%(user_id)
# print(sql)
try:
mutex.acquire()
cursor.execute(sql)
results = cursor.fetchall()
mutex.release()
tmp = []
for i in results:
tmp.append(gen_openC_dict(i))
return jsonify(success_response('查询成功', {'Elective': tmp}))
except Exception as e:
mutex.release()
print(e)
##判断是否可选课
sql = "select * from Semester where semester_available=true and semester_able=true"
mutex.acquire()
cursor.execute(sql)
result = cursor.fetchone()
mutex.release()
if not result:
return jsonify(error_response(400, '选课时间未到。'))
openc_id = request.form.get("openc_id", None)
if not openc_id:
return jsonify(error_response(400, 'lack of params'))
if request.method == 'POST':
try:
sql = "select * from OpenC where id ='%s' and openc_curnum<openc_maxnum and openc_available=TRUE " % openc_id
mutex.acquire()
cursor.execute(sql)
flag = cursor.fetchone()
if not flag:
mutex.release()
return jsonify(error_response(400, '选课失败，请确定课程处于能被选状态'))
real_time = flag[4]
#查询是否已选此课程
sql="select course_no from Course where id in (select openc_course_id from OpenC where id in " \
"(select elective_openc_id from Elective where elective_student_id='%s') " \
"and openc_semester_id in (select id from Semester where semester_available = true))"%(user_id)
cursor.execute(sql)
courses_no_tuple = cursor.fetchall()
# print(courses_no_tuple)
sql = "select course_no from Course where id in (select openc_course_id from OpenC where id = %s " \
"and openc_semester_id in (select id from Semester where semester_available = true))" % (openc_id)
cursor.execute(sql)
courses_no = cursor.fetchone()
# print(courses_no)
for i in courses_no_tuple:
if i[0] == courses_no[0]:
mutex.release()
return jsonify(error_response(400, '已选此门课程'))
sql = "select openc_time from OpenC where id in " \
"(select elective_openc_id from Elective where elective_student_id='%s')" \
"and openc_semester_id in (select id from Semester where semester_available = true)"%(user_id)
cursor.execute(sql)
time_list = cursor.fetchall()
if not judge_time(real_time, time_list):
mutex.release()
return jsonify(error_response(400, '选课时间冲突'))
#选课
sql = "INSERT INTO Elective(elective_student_id,elective_openc_id) values ('%s','%s') " % (
user_id, openc_id)
cursor.execute(sql)
db.commit()
mutex.release()
return jsonify(success_response('选课成功'))
except Exception as e:
mutex.release()
print(e)
if request.method == 'DELETE':
try:
# 查询是否已选此课程
sql="SELECT * from Elective left join OpenC on OpenC.id=elective_openc_id where openc_semester_id in (select id from Semester where semester_available = true) " \
"and elective_student_id = '%s' and elective_openc_id = '%s'"%(user_id,openc_id)
mutex.acquire()
cursor.execute(sql)
results = cursor.fetchone()
if results == None:
mutex.release()
return jsonify(error_response(400, '未选此课程'))
#退课
sql = "DELETE from Elective where elective_student_id='%s' and elective_openc_id='%s'" % (
user_id, openc_id)
cursor.execute(sql)
db.commit()
mutex.release()
return jsonify(success_response('退课成功'))
except Exception as e:
mutex.release()
print(e)
return jsonify(error_response(400, 'DB发生错误'))