def index():
male_num = "select count(Sno) from S where Sgender='男'"
female_num = "select count(Sno) from S where Sgender='女'"
cursor.execute(male_num)
male_num = cursor.fetchall()[0][0]
cursor.execute(female_num)
female_num = cursor.fetchall()[0][0]
dict = {'male_num': male_num, 'female_num': female_num}
return render_template('cms/index.html', **dict)
def teacher_replace_judge():
difficulty = request.form.get('difficulty')
html_type = request.form.get('html_type')
selected_ids = request.form.get('selected_ids')
db_type = request.form.get('db_type')
print('[{}]'.format(teacher_replace_judge.__name__), difficulty, html_type,
db_type, selected_ids)
selected_ids = [int(x) for x in json.loads(selected_ids)]
sql = 'SELECT * FROM {} WHERE q_type like %s and q_difficulty = %s'
replace_list = []
if html_type == 'judge':
cursor.execute(sql.format(judge_question_table), (db_type, difficulty))
elif html_type == 'choice':
cursor.execute(sql.format(choice_question_table),
(db_type, difficulty))
elif html_type == 'subjective':
cursor.execute(sql.format(subjective_question_table),
(db_type, difficulty))
for x in cursor.fetchall():
if x.get('q_id') not in selected_ids:
replace_list.append(x)
print('[{}]'.format(teacher_replace_judge.__name__), replace_list)
print('[{}]'.format(teacher_replace_judge.__name__),
[x.get('q_id') for x in replace_list])
return jsonify({'success': 1, 'replace_list': replace_list[0:10]})
def login_session():
if request.method == 'POST':
# Valores obtenidos por el metodo
rut_entrada = request.form['rut']
contraseña_entrada = request.form['contraseña']
print('usuario: ', rut_entrada)
print('contraseña: ', contraseña_entrada)
# Query donde se obtienen los datos del usuario
query = ('''
SELECT
rut,
contraseña,
credencial,
email
FROM Usuario
WHERE rut = %s;
''')
cursor = connection.cursor()
cursor.execute(query, (rut_entrada, ))
resultado = cursor.fetchall()
# Si los datos para ingresar son incorrectos redirigira al login y enviara un mensaje
if (resultado == []):
flash('El usuario o la contraseña estan mal xD')
return redirect('/victor/login')
# Si la contraseña es incorrecta
elif (contraseña_entrada != resultado[0][1]):
flash('El usuario o la contraseña estan mal xD')
return redirect('/victor/login')
else:
return 'perfil'
return 'OK'
def get_todo_detail(self, todo_id):
sql = "select * from todo where id = %s;"
n = cursor.execute(sql)
if not n:
return None
data = cursor.fetchall()
return TodoModel.load(data)[0]
def get_todo_list(self):
sql = "select * from todo;"
n = cursor.execute(sql)
if not n:
return []
data = cursor.fetchall()
return TodoModel.load(data)
def modifyPwd():
user_id = session.get('user_id')
if user_id is None:
return '请先登录！'
sql = 'SELECT * FROM user WHERE user_id = \'' + user_id + '\' '
cursor.execute(sql)
userdata = cursor.fetchall()
password = userdata[0].get('user_password')
oldpwd = request.form.get('old_pwd')
newpwd = request.form.get('new_pwd')
conpwd = request.form.get('con_pwd')
if password != oldpwd:
return '原密码不正确！'
if newpwd != conpwd:
return '确认密码不一致！'
if len(newpwd) < 8:
return '密码长度不足8位！'
sql = 'update user set user_password = \'' + newpwd + '\' where user_id = \'' + user_id + '\' '
cursor.execute(sql)
if user_id[0] == 'T':
return redirect(url_for('teacher_personal_info'))
else:
return redirect(url_for('student_personal_info'))
def result():
if session.get('user_id'):
sql = 'select * from user_grade where user_id = %s'
cursor.execute(sql, (session.get('user_id'), ))
user_grade = cursor.fetchall()
return render_template('results.html', user_grade=user_grade)
else:
return render_template('temp.html')
def admin():
if session.get('user_id') == 1:
sql = 'select * from user_grade order by user_name,paper_id'
cursor.execute(sql, )
user_grade_data = cursor.fetchall()
return render_template('admin.html', user_grade_data=user_grade_data)
else:
return render_template('temp.html')
def teacher_modify():
print_log('teacher modify', request.method)
std_exam_dict = dict(exam_id='',
exam_title='',
exam_date='',
student_submit_exam_list=[])
std_submit_exam_dict = dict(student_id=0,
student_name='',
submit_time='',
grade=100,
subjective_grade=-1)
exam_list = []
sql = 'SELECT * FROM ' + exam_paper_table + ' WHERE paper_userid=%s'
cursor.execute(sql, session.get('user_id'))
data = cursor.fetchall()
for exam in data:
exam_dict = dict(std_exam_dict)
exam_dict['exam_id'] = exam.get('paper_id')
exam_dict['exam_title'] = exam.get('paper_title')
exam_dict['exam_date'] = exam.get('paper_date')
submit_list = []
sql = 'SELECT * FROM ' + student_exam_log_table + ' INNER JOIN ' + user_table + \
'ON student_exam_log.student_id=user.user_id ' + \
'WHERE paper_id=%s'
cursor.execute(sql, exam.get('paper_id'))
for submit_log in cursor.fetchall():
submit_dict = dict(std_submit_exam_dict)
submit_dict['student_name'] = submit_log.get('user_name')
submit_dict['student_id'] = submit_log.get('user_id')
submit_dict['submit_time'] = submit_log.get('submit_time')
submit_dict['grade'] = submit_log.get('grade')
submit_dict['subjective_grade'] = submit_log.get(
'subjective_grade')
submit_list.append(submit_dict)
exam_dict['student_submit_exam_list'] = submit_list
exam_list.append(exam_dict)
return render_template('teacherModify.html', exam_list=exam_list)
def teacher_personal_info():
user_id = session.get('user_id')
sql = 'SELECT * FROM user WHERE user_id = \'' + user_id + '\' '
user_dict = {'user_id:': '', 'user_name': '', 'user_email': ''}
cursor.execute(sql)
userdata = cursor.fetchall()
u = dict(user_dict)
u['user_id'] = userdata[0].get('user_id')
u['user_name'] = userdata[0].get('user_name')
u['user_email'] = userdata[0].get('user_email')
return render_template('teacher-personInfo.html', person=u)
def studentExam():
print_log('student exam', request.method)
exam_dict = {
'title': '',
'teacher_name': '',
'date': '',
'duration': 0,
'is_open': 0,
'exam_id': -1
}
exam_list = list()
# 该学生关联的老师发布的考试
sql = 'SELECT * FROM ' + \
exam_paper_table + ' INNER JOIN ' + teacher_student_table + ' ON exam_paper.paper_id=teacher_student.paper_id ' + \
'INNER JOIN ' + user_table + ' ON user.user_id=teacher_student.teacher_id ' + \
'WHERE student_id=%s'
cursor.execute(sql, session.get('user_id'))
data = cursor.fetchall()
# 该学生已经完成的考试
sql = 'SELECT paper_id FROM ' + student_exam_log_table + ' WHERE student_id = %s'
cursor.execute(sql, session.get('user_id'))
finished_exam_ids = [x.get('paper_id') for x in cursor.fetchall()]
exam_order = 1
for x in data:
if x.get('paper_id') in finished_exam_ids:
continue
d = dict(exam_dict)
d['title'] = x.get('paper_title')
d['teacher_name'] = x.get('user_name')
d['date'] = x.get('paper_date')
d['duration'] = x.get('paper_time')
d['is_open'] = x.get('paper_open')
d['exam_id'] = x.get('paper_id')
d['order'], exam_order = exam_order, exam_order + 1
exam_list.append(d)
return render_template('studentExam.html', exam_list=exam_list)
def admin_examlist():
exam_list = list()
exam_dict = {
'exam_id:': '',
'exam_title': '',
'teacher': '',
'exam_date': ''
}
sql = 'SELECT paper_userid,paper_title,paper_id,paper_date,user_name,paper_time,paper_class FROM exam_paper inner join user on user_id=paper_userid'
cursor.execute(sql)
data = cursor.fetchall()
for x in data:
e = dict(exam_dict)
e['exam_id'] = x.get('paper_id')
e['exam_title'] = x.get('paper_title')
e['teacher'] = x.get('user_name')
e['exam_date'] = x.get('paper_date')
e['exam_duration'] = x.get('paper_time')
e['exam_class'] = x.get('paper_class')
e['exam_userid'] = x.get('paper_userid')
exam_list.append(e)
return render_template('admin-examlist.html', exam_list=exam_list)
def exam():
if session.get('user_id'):
global paper_id, user_name
sql = 'select count(*) as total from docx'
cursor.execute(sql)
count = cursor.fetchone().get('total')
sql = 'select * from questions where paper_id = %s'
random_paper_id = random.randint(1, count)
paper_id = random_paper_id
cursor.execute(sql, (paper_id, ))
res = cursor.fetchall()
user_id = session.get('user_id')
sql = 'select * from users where id=%s'
cursor.execute(sql, user_id)
user_name = cursor.fetchone().get('realname')
return render_template('exam.html', question=res)
else:
return render_template('temp.html')
def teacher_result():
# std_student_dict = {'user_name': '', 'student_id': '', 'grade': 0}
std_paper_dict = {
'name': '',
'paper_id': -1,
'student_num': '',
'avg_grade': 0,
'student_list': [],
'grade_segment': {}
}
paper_list = []
# 查询该教师发布的考试
sql = 'SELECT paper_id, paper_title FROM ' + exam_paper_table + ' WHERE paper_userid=%s'
cursor.execute(sql, session.get('user_id'))
published_exams = cursor.fetchall()
for exam in published_exams:
paper_dict = dict(std_paper_dict)
# 已完成该考试的学生列表
student_list = sql_helper.get_students_by_paperid(exam.get('paper_id'))
# 如果 student_list 为空，没有学生参加考试，则不显示
if student_list is None or len(student_list) == 0:
continue
paper_dict['name'] = exam.get('paper_title')
paper_dict['paper_id'] = exam.get('paper_id')
paper_dict['student_num'] = len(student_list)
paper_dict['avg_grade'] = sum(
[int(x.get('grade'))
for x in student_list]) / paper_dict['student_num']
paper_dict['student_list'] = student_list
paper_dict['grade_segment'] = common_helper.get_grade_segment(
student_list)
paper_list.append(paper_dict)
return render_template('teacherResult.html', paper_list=paper_list)
def student_history():
print_log('student history', request.method)
student_id = session.get('user_id')
history_list = list()
std_dict = dict({
'order': 0,
'title': '',
'teacher': '',
'date': '',
'duration': '',
'grade': 0,
'full_grade': 100,
'subjective_grade': 0,
'exam_id': -1
})
# 三个表进行 INNER JOIN, 👴就是一个 sql 工具人
sql = 'SELECT student_exam_log.paper_id, grade, full_grade, subjective_grade, paper_title, paper_time, paper_date, user_name FROM ' + \
'(student_exam_log INNER JOIN exam_paper ON student_exam_log.`paper_id`=exam_paper.`paper_id`) ' + \
' INNER JOIN `user` ON user.`user_id`=exam_paper.`paper_userid` ' + \
' WHERE student_exam_log.student_id = %s'
cursor.execute(sql, student_id)
data = cursor.fetchall()
order = 1
for x in data:
d = dict(std_dict)
d['order'], order = order, order + 1
d['title'] = x.get('paper_title')
d['teacher'] = x.get('user_name')
d['date'] = x.get('paper_date').date()
d['duration'] = x.get('paper_time')
d['grade'] = x.get('grade')
d['full_grade'] = x.get('full_grade')
d['exam_id'] = x.get('paper_id')
d['subjective_grade'] = x.get(
'subjective_grade') if x.get('subjective_grade') != -1 else '未发布'
history_list.append(d)
return render_template('studentHistory.html', exam_history=history_list)
def teacherIndex():
print_log('teacherIndex', request.method)
teacher_id = session.get('user_id')
if teacher_id is None:
return '请先登录!'
elif session.get('user_type') == 'STUDENT':
return '无权访问教师页面！'
std_dict = dict({
'title': '',
'description': '',
'is_open': 0,
'exam_id': 0,
'day': '',
'month': '',
'year': ''
})
print_log('teacherIndex', str(std_dict))
exam_list = []
sql = 'select paper_id, paper_title, paper_desc, paper_date, paper_time, paper_open from ' + \
exam_paper_table + ' where paper_userid=%s'
cursor.execute(sql, teacher_id)
results = cursor.fetchall()
for x in results:
std_dict['title'] = x.get('paper_title')
std_dict['description'] = x.get('paper_desc')
paper_date = x.get('paper_date')
std_dict['day'] = paper_date.day
std_dict['month'] = common_helper.month_int2str(paper_date.month)
std_dict['year'] = paper_date.year
std_dict['duration'] = x.get('paper_time')
std_dict['time'] = paper_date.time()
std_dict['is_open'] = '是' if int(x.get('paper_open')) == 1 else '否'
std_dict['exam_id'] = x.get('paper_id')
exam_list.append(dict(std_dict))
return render_template('teacherIndex.html', exam_list=exam_list)
def studentIndex():
print_log('studentIndex', request.method)
student_id = session.get('user_id')
if student_id is None:
return '请先登录!'
std_dict = dict({
'title': '',
'description': '',
'day': '',
'year': '',
'month': '',
'duration': '',
'time': '',
'teacher': ''
})
print_log('studentIndex', str(std_dict))
exam_list = []
sql = 'SELECT * FROM ' + \
exam_paper_table + ' INNER JOIN ' + teacher_student_table + ' ON exam_paper.paper_id=teacher_student.paper_id ' + \
'INNER JOIN ' + user_table + ' ON user.user_id=teacher_student.teacher_id ' + \
'WHERE student_id=%s'
print_log('student index', sql)
cursor.execute(sql, session.get('user_id'))
results = cursor.fetchall()
for x in results:
std_dict['title'] = x.get('paper_title')
std_dict['description'] = x.get('paper_desc')
date = x.get('paper_date')
std_dict['day'] = date.day
std_dict['month'] = common_helper.month_int2str(date.month)
std_dict['year'] = date.year
std_dict['duration'] = x.get('paper_time')
std_dict['time'] = date.time()
std_dict['teacher'] = x.get('user_name')
exam_list.append(dict(std_dict))
return render_template('studentIndex.html', exam_list=exam_list)
def editar(rut=None):
if request.method == 'GET':
if rut:
query = ('''SELECT * FROM Usuario WHERE rut= %s''')
cursor = db.cursor()
cursor.execute(query, (rut, ))
resultado = cursor.fetchall()
print(resultado[0])
if (resultado == []):
return render_template("/pablo/editar_usuario.html",
msg='No existe ese alumno')
elif (resultado[0][0] == rut):
credencial = 'Alumno' if resultado[0][
1] == 3 else 'Profesor' if resultado[0][
1] == 2 else 'Administrador' if resultado[0][
1] == 1 else None
return render_template("/pablo/editar_usuario.html",
datos=resultado[0],
credencial=credencial)
else:
return render.template("/pablo/editar_usuario.html",
msg='Error en el rut')
elif request.method == 'POST':
datos_usuario = request.form.to_dict()
def student():
resultHasGrade = False # 查询结果是否含有成绩的标志
# 查询出课程名
course = "select Cname from C"
cursor.execute(course)
courses = cursor.fetchall() # (('高等数学',), ('线性代数',), ('数据库原理',)
# 需要把courses转换成字符串 "高等数学，线性代数，数据库原理"
courses = list(map(lambda x: x[0], courses))
courses = "'{}'".format(",".join(courses))
# 查询出班级
Sclass = "select distinct Sclass from S"
cursor.execute(Sclass)
Sclass = cursor.fetchall()
# 同理，班级也要转换格式
Sclass = list(map(lambda x: x[0], Sclass))
Sclass = "'{}'".format(",".join(Sclass))
if request.method == 'GET':
student = "select * from S"
cursor.execute(student)
students = cursor.fetchall()
else:
sql = "select * from S "
gender = request.values.get('Sgender')
if gender != '男' and gender != '女': # 如果未选择学生性别，则把性别设为空
sql = sql + "where 1=1"
else:
sql = sql + "where Sgender='{}'".format(gender)
option = request.values.get('provinces') # 得到选择查询的类别
if option == '0': # 按照学号查询
SnoInput = request.values.get('textInput')
sql = sql + " and SNO='{}'".format(SnoInput)
elif option == '1': # 按照班级查询
Sclass = request.values.get('city')
sql = sql + " and Sclass='{}'".format(Sclass)
elif option == '2': # 按照成绩查询
gradeMin = request.values.get('textInput') # 分数下线
gradeMax = request.values.get('low') # 分数上线
subject = request.values.get('city') # 学科
CNO_sql = "select CNO from C where Cname='{}'".format(subject)
cursor.execute(CNO_sql)
CNO = cursor.fetchone()[0]
sql = """
select S.SNO, S.Sname, S.Sgender, S.Sclass, C.Cname, SC.grade
from S, C, SC
where S.SNO=SC.SNO and C.CNO=SC.CNO and C.CNO='{}' and SC.grade>={} and SC.grade<={}
""".format(CNO, gradeMin, gradeMax)
resultHasGrade = True
elif option == '3': # 按照姓名查询
Sname = request.values.get('textInput')
sql = sql + " and Sname='{}'".format(Sname)
elif option == '4': # 按照选修课查询
subject = request.values.get('city')
CNO_sql = "select CNO from C where Cname='{}'".format(subject)
cursor.execute(CNO_sql)
CNO = cursor.fetchone()[0]
sql = """
select S.SNO, S.Sname, S.Sgender, S.Sclass, C.Cname, SC.grade
from S, C, SC
where S.SNO=SC.SNO and C.CNO=SC.CNO and C.CNO='{}'
""".format(CNO)
resultHasGrade = True
cursor.execute(sql)
students = cursor.fetchall()
dict = {
'results': students,
'courses': courses,
'Sclass': Sclass,
'flag': resultHasGrade
}
return render_template('cms/student.html', **dict)
def teacher():
# 查询出老师的专业/系
Tdepts = "select distinct depart from T"
cursor.execute(Tdepts)
Tdepts = cursor.fetchall() # (('物联网',), ('计算机',), ('微电子',))
Tdepts = list(map(lambda x: x[0], Tdepts))
Tdepts = "'{}'".format(",".join(Tdepts))
# 查询出课程名
course = "select Cname from C"
cursor.execute(course)
courses = cursor.fetchall() # (('高等数学',), ('线性代数',), ('数据库原理',)
# 需要把courses转换成字符串 "高等数学，线性代数，数据库原理"
courses = list(map(lambda x: x[0], courses))
courses = "'{}'".format(",".join(courses))
studentGradeSql = '' # 查询学生成绩的sql
studentGrade = ()
courseGradeChart = [] # 成绩 作图使用
SnameChart = [] # 学生姓名作图使用
courseNameChart = "" # 课程名称 作图使用
TnameFlag = False # 是否显示教师姓名
if request.method == 'GET':
sql = "select * from T"
cursor.execute(sql)
results = cursor.fetchall()
else:
sql = "select * from T"
Tgender = request.values.get("Tgender")
if Tgender != '男' and Tgender != '女':
sql = sql + " where 1=1"
else:
sql = sql + " where Tgender='{}'".format(Tgender)
option = request.values.get("provinces2") # 获得选取的查询方式
if option == '1': # 按照姓名查找
Tname = request.values.get("inputLow")
sql = sql + " and Tname='{}'".format(Tname)
elif option == '2': # 按照工号查找
TNO = request.values.get("inputLow")
radio = request.values.get("radio")
if radio == '1': # 选择 查询学生成绩
studentGradeSql = """
select S.SNO, S.Sname, C.Cname, SC.grade
from S, C, SC, TC
where S.SNO=SC.SNO and C.CNO=SC.CNO and TC.CNO=SC.CNO and TC.TNO='{}'
order by SC.grade desc
""".format(TNO)
cursor.execute(studentGradeSql)
studentGrade = cursor.fetchall()
courseGradeChart = list(map(lambda x: int(x[3]),
studentGrade)) # 把学生成绩放进作图使用的成绩列表里
SnameChart = list(map(lambda x: x[1],
studentGrade)) # 把学生姓名放进作图使用的姓名列表里
courseNameChart = "'{}'".format(
studentGrade[0][2]) # 作图使用的课程名称
print(courseGradeChart)
print(SnameChart)
print(courseNameChart)
sql = sql + " and TNO='{}'".format(TNO)
elif option == '3': # 按照课程查找
subject = request.values.get("city2")
radio = request.values.get("radio")
if radio == '1':
studentGradeSql = """
select S.SNO, S.Sname, C.Cname, SC.grade,T.Tname
from S, C, SC, TC, T
where S.SNO=SC.SNO and C.CNO=SC.CNO and TC.CNO=SC.CNO and T.TNO=TC.TNO and C.Cname='{}'
order by SC.grade desc
""".format(subject)
TnameFlag = True # 显示出教师的姓名
cursor.execute(studentGradeSql)
studentGrade = cursor.fetchall()
courseGradeChart = list(map(lambda x: int(x[3]),
studentGrade)) # 把学生成绩放进作图使用的成绩列表里
SnameChart = list(map(lambda x: x[1],
studentGrade)) # 把学生姓名放进作图使用的姓名列表里
courseNameChart = "'{}'".format(
studentGrade[0][2]) # 作图使用的课程名称
if Tgender == '男' or Tgender == '女':
sql = """
select * from T where TNO in(
select TNO from TC where CNO in(
select CNO from C where Cname='{}'
)) and Tgender='{}'
""".format(subject, Tgender)
else:
sql = """
select * from T where TNO in(
select TNO from TC where CNO in(
select CNO from C where Cname='{}'
))
""".format(subject)
elif option == '4': # 按照部门查找
dept = request.values.get("city2")
sql = sql + " and depart='{}'".format(dept)
elif option == '6': # 按照是否授课查找
dept = request.values.get("city2")
radio = request.values.get("radio")
sql = sql + " and depart='{}' and stat='{}'".format(dept, radio)
print(sql)
cursor.execute(sql)
results = cursor.fetchall()
dict = {
'results': results, # 教师信息查询结果（必有）
'Tdepts': Tdepts, # 教师专业/系/部门（必有）
'courses': courses, # 所有课程名称 （必有）
'studentGrade': studentGrade, # 学生成绩（可能为空）
'TnameFlag': TnameFlag, # 是否显示教师姓名
'courseGradeChart': courseGradeChart, # 作图使用的成绩列表
'SnameChart': SnameChart, # 作图使用的姓名列表
'courseNameChart': courseNameChart # 作图使用的课程名称
}
return render_template('cms/teacher.html', **dict)
def get_questions():
current_year = str(datetime.now().year)
import admin_helper
title_list = admin_helper.admin_get_questions_type()
keyword = request.args.get('keyword')
print_log('get questions', str(keyword))
if keyword is None or keyword == '':
return render_template('teacherQuestion.html',
title_list=title_list,
keyword='请选择',
xuanze=0,
panduan=0,
jianda=0,
nanti3=0,
nanti2=0,
nanti1=0)
xuanze = int(request.args.get('xuanze'))
panduan = int(request.args.get('panduan'))
jianda = int(request.args.get('jianda'))
nanti1 = int(request.args.get('nanti1'))
nanti2 = int(request.args.get('nanti2'))
nanti3 = int(request.args.get('nanti3'))
update_sql = 'UPDATE {} SET q_year=' + current_year + ', q_counter=q_counter+1 WHERE q_id=%s'
sql = 'SELECT * FROM {} WHERE q_type like %s'
# 先选出所有题目
cursor.execute(sql.format(choice_question_table), keyword)
choice_list = admin_helper.select_questions_strategy(
cursor.fetchall(), xuanze, nanti1)
cursor.execute(sql.format(judge_question_table), keyword)
judge_list = admin_helper.select_questions_strategy(
cursor.fetchall(), panduan, nanti2)
cursor.execute(sql.format(subjective_question_table), keyword)
subjective_list = admin_helper.select_questions_strategy(
cursor.fetchall(), jianda, nanti3)
# 修改q_year, q_counter
# for x in choice_list:
# cursor.execute(update_sql.format(choice_question_table), x.get('q_id'))
#
# for x in judge_list:
# cursor.execute(update_sql.format(judge_question_table), x.get('q_id'))
# print(update_sql.format(subjective_question_table))
# for x in subjective_list:
# try:
# cursor.execute(update_sql.format(subjective_question_table), x.get('q_id'))
# db_connector.commit()
# except:
# db_connector.rollback()
return render_template('teacherQuestion.html',
choice_list=choice_list,
judge_list=judge_list,
subjective_list=subjective_list,
title_list=title_list,
keyword=keyword,
xuanze=xuanze,
panduan=panduan,
jianda=jianda,
nanti1=nanti1,
nanti2=nanti2,
nanti3=nanti3)
def show_users():
cursor.execute('SELECT name, email FROM user')
users = cursor.fetchall()
return jsonify({'users': users})
def search_question():
desc = request.args.get('keyword')
if desc is None:
desc = ''
print(desc)
choice_list = list()
choice_dict = {
'q_id:': '',
'q_description': '',
'q_answer': '',
'q_value': '',
'q_A': '',
'q_B': '',
'q_C': '',
'q_D': '',
'q_diff': '',
'q_type': ''
}
sql = 'select * from choice_question where q_description like \'%' + desc + '%\' '
cursor.execute(sql)
data = cursor.fetchall()
for x in data:
e = dict(choice_dict)
e['q_id'] = x.get('q_id')
e['q_description'] = x.get('q_description')
e['q_answer'] = x.get('q_answer')
e['q_value'] = x.get('q_value')
e['q_A'] = x.get('q_A')
e['q_B'] = x.get('q_B')
e['q_C'] = x.get('q_C')
e['q_D'] = x.get('q_D')
e['q_diff'] = x.get('q_difficulty')
e['q_type'] = x.get('q_type')
choice_list.append(e)
judge_list = list()
judge_dict = {
'q_id:': '',
'q_description': '',
'q_answer': '',
'q_value': '',
'q_diff': '',
'q_type': ''
}
sql = 'select * from judge_question where q_description like \'%' + desc + '%\' '
cursor.execute(sql)
data = cursor.fetchall()
for x in data:
e = dict(judge_dict)
e['q_id'] = x.get('q_id')
e['q_description'] = x.get('q_description')
e['q_answer'] = x.get('q_answer')
e['q_value'] = x.get('q_value')
e['q_diff'] = x.get('q_difficulty')
e['q_type'] = x.get('q_type')
judge_list.append(e)
subjective_list = list()
subjective_dict = {
'q_id:': '',
'q_description': '',
'q_answer': '',
'q_value': '',
'q_diff': '',
'q_type': ''
}
sql = 'select * from subjective_question where q_description like \'%' + desc + '%\' '
cursor.execute(sql)
data = cursor.fetchall()
for x in data:
e = dict(subjective_dict)
e['q_id'] = x.get('q_id')
e['q_description'] = x.get('q_description')
e['q_answer'] = x.get('q_answer')
e['q_value'] = x.get('q_value')
e['q_diff'] = x.get('q_difficulty')
e['q_type'] = x.get('q_type')
subjective_list.append(e)
return render_template('admin-questionlist.html',
choice_list=choice_list,
judge_list=judge_list,
subjective_list=subjective_list)
def get_logs():
sql = ("SELECT * FROM logs order by created ")
cursor.execute(sql)
result = cursor.fetchall()
for row in result:
print(row)
def get_music_info():
cursor.execute("SELECT * FROM `films`")
data = cursor.fetchall()
return data
def uploadFile():
user_id = session.get('user_id')
if user_id is None:
return '请先登录！'
paper_title = request.form.get('exam-title-input')
paper_desc = request.form.get('exam-desc-input')
paper_time = request.form.get('exam-time-input')
paper_date = request.form.get('exam-date-input')
paper_open = (request.form.get('optionsRadios') == 'open-paper')
paper_file = request.files['exam-file-input']
paper_class = request.form.get('exam-class-input')
print('paper-title', paper_title, type(paper_title))
print('paper-desc', paper_desc, type(paper_desc))
print('paper-time', paper_time, type(paper_time))
print('paper-date', paper_date, type(paper_date))
print('paper-open', paper_open, type(paper_open))
print('paper-file', paper_file, type(paper_file))
print('paper-class', paper_class)
# 上传文件到项目路径下的 upload_path / paper_path
paper_set.save(paper_file,
name=session.get('user_id') + '-' + paper_title + '.xlsx')
# 插入 exam_paper 表
# (paper_title, paper_desc, paper_time, paper_date, paper_open, paper_path, paper_userid)
file_path = path.join(base_path, upload_path, paper_path,
session.get('user_id') + '-' + paper_title + '.xlsx')
sql = 'insert into ' + exam_paper_table + exam_paper_columns + \
'values' + '(%s, %s, %s, %s, %s, %s, %s, %s)'
try:
cursor.execute(sql, (paper_title, paper_desc, paper_time, paper_date,
paper_open, file_path, user_id, paper_class))
db_connector.commit()
except Exception as e:
db_connector.rollback()
# 获取上面新增试卷的ID
sql = 'SELECT max(paper_id) FROM ' + exam_paper_table
cursor.execute(sql)
paper_id = cursor.fetchone().get('max(paper_id)')
print_log('upload files', str(paper_id))
# 在 teacher_student 中建立关联
for c in paper_class.split(';'):
if c != '':
sql = 'SELECT * FROM `user` WHERE user_id LIKE %s'
cursor.execute(sql, c + '%')
students = cursor.fetchall()
for s in students:
print_log('upload files', s.get('user_id'))
sql = 'INSERT INTO ' + teacher_student_table + ' VALUES (%s,%s,%s)'
try:
cursor.execute(sql, (user_id, s.get('user_id'), paper_id))
db_connector.commit()
except:
db_connector.rollback()
# 插入试题数据库
sql_helper.insert_questions(paper_id=paper_id,
paper_path=file_path,
paper_title=paper_title)
return redirect(url_for('teacherIndex'))
