62
Приложение 1. Основной код программы
from flask import Flask, render_template, json, request
from flask.ext.mysql import MySQL
from werkzeug import generate_password_hash, check_password_hash
mysql = MySQL()
app = Flask(__name__)
app.config['MYSQL_DATABASE_USER'] = ''
app.config['MYSQL_DATABASE_PASSWORD'] = ''
app.config['MYSQL_DATABASE_DB'] = ''
app.config['MYSQL_DATABASE_HOST'] = ''
mysql.init_app(app)
entered = 0
right = 0
logged_user = ""
@app.route('/')
def main():
return render_template('index.html')
@app.route('/showSignIn')
def showSignUp():
return render_template('signin.html')
@app.route('/showTest')
def showTest():
return render_template('test.html')
@app.route('/showChange')
def showChange():
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("SELECT * FROM tbl_user")
df = cursor.fetchall()
return render_template('change.html', data=df)
@app.route('/showTasks')
def showTasks():
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("SELECT * FROM tasks")
df = cursor.fetchall()
return render_template('tasks.html')
@app.route('/showResults')
def showResults():
return render_template('results.html')
@app.route('/showList')
63
def showList():
return render_template('list.html')
@app.route('/signIn',methods=['POST','GET'])
def signIn():
try:
_name = request.form['inputName']
_password = request.form['inputPassword']
if _name and _password:
conn = mysql.connect()
cursor = conn.cursor()
_hashed_password = generate_password_hash(_password)
cursor.execute("SELECT p_name, p_password, p_right FROM tbl_us
er WHERE p_name = :_name AND p_password = :_password", {"_name": _name, "_
password": _password})
results = cursor.fetchall()
if (len(results)):
right = results[0][2]
entered = 1
logged_user = _name
cursor.execute("SELECT p_name, name, surname FROM names WHER
E p_name = :_name", {"_name": p_name})
results = cursor.fetchall()
return json.dumps({'html':'<span>Добрый день, %s %s </span>'
%(results[0][0], results[0][1])})
else:
return json.dumps({'html':'<span>Неверные входные данные, по
пробуйте еще раз</span>'})
else:
return json.dumps({'html':'<span>Введены не все данные!</span>
'})
except Exception as e:
return json.dumps({'error':str(e)})
finally:
cursor.close()
conn.close()
@app.route('/Tasks',methods=['POST','GET'])
def Tasks():
if entered and right >= 1:
try:
_task = request.form['inputTask']
_var_1 = request.form['inputVar1']
_var_2 = request.form['inputVar1']
_var_3 = request.form['inputVar1']
_var_4 = request.form['inputVar1']
_correct = request.form['inputCorr']
if _task and _var_1 and _var_2 and _var_3 and _var_4 and _correct:
conn = mysql.connect()
64
cursor = conn.cursor()
cursor.execute("INSERT INTO tasks (task, var_1, var_2, var_3,
var_4, correct) VALUES (:_task, :_var_1, :_var_2, :_var_3, :_var_4, :_corr
ect);", {"_task": _task, "_var_1": _var_1, "_var_2": _var_2, "_var_3": _va
r_3, "_var_4": _var_4, "_correct": _correct})
conn.commit()
else:
return json.dumps({'html':'<span>Введены не все данные!</span>
'})
except Exception as e:
return json.dumps({'error':str(e)})
finally:
cursor.close()
conn.close()
else:
return json.dumps({'html':'<span>Недостаточно прав!</span>'})
@app.route('/deleteTask', number)
def deleteTasks():
if entered and right >= 1:
try:
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute("SELECT * FROM tasks")
results = cursor.fetchall()
task = results[number][0]
cursor.execute("DELETE FROM tasks where task = :_task", {"_task": ta
sk})
conn.commit()
except Exception as e:
return json.dumps({'error':str(e)})
finally:
cursor.close()
conn.close()
else:
return json.dumps({'html':'<span>Недостаточно прав!</span>'})
@app.route('/addUser', methods=['POST','GET'])
def addUser():
if entered and right >= 2:
try:
_name = request.form['inputName']
_surname = request.form['inputSurname']
_patronym = request.form['inputPatronym']
_clas = request.form['inputClass']
_right = request.form['right']
_username = _surname + _name[0] + _patronym[0]
_password = _username
conn = mysql.connect()
cursor = conn.cursor()
65
_hashed_password = generate_password_hash(_password)
cursor.callproc('createUser',(_name, _password, _right))
conn.commit()
except Exception as e:
return json.dumps({'error':str(e)})
finally:
cursor.close()
conn.close()
else:
return json.dumps({'html':'<span>Недостаточно прав!</span>'})
@app.route('/deleteUser', methods=['POST','GET'])
def deleteUser():
if entered and right >= 2:
try:
_username = request.form['inputName']
conn = mysql.connect()
cursor = conn.cursor()
cursor.callproc('deleteUser',(_username))
conn.commit()
except Exception as e:
return json.dumps({'error':str(e)})
finally:
cursor.close()
conn.close()
else:
return json.dumps({'html':'<span>Недостаточно прав!</span>'})
@app.route('/changePassword', methods=['POST','GET'])
def changePassword():
try:
_old = request.form['old_password']
_new = request.form['new_password']
conn = mysql.connect()
cursor = conn.cursor()
cursor.callproc('changePassword',(logged_user, _old, _new))
conn.commit()
except Exception as e:
return json.dumps({'error':str(e)})
finally:
cursor.close()
conn.close()
@app.route('/makeTest', methods=['POST','GET'])
def makeTest():
if entered and right >= 1:
try:
testnums = request.form['test']
testnums = testnums.split()
conn = mysql.connect()
66
cursor = conn.cursor()
res = []
for num in testnums:
cursor.execute("SELECT test, var_1, var_2, var_3, var_4, correct f
rom tasks where row_num = :_num", {"_num": num - 1})
result = cursor.fetchall()
res.append(result)
leng = len(cursor.execute("SELECT * FROM tests"))
cursor.execute("INSERT INTO tests (number, test) values (:_number, :
_test)", {"_number": leng + 1, "_test": res})
conn.commit()
except Exception as e:
return json.dumps({'error':str(e)})
finally:
cursor.close()
conn.close()
else:
return json.dumps({'html':'<span>Недостаточно прав!</span>'})
@app.route('/solveTest', methods=['POST','GET'])
def solveTest():
if entered:
try:
num = request.form['test']
conn = mysql.connect()
cursor = conn.cursor()
res = []
cursor.execute("SELECT number, test from tests where row_num = :_num
", {"_num": num - 1})
result = np.array(cursor.fetchall())[1]
return render_template('main.html', q = result[:, 0], o = result[:,
1:5])
except Exception as e:
return json.dumps({'error':str(e)})
finally:
cursor.close()
conn.close()
else:
return json.dumps({'html':'<span>Авторизуйтесь для выполнения действий
</span>'})
@app.route('/checkresults', methods=['POST','GET'])
def checkResults():
if entered and right >= 1:
try:
answers = request.form['answers'].split()
conn = mysql.connect()
cursor = conn.cursor()
res = []
67
cursor.execute("SELECT test from tests where row_num = :_num", {"_nu
m": num - 1})
result = np.array(cursor.fetchall())[1][:, -1]
correct = 0
for i in range(len(answers)):
if (answers[i] == result[i]):
correct += 1
return correct / len(answers)
except Exception as e:
return json.dumps({'error':str(e)})
finally:
cursor.close()
conn.close()
else:
return json.dumps({'html':'<span>Недостаточно прав!</span>'})
@app.route('/getresults', methods=['POST','GET'])
def getResults():
if entered and right >= 1:
try:
_clas = request.form['class'].split()
conn = mysql.connect()
cursor = conn.cursor()
res = []
cursor.execute("SELECT * from results where class = :_class", {"_cla
ss": _clas})
result = pd.DataFrame(cursor.fetchall())
result.to_excel("results.xlsx")
except Exception as e:
return json.dumps({'error':str(e)})
finally:
cursor.close()
conn.close()
else:
return json.dumps({'html':'<span>Недостаточно прав!</span>'})
if __name__ == "__main__":
app.run(port=5002)
68
Приложение 2. Дополнительные функции для реализации проекта
Данные функции позволяют с помощью SQL добавлять и удалять пользователя, а
также изменять пароль.
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `createUser`(
IN p_username VARCHAR(100),
IN p_password VARCHAR(100),
IN p_right INT(2).
)
BEGIN
if (select exists (select * from tbl_user where user_username = p_user
name)) THEN
select 'Пользователь уже существует!';
ELSE
insert into tbl_user
(
user_username,
user_password,
user_rights
)
values
(
p_username,
p_password,
p_right
);
END IF;
END$$
DELIMITER ;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `deleteUser`(
IN p_username VARCHAR(100).
)
BEGIN
if (select exists (select 1 from tbl_user where user_username = p_user
name)) THEN
delete from tbl_user
where user_username = p_username
ELSE
select 'Пользователя не существует!';
END IF;
END$$
DELIMITER ;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `changePassword`(
IN p_username VARCHAR(100),
69
IN p_password VARCHAR(100),
IN new_password VARCHAR(100).
)
BEGIN
IF (select exists (select 1 from tbl_user where user_username = p_user
name and user_password = p_password)) THEN
insert into tbl_user
(
user_username,
user_password
)
values
(
p_username,
new_password
);
delete from tbl_user
where user_username = p_username
and user_password = p_password;
ELSE
select 'Неверный логин или пароль, попробуйте еще раз!';
END IF;
END$$
DELIMITER ;