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