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 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 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 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 admin_addchoice(): q_desc = request.form.get('q_desc') q_value = request.form.get('q_value') q_answer = request.form.get('q_answer') q_diff = request.form.get('q_diff') q_A = request.form.get('q_A') q_B = request.form.get('q_B') q_C = request.form.get('q_C') q_D = request.form.get('q_D') q_class = request.form.get('q_class') table_name = 'choice_question' sql = 'INSERT INTO ' + choice_question_table + \ '(q_description, q_value, q_answer, q_A, q_B, q_C, q_D, q_counter, q_difficulty, q_year,q_paperid, q_type) ' + \ 'VALUES (%s, %s, %s, %s, %s, %s, %s, 0, %s, 2020,30, %s)' print(q_desc, q_value, q_answer, table_name, q_A, q_B, q_C, q_D, q_class) try: cursor.execute( sql, (q_desc, q_value, q_answer, q_A, q_B, q_C, q_D, q_diff, q_class)) db_connector.commit() return jsonify({'success': 1}) except: print('[admin add choice]', 'insert failed') db_connector.rollback() return jsonify({'success': 0})
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 modify_choice(): q_id = request.form.get('q_id') q_desc = request.form.get('q_desc') q_value = request.form.get('q_value') q_answer = request.form.get('q_answer') q_difficulty = request.form.get('q_diff') q_A = request.form.get('q_A') q_B = request.form.get('q_B') q_C = request.form.get('q_C') q_D = request.form.get('q_D') q_type = request.form.get('q_type') sql = 'UPDATE ' + choice_question_table + ' SET ' + \ 'q_description=%s, q_value=%s, q_answer=%s, q_A=%s, q_B=%s, q_C=%s, q_D=%s, q_difficulty=%s, q_type=%s ' + \ 'WHERE q_id = ' + q_id print(sql) print(q_id, q_desc, q_value, q_answer, q_A, q_B, q_C, q_D, q_difficulty, q_type) try: cursor.execute(sql, (q_desc, q_value, q_answer, q_A, q_B, q_C, q_D, q_difficulty, q_type)) db_connector.commit() return jsonify({'success': 1}) except: print('[admin modify choice]', 'modify failed') db_connector.rollback() return jsonify({'success': 0})
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 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 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 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 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 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 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 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 create_tables(): cursor.execute("""CREATE TABLE IF NOT EXISTS vacancy( id TEXT PRIMARY KEY, v_name TEXT, salary_from INT, salary_to INT);""") cursor.execute("""CREATE TABLE IF NOT EXISTS main_cities( city_name TEXT PRIMARY KEY, salary_from INT, salary_to INT, avg_salary INT);""")
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 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 create_table(dbname): cursor.execute(f'USE {dbname}') for table_name in TABLES: table_description=TABLES[table_name] try: cursor.execute(table_description) print(f"Creating table {table_name}",end='') except mysql.connector.Error as err: if err.errno == errorcode.ER_TABLE_EXISTS_ERROR: print("Already exists") else: print(err.msg)
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 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 get_todo_list(self): sql = "select * from todo;" n = cursor.execute(sql) if not n: return [] data = cursor.fetchall() return TodoModel.load(data)
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 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 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 check_id() -> object: if request.method == 'GET': return 'checkRegisterStudentID-GET' else: user_id = request.form['user_id'] query = 'select * from ' + user_table + ' where user_id = %s' flag = cursor.execute(query, user_id) return jsonify({'has': flag})
def teacher_submit_grade(): print_log('teacher submit grade', request.method) paper_id = request.form['paper_id'] student_id = request.form['student_id'] subjective_grade = request.form['subjective_grade'] print_log( 'teacher submit grade', str(paper_id) + ' ' + str(student_id) + ' ' + str(subjective_grade)) sql = 'UPDATE ' + student_exam_log_table + ' SET subjective_grade=%s ' + \ 'WHERE paper_id=%s AND student_id=%s' try: cursor.execute(sql, (subjective_grade, paper_id, student_id)) db_connector.commit() except: db_connector.rollback() return redirect(url_for('teacher_modify'))
def checkEmail(): if request.method == 'GET': return 'GET' else: email = request.form['email'] sql = "select * from users where email=%s" res = cursor.execute(sql, (email, )) if res: return jsonify({'has': 1}) return jsonify({'has': 0})
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 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})