def callback_worker(call): global country, city city_name = [(str(city))] if call.data == "rus": country = 'Россия' msg = bot.send_message(call.message.chat.id, "Из какого ты города?") bot.register_next_step_handler(msg, ask_city) elif call.data == "ukr": country = 'Украина' msg = bot.send_message(call.message.chat.id, "Из какого ты города?") bot.register_next_step_handler(msg, ask_city) elif call.data == "max": cursor.execute( f"SELECT salary_to FROM main_cities WHERE city_name = ?", city_name) result = cursor.fetchone() bot.send_message(call.message.chat.id, "Максимальная ЗП: " + str(result[0])) elif call.data == "min": cursor.execute( f"SELECT salary_from FROM main_cities WHERE city_name = ?", city_name) result = cursor.fetchone() bot.send_message(call.message.chat.id, "Максимальная ЗП: " + str(result[0])) elif call.data == "avg": cursor.execute( f"SELECT avg_salary FROM main_cities WHERE city_name = ?", city_name) result = cursor.fetchone() bot.send_message(call.message.chat.id, "Cредняя ЗП: " + str(round(result[0])))
def submit_paper(): sum = 0 data = request.form['answers'] # for i in range(1,47): # if (dict(data).get('{}'.format(i))): # print(data[i]) dict_data = dict(eval(data)) for i in range(1, 46 * paper_id + 1): if dict_data.get('{}'.format(i)): sql = 'select id,answer,q_value from questions where id = %s and paper_id = %s' cursor.execute(sql, (i, paper_id)) database = cursor.fetchone() trueAnswer = database.get('answer') yourAnser = dict_data.get('{}'.format(i)) if trueAnswer == yourAnser: sum = sum + float(database.get('q_value')) # print('{}'.format(i),cursor.fetchone().get('answer'),dict_data.get('{}'.format(i))) sql = 'select * from docx where id = %s' cursor.execute(sql, (paper_id, )) temp = cursor.fetchone() paper_name = temp.get('paper_name') sql = 'insert into user_grade (user_id, paper_id, exam_grade, create_time, paper_name,user_name) values (%s,%s,%s,now(), %s, %s)' cursor.execute( sql, (session.get('user_id'), paper_id, sum, paper_name, user_name)) db.commit() return str(sum)
def min_sal(): # находим и выводим минимсальную зарплату с вакансией cursor.execute("SELECT MIN(salary_from) FROM vacancy") result1 = cursor.fetchone() cursor.execute("SELECT MIN(salary_to) FROM vacancy") result2 = cursor.fetchone() if result1 < result2: return result1[0] else: return result2[0]
def max_sal(): # находим и выводим максимальную зарплату с вакансией cursor.execute("SELECT MAX(salary_from) FROM vacancy") result1 = cursor.fetchone() cursor.execute("SELECT MAX(salary_to) FROM vacancy") result2 = cursor.fetchone() if result1 > result2: return result1[0] else: return result2[0]
def teacher_check_paper(): # 这个函数跟学生的 show_answers() 逻辑是一样的 print_log('teacher check paper', request.method) # 提取GET方法的参数 paper_id = request.args.get('paper_id') student_id = request.args.get('student_id') student_name = request.args.get('student_name') # 找到试卷文件,并提取出试题和正确答案 sql = 'SELECT paper_path FROM ' + exam_paper_table + 'WHERE paper_id=%s' cursor.execute(sql, paper_id) path = cursor.fetchone().get('paper_path') std_ans = common_helper.get_std_answers(path) question = common_helper.parse_paper(path) # 找到学生选择的试卷的答案 sql = 'SELECT answer_json FROM ' + student_exam_log_table + \ 'WHERE student_id=%s and paper_id=%s' cursor.execute(sql, (student_id, paper_id)) answers = json.loads(cursor.fetchone().get('answer_json')) for i in range(0, len(question)): question[i]['std_ans'] = std_ans[str(i)] if answers.get(str(i)) is None: continue if question[i].get('q_type') == 'checkbox': is_correct = set(answers[str(i)]) == set(std_ans[str(i)]) else: is_correct = answers[str(i)] == std_ans[str(i)] question[i]['is_correct'] = is_correct question[i]['selected'] = answers[str(i)] # 找到试卷相关信息 sql = 'SELECT * FROM ' + \ exam_paper_table + 'INNER JOIN' + user_table + 'ON user.user_id=exam_paper.paper_userid ' + \ 'WHERE paper_id=%s' cursor.execute(sql, paper_id) data = cursor.fetchone() exam = { 'title': data.get('paper_title'), 'exam_id': paper_id, 'teacher': data.get('user_name'), 'duration': data.get('paper_time') } return render_template('teacher-check-paper.html', question=question, exam=exam, student=dict(student_name=student_name, student_id=student_id))
def get_question_list_by_ids(ids_list: list, table: str): sql = 'SELECT * FROM ' + table + 'WHERE q_id=%s' result = [] for x in ids_list: cursor.execute(sql, x) result.append(cursor.fetchone()) return result
def iniciar_sesion(): datos_usuario = request.form.to_dict( ) # Se obtienen los datos del formulario # Se codifica la password ingresada en el formulario para comparación de hash datos_usuario["password"] = datos_usuario["password"].encode( encoding="UTF-8") # Se obtienen los datos del colaborador (contraseña --> hash de contraseña) sql_query = """ SELECT rut,id_credencial,email,contraseña FROM Usuario WHERE rut = '%s' """ % (datos_usuario["rut"]) cursor.execute(sql_query) # Se obtienen los datos asociados al rut ingresado en el formulario datos_usuario_registrado = cursor.fetchone() # Si no se obtiene un registro, entonces el rut no se encuentra registrado en el sistema if datos_usuario_registrado is None: flash( "credenciales-invalidas" ) # Se notifica al front-end acerca del error para alertar al usuario return redirect(url_for("rutas_seba.principal")) return "Cuenta existente"
def get_from_main(city_name): city = [(str(city_name))] cursor.execute("SELECT * FROM main_cities WHERE city_name = ?", city) if cursor.fetchone() is None: return False else: return True
def submit_paper(): print_log('submit-paper', request.method) answers = request.form['answers'] exam_id = request.form['exam_id'] print_log('submit paper', answers + ' ' + str(type(answers))) print_log('submit paper', exam_id) sql = 'SELECT * FROM ' + exam_paper_table + ' WHERE paper_id = %s' cursor.execute(sql, exam_id) data = cursor.fetchone() # 单选题、多选题、判断题判分 grade, full_grade = common_helper.compare_answer(json.loads(answers), data.get('paper_path')) print_log('submit paper', 'grade = ' + str(grade)) print_log('submit paper', 'full grade = ' + str(full_grade)) # 写入数据库 sql = 'INSERT INTO ' + student_exam_log_table + ' VALUES (%s, %s, %s, %s, %s, -1, now())' print_log('submit paper', 'sql = ' + sql) try: cursor.execute( sql, (exam_id, session.get('user_id'), str(answers), grade, full_grade)) db_connector.commit() except: db_connector.rollback() return jsonify({'success': 1})
def login(): print_log('login', request.method) if request.method == 'GET': return 'login-GET' else: user_id = str(request.form['UserIDLogin']) password = str(request.form['PasswordLogin']) query = 'select * from ' + user_table + ' where user_id = %s' flag = cursor.execute(query, user_id) # flag = 1, 账号正确 if flag: result = cursor.fetchone() data = { 'success': 1, 'user_id': result.get('user_id'), 'user_name': result.get('user_name'), 'user_type': result.get('user_type') } session['user_id'] = data.get('user_id') print_log('login', str(data)) if str(result['user_password']) == password: return jsonify(data) else: data['success'] = 0 return jsonify(data) # flag = 0, 账号错误 else: return jsonify({'success': -1})
def previwe_paper(): print_log('preview paper', request.method) exam_id = request.args.get('exam_id') if session.get('user_id').upper() == common_helper.admin_type: teacher_id = request.args.get('user_id') else: teacher_id = session.get('user_id') sql = 'SELECT paper_path, paper_title, paper_time, paper_date FROM ' + \ exam_paper_table + ' INNER JOIN ' + user_table + ' ON exam_paper.paper_userid=user.user_id ' + \ 'WHERE paper_id=%s and paper_userid=%s' cursor.execute(sql, (exam_id, teacher_id)) data = cursor.fetchone() exam_dict = { 'title': data.get('paper_title'), 'exam_id': exam_id, 'duration': data.get('paper_time'), 'date': data.get('paper_date') } if session.get('user_id').upper() == 'ADMIN': return render_template('admin-preview-paper.html', question=common_helper.parse_paper( data.get('paper_path')), exam=exam_dict) else: return render_template('preview-paper.html', question=common_helper.parse_paper( data.get('paper_path')), exam=exam_dict)
def makepaper(): for index, item in enumerate(analyze()): answer = list(map(decidetra, getAnswer()))[index] sql = 'select count(*) as last_id from docx' cursor.execute(sql) paper_id = cursor.fetchone().get('last_id') sql = 'insert into questions (q_text,q_type,q_value,A,B,C,D,paper_id,answer) values (%s,%s,%s,%s,%s,%s,%s,%s,%s)' if index <= 23: cursor.execute( sql, (item.get('question'), 'radio', 1.5, item.get('items')[0], item.get('items')[1], item.get('items')[2], item.get('items')[3], paper_id, answer)) db.commit() if index > 23 and index < 36: cursor.execute( sql, (item.get('question'), 'checkbox', 2, item.get('items')[0], item.get('items')[1], item.get('items')[2], item.get('items')[3], paper_id, answer)) db.commit() if index >= 36: cursor.execute(sql, (item.get('question'), 'decide', 1, '1', 0, 0, 0, paper_id, answer)) db.commit()
def write_paper_file(question_ids: dict, output_file:str): print_log('write paper file', question_ids) choice_sql = 'SELECT * FROM ' + choice_question_table + 'WHERE q_id=%s' judge_sql = 'SELECT * FROM ' + judge_question_table + 'WHERE q_id=%s' subjective_sql = 'SELECT * FROM ' + subjective_question_table + 'WHERE q_id=%s' choice_header = ['Description', 'A', 'B', 'C', 'D', 'Answer', 'Value'] judge_header = ['Description', 'Answer', 'Value'] subjective_header = list(judge_header) workbook = xlwt.Workbook() single_sheet = workbook.add_sheet('单项选择题') multi_sheet = workbook.add_sheet('多项选择题') judge_sheet = workbook.add_sheet('判断题') subjective_sheet = workbook.add_sheet('主观题') add_sheet_header(choice_header, single_sheet) add_sheet_header(choice_header, multi_sheet) add_sheet_header(judge_header, judge_sheet) add_sheet_header(subjective_header, subjective_sheet) single_row, multi_row, judge_row, subjective_row = 1, 1, 1, 1 for choice_id in question_ids.get('choice'): cursor.execute(choice_sql, choice_id) data = cursor.fetchone() if len(data.get('q_answer')) == 1: add_choice_row(sheet=single_sheet, data=data, row=single_row) single_row += 1 else: add_choice_row(sheet=multi_sheet, data=data, row=multi_row) multi_row += 1 for judge_id in question_ids.get('judge'): cursor.execute(judge_sql, judge_id) data = cursor.fetchone() add_judge_row(sheet=judge_sheet, data=data, row=judge_row) judge_row += 1 for subjective_id in question_ids.get('subjective'): cursor.execute(subjective_sql, subjective_id) data = cursor.fetchone() add_subjective_row(sheet=subjective_sheet, data=data, row=subjective_row) subjective_row += 1 workbook.save(output_file) return
def get_log(id): sql = ("SELECT * FROM logs where id = %s order by created ") try: cursor.execute(sql, (id, )) result = cursor.fetchone() for row in result: print(row) except TypeError: print('Entered id is not present in table')
def checkSession(): user_id = session.get('user_id') if user_id: sql = 'select realname from users where id=%s' cursor.execute(sql, (user_id, )) return jsonify({ 'success': 1, 'username': cursor.fetchone().get('name') }) return jsonify({'success': 0})
def student_personal_info(): user_id = session.get('user_id') sql = 'SELECT * FROM ' + user_table + ' WHERE user_id=%s' cursor.execute(sql, user_id) data = cursor.fetchone() u = dict() u['user_id'] = user_id u['user_name'] = data.get('user_name') u['user_email'] = data.get('user_email') return render_template('student-personInfo.html', person=u)
def start_exam(): print_log('start-exam', request.method + request.args.get('exam_id')) exam_id = request.args.get('exam_id') sql = 'SELECT * FROM ' + exam_paper_table + ' WHERE paper_id=%s' cursor.execute(sql, str(exam_id)) data = cursor.fetchone() sql2 = 'SELECT user_name FROM ' + user_table + ' WHERE user_id=%s' cursor.execute(sql2, data.get('paper_userid')) data2 = cursor.fetchone() question = common_helper.parse_paper(data.get('paper_path')) exam = { 'exam_id': exam_id, 'title': data.get('paper_title'), 'duration': data.get('paper_time'), 'teacher': data2.get('user_name') } return render_template('exam.html', question=question, exam=exam)
def my_context(): user_id = session.get('user_id') if user_id: sql = 'select realname from users where id = %s' cursor.execute(sql, (user_id, )) name = cursor.fetchone().get('realname') return { 'name': name, } else: return {}
def enviar_recuperacion_password(): # Se obtienen los datos del formulario datos_recuperacion = request.form.to_dict() datos_recuperacion["identificacion_usuario"] = db.converter.escape( datos_recuperacion["identificacion_usuario"]) # Se revisa si el RUT o correo coincide con el registro de usuarios sql_query = """ SELECT nombres,email FROM Usuario WHERE rut = '%s' OR email = '%s' """ % (datos_recuperacion["identificacion_usuario"], datos_recuperacion["identificacion_usuario"]) cursor.execute(sql_query) datos_usuario = cursor.fetchone() # Si el correo o el rut no se encuentran registrados, se alerta al usuario if datos_usuario is None: flash( "recuperacion-invalida" ) # Se notifica al front-end acerca del error para alertar al usuario return redirect(url_for("rutas_seba.recuperacion_password")) # En caso de existir registro, se envía el correo de recuperación y se alerta al usuario # Se abre el template HTML correspondiente al restablecimiento de contraseña direccion_template = os.path.normpath( os.path.join( os.getcwd(), "app/templates/vistas_exteriores/recuperacion_password_mail.html")) html_restablecimiento = open(direccion_template, encoding="utf-8").read() # Se crea el mensaje correo = MIMEText(html_restablecimiento, "html") correo.set_charset("utf-8") correo["From"] = "*****@*****.**" correo["To"] = datos_usuario["email"] correo["Subject"] = "Prueba - Sistema LabEIT UDP" try: server = smtplib.SMTP("smtp.gmail.com", 587) server.starttls() server.login("*****@*****.**", "LabEIT_UDP_2020") str_correo = correo.as_string() server.sendmail("*****@*****.**", datos_usuario["email"], str_correo) server.close() return "OK" except Exception as e: return str(e)
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 my_context(): user_id = session.get('user_id') if session.get( 'user_id') is not None else '' if user_id != '': sql = 'select user_name, user_type from ' + user_table + ' where `user_id` = %s' cursor.execute(sql, user_id) result = cursor.fetchone() user_name = result.get('user_name') user_type = result.get('user_type') print_log('my context', str(user_name) + str(user_type) + str(user_id)) return { 'user_id': user_id, 'user_name': user_name, 'user_type': user_type } else: return {}
def generate_paper(): questions = request.form.get('selected_questions') questions = json.loads(questions) exam_title = request.form.get('exam_title') exam_tips = request.form.get('exam_tips') exam_duration = request.form.get('exam_duration') exam_datetime = request.form.get('exam_datetime') exam_class = request.form.get('exam_class') print('[generate paper]', str(questions), type(questions)) print(exam_title, exam_tips, exam_duration, exam_datetime, exam_class) output = os.path.join(file_dest, session.get('user_id') + '-' + exam_title + '.xls') common_helper.write_paper_file(question_ids=questions, output_file=output) import teacher_helper teacher_helper.update_questions_info(questions_ids=questions) sql = 'INSERT INTO ' + exam_paper_table + exam_paper_columns + \ 'VALUES (%s, %s, %s, %s, %s, %s, %s, %s)' try: cursor.execute(sql, (exam_title, exam_tips, exam_duration, exam_datetime, 0, output, session.get('user_id'), exam_class)) db_connector.commit() except: db_connector.rollback() return jsonify({'success': 0}) # 获取上面新增试卷的ID sql = 'SELECT max(paper_id) FROM ' + exam_paper_table cursor.execute(sql) paper_id = cursor.fetchone().get('max(paper_id)') # 在 teacher_student 中建立关联 if -1 == sql_helper.insert_teacher_student( paper_class=exam_class, teacher_id=session.get('user_id'), paper_id=paper_id): return jsonify({'success': 0}) return jsonify({'success': 1})
def login(): if request.method == 'GET': return 'GET' else: email = request.form['email'] password = request.form['password'] sql = "select id,realname from users where email=%s and password=%s" res = cursor.execute(sql, (email, password)) if res: result = cursor.fetchone() data = { 'success': 1, 'user_id': result.get('id'), 'username': result.get('realname'), } session['user_id'] = data.get('user_id') else: data = {'success': 0} return jsonify(data)
def register(): body = request.json name = body.get('name') email = body.get('email') password = body.get('password') if not email or not name or not password: return jsonify({'error': 'name, email and password are required'}) #check if user exist in database cursor.execute(f'SELECT * FROM user WHERE email = "{email}"') user = cursor.fetchone() if user != None: return jsonify({'message': 'User Already Exists'}) # hash the password and save to db hashedPassword = hashpw(password.encode(), gensalt(12)) cursor.execute( f'INSERT INTO user (name, email, password) VALUES ("{name}", "{email}", "{hashedPassword.decode()}")' ) db.commit() return jsonify({ 'message': 'Successfully registered', })
def login(): body = request.json email = body.get('email') password = body.get('password') #all fields are required if not email or not password: return jsonify({'error': 'email and password are required'}) #check if user exist in database cursor.execute(f'SELECT * FROM user WHERE email = "{email}"') user = cursor.fetchone() if user == None: return jsonify({'message': 'User does not exist, please register'}) #check if password match isValid = checkpw(password.encode(), user.get('password').encode()) if isValid: token = gentoken({'id': str(user['id']), 'name': user['name']}) return jsonify({ 'message': 'Successfully logged in', 'token': token.decode() }) else: return jsonify({'message': 'Incorrect password'})
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 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'))
def show_user(userId): cursor.execute(f'SELECT name, email FROM user WHERE id = {userId}') user = cursor.fetchone() if (user == None): return jsonify({'msg': 'User not found'}) return jsonify({'user': user})
def avg_sal(): cursor.execute("SELECT AVG((salary_to+salary_from) / 2) FROM vacancy") result = cursor.fetchone() return result[0]
def getDocxPath(): sql = 'select * from docx order by id' cursor.execute(sql, ) path = cursor.fetchone().get('path') return path