def add_item_to_answer_db(table, request):
handle_database(
"""INSERT INTO {} ({}, {}, {}, {}) VALUES ('{}', {}, {}, '{}');""".
format(table, 'submission_time', 'vote_number', 'question_id',
'message',
str(datetime.now())[:-7], 0, request['question_id'],
request['new_answer_message']))
def vote():
vote = request.json['vote']
voted_planet_id = json.loads(vote)['vote']
username = json.loads(vote)['username']
text = "SELECT id \
FROM swuser\
WHERE username = %s"
data = (username, )
result = data_manager.handle_database(text, data)
if result['result'] != 'success':
return render_template(
'error.html', error='Error handling your vote. Try to vote again!')
else:
swuser_id = result['rows'][0][0]
text = "INSERT INTO planetvotes(planet_id, swuser_id, submission_time) \
VALUES (%s, %s, %s)"
data = (voted_planet_id, swuser_id, str(datetime.now())[:-7])
result = data_manager.handle_database(text, data)
if result['result'] != 'success':
return render_template(
'error.html',
error='Error handling your vote. Try to vote again!')
else:
return redirect(url_for('index'))
def post_login():
username_to_login = request.form['username']
text = "SELECT username \
FROM swuser\
WHERE username = %s"
data = (username_to_login, )
result = data_manager.handle_database(text, data)
if result['result'] == 'success':
if result['row_count'] != 0:
password = request.form['password']
text = "SELECT password \
FROM swuser \
WHERE username = %s"
data = (username_to_login, )
result = data_manager.handle_database(text, data)
if result['result'] == 'success':
password_from_database = result['rows'][0][0]
if check_password_hash(password_from_database, password):
session['username'] = username_to_login
global username
username = username_to_login
redirection = redirect(url_for('index'))
response = make_response(redirection)
response.set_cookie("username", username)
return response
else:
flash('Authentification failed. Try to login again!')
return redirect(url_for('get_login'))
else:
flash('Username not registered. Try to login again!')
return redirect(url_for('get_login'))
else:
return render_template('error.html', error=result['result'])
def add_item_to_comment_db(table, request):
if "question_id" in request:
handle_database(
"""INSERT INTO {} ({}, {}, {}, {}) VALUES ({}, {}, '{}', '{}');""".
format(table, 'question_id', 'answer_id', 'message',
'submission_time', request['question_id'], 'NULL',
request['new_comment_message'],
str(datetime.now())[:-7]))
else:
handle_database(
"""INSERT INTO {} ({}, {}, {}) VALUES ({}, '{}', '{}');""".format(
table, 'answer_id', 'message', 'submission_time',
request['answer_id'], request['new_comment_message'],
str(datetime.now())[:-7]))
def post_register():
username_to_register = request.form['username']
text = 'SELECT username \
FROM swuser\
WHERE username = %s;'
data = (username_to_register, )
result = data_manager.handle_database(text, data)
if result['result'] == 'success':
if result['row_count'] == 0:
if request.form['password'] != request.form['confirm-password']:
flash(
'Password confirmation falied. Please re-enter password!')
return render_template('register.html',
username=username_to_register)
else:
password = generate_password_hash(request.form['password'])
text = "INSERT INTO swuser(username, password) \
VALUES(%s, %s)"
data = (username_to_register, password)
result = data_manager.handle_database(text, data)
if result['result'] == 'success':
text = "SELECT username \
FROM swuser\
WHERE username = %s"
data = (username_to_register, )
result = data_manager.handle_database(text, data)
if result['result'] == 'success':
info = True
return render_template('register.html', info=info)
else:
return render_template('error.html',
error=result['result'])
else:
return render_template('error.html',
error=result['result'])
else:
flash('Username already in database! Choose another username')
return redirect(url_for('get_register'))
else:
return render_template('error.html', error=result['result'])
def get_user_data():
loggedin = False
username = ""
user_id = ""
if 'username' in session:
loggedin = True
username = session['username']
get_id_query = """SELECT id FROM diet_users WHERE username=%s"""
data = (username, )
result = data_manager.handle_database(get_id_query, data)
user_id = result['rows'][0][0]
return {"loggedin": loggedin, "username": username, "user_id": user_id}
def statistics():
text = "SELECT planet_id, count(planet_id) \
FROM planetvotes \
GROUP BY planet_id \
ORDER BY planet_id"
data = None
result = data_manager.handle_database(text, data)
if result['result'] == 'success':
statistics = []
for row in result['rows']:
planet_name = get_planet_name(row[0])
statistics.append([planet_name, row[1]])
statistics.sort()
json_statistics = jsonify(statistics)
return json_statistics
else:
return render_template('error.html',
error='Error handling statistics. Try again!')
def add_new_registration():
tolerance = request.form['tolerance']
new_username = request.form['new_user_name']
new_password = request.form['new_password']
username_check_query = """SELECT username FROM diet_users WHERE username=%s"""
data = (new_username, )
result = data_manager.handle_query(username_check_query, data)
if result['result'] == 'success':
if result['row_count'] == 0:
if request.form['new_password'] != request.form['confirm_password']:
flash(
'Password confirmation failed. Please re-enter password!')
return render_template('registration.html',
username=new_username)
else:
hashed_password = werkzeug.security.generate_password_hash(
new_password, method='pbkdf2:sha256', salt_length=8)
query = """INSERT INTO diet_users (username, password, submission_time, tolerance) VALUES (%s, %s, %s, %s)"""
submission_time = str(datetime.now())[:-7]
data = (new_username, hashed_password, submission_time,
tolerance)
insert_check_result = data_manager.handle_query(query, data)
if insert_check_result['result'] == 'success':
insert_check_query = """SELECT username FROM diet_users WHERE username = %s"""
data = (new_username, )
result = data_manager.handle_database(
insert_check_query, data)
if result:
info = True
return render_template('registration.html', info=info)
else:
return render_template('error.html',
error=result['result'])
else:
return render_template('error.html',
error=result['result'])
else:
flash('Username already in database! Choose another username')
return redirect(url_for('registration'))
else:
return render_template('error.html', error=result['result'])
return redirect('/')
def add_item_to_tag(table, name):
handle_database("""INSERT INTO {} ({}) VALUES ('{}');""".format(
table, 'name', name))
def get_existing_tags():
result = handle_database("""SELECT name FROM tag;""")
return result
def get_tag_id_by_name(tag_name):
result = handle_database(
"""SELECT id FROM tag WHERE name='{}';""".format(tag_name))
return result
def handle_query(sql_query):
result = handle_database(sql_query)
return result
def search_in_db(search_query):
result = handle_database("""{};""".format(search_query))
return result
def delete_record(table_name, condition):
handle_database("""DELETE FROM {} WHERE {};""".format(
table_name, condition))
def get_record_from_tag(table_name, join_text, condition):
# SELECT tag.name FROM tag JOIN question_tag ON question_tag.tag_id=tag.id WHERE question_tag.question_id=1;
result = handle_database(
"""SELECT name FROM {} JOIN {} WHERE {};""".format(
table_name, join_text, condition))
return result
def get_table_from_sql_db(table_name,
limit,
order_by=['submission_time', 'DESC']):
result = handle_database("""SELECT * FROM {} ORDER BY {} {}{};""".format(
table_name, order_by[0], order_by[1], limit))
return result
def add_item_to_question_tag(table, question_id, tag_id):
handle_database("""INSERT INTO {} ({}, {}) VALUES ({}, {});""".format(
table, 'question_id', 'tag_id', question_id, tag_id))
def update_record(table_name, set_value, condition):
handle_database("""UPDATE {} SET {} WHERE {}""".format(
table_name, set_value, condition))
def get_record_from_sql_db(table_name, condition):
result = handle_database("""SELECT * FROM {} WHERE {};""".format(
table_name, condition))
return result