def add_new_tag(question_id, tag_id, tag_name):
new_tag = tag_name
sql_query = """INSERT INTO tag (name) VALUES (%s);"""
db_connection.sql_data(sql_query, "write", new_tag)
tag = (question_id, tag_id)
sql_query = """INSERT INTO question_tag (question_id, tag_id) VALUES (%s, %s);"""
db_connection.sql_data(sql_query, "write", tag)
def update_question(id, data):
title = data['title']
description = data['message']
sql_query = """UPDATE question
SET title = %s, message = %s
WHERE id = %s;"""
question = [title, description, id]
db_connection.sql_data(sql_query, "update", question)
def add_comment(form_data, id):
sql_read = """SELECT * FROM answer WHERE id = %s"""
answer_row = db_connection.sql_data(sql_read, "read", id)
for answer in answer_row:
question_id = answer["question_id"]
new_comment = (form_data['comment'], datetime.now(), 0, id, question_id)
sql_query = """INSERT INTO comment (message, submission_time, edited_count, answer_id, question_id)
VALUES (%s, %s, %s, %s, %s)"""
db_connection.sql_data(sql_query, "write", new_comment)
def verify_login(data):
sql_query = """SELECT user_login, user_password FROM credentials WHERE user_login = %s"""
login_and_password = db_connection.sql_data(sql_query, "read", (data["username"],))
if login_and_password:
if verify_password(data["password"], login_and_password[0]["user_password"]):
return 0
return 1
return 1
sql_query = """SELECT user_id FROM credentials WHERE user_login = %s"""
user_id = db_connection.sql_data(sql_query, "read", (username, ))
return user_id
def delete_question_element(element_id):
sql_query_question = """DELETE FROM question WHERE id = %s;"""
db_connection.sql_data(sql_query_question, "write", element_id)
sql_query_answer = """DELETE FROM answer WHERE question_id = %s;"""
db_connection.sql_data(sql_query_answer, "write", element_id)
sql_query_comment = """DELETE FROM comment WHERE question_id = %s;"""
db_connection.sql_data(sql_query_comment, "write", element_id)
def new_question(request):
data = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
question = (data, "0", "0", request['title'], request["message"], None)
sql_query = """INSERT INTO question (submission_time, view_number, vote_number, title, message, image)
VALUES (%s, %s, %s, %s, %s, %s);"""
db_connection.sql_data(sql_query, "write", question)
def get_questions():
sql_query = """SELECT * FROM question ORDER BY id ASC;""" #selecting lisr of dicts from sql
q_list = db_connection.sql_data(sql_query, "read")
print(q_list)
return q_list
def question_view_count_increase(id):
sql_update = """UPDATE question
SET view_number = view_number + 1
WHERE id = %s;"""
db_connection.sql_data(sql_update, "update", id)
def get_question_id(answer_id): #s
sql_query = """SELECT * FROM answer WHERE id = %s"""
answers = db_connection.sql_data(sql_query, "read", answer_id)
for answer in answers:
question_id = str(answer['question_id'])
return question_id
def get_comment_to_answer(question_id):
sql_query = """SELECT * FROM comment WHERE question_id = %s;"""
comments = db_connection.sql_data(sql_query, "read", question_id)
return comments
def one_question(id_of_question):
sql_query = """SELECT * FROM question WHERE id=%s;"""
question = db_connection.sql_data(sql_query, "read", id_of_question)
return question
def sort_vote():
sql_read = """SELECT * FROM question ORDER BY vote_number DESC"""
questions = db_connection.sql_data(sql_read, "read")
return questions
def get_tags():
sql_query = """SELECT * FROM tag ORDER BY id ASC;"""
tags_list = db_connection.sql_data(sql_query, "read")
return tags_list
def add_tag(question_id, tag_id):
tag = (question_id, tag_id)
sql_query = """INSERT INTO question_tag (question_id, tag_id) VALUES (%s, %s);"""
db_connection.sql_data(sql_query, "write", tag)
def delete_answer_element(element_id): #usuwanie commentarzy do odpowiedzi
sql_query_answer = """DELETE FROM answer WHERE id = %s;"""
db_connection.sql_data(sql_query_answer, "write", element_id)
sql_query_comment = """DELETE FROM comment WHERE answer_id = %s;"""
db_connection.sql_data(sql_query_comment, "write", element_id)
def vote_answers_minus(id):
sql_update = """UPDATE answer
SET vote_number = vote_number - 1
WHERE id = %s;"""
db_connection.sql_data(sql_update, "update", id)
def delete_comment_element(element_id):
sql_query_comment = """DELETE FROM comment WHERE id = %s;""" #dunno if that work
db_connection.sql_data(sql_query_comment, "write", element_id)
def vote_questions_minus(id):
sql_update = """UPDATE question
SET vote_number = vote_number - 1
WHERE id = %s;"""
db_connection.sql_data(sql_update, "update", id)
def get_email(username=None):
sql_query = """SELECT user_email FROM credentials WHERE user_login = %s"""
info = db_connection.sql_data(sql_query, "read", (username, ))
return info
def best_questions():
sql_read = """SELECT * FROM question ORDER BY vote_number DESC LIMIT 1;"""
question = db_connection.sql_data(sql_read, "read")
return question
def verify_username(data):
sql_query = """SELECT user_id FROM credentials WHERE user_login = %s"""
login_check = db_connection.sql_data(sql_query, "read", (data, ))
if login_check:
return "login taken"
return None
def sort_time():
sql_read = """SELECT * FROM question ORDER BY submission_time DESC"""
questions = db_connection.sql_data(sql_read, "read")
return questions
def get_answers_to_question(id_from_question):
sql_query = """SELECT * FROM answer WHERE question_id = %s ORDER BY id ASC;"""
answers = db_connection.sql_data(sql_query, "read", id_from_question)
return answers
def newest_questions():
sql_query = """SELECT * FROM question ORDER BY submission_time DESC LIMIT 5;"""
questions = db_connection.sql_data(sql_query, "read")
return questions
def get_all_answers():
sql_query = """SELECT * FROM answer;""" #selecting list of dicts from sql
a_list = db_connection.sql_data(sql_query, "read")
return a_list
def register(data):
credentials = (data["username"], data["email"],
hash_password(data['password']))
sql_query = """INSERT INTO credentials(user_login, user_email, user_password)
VALUES (%s, %s, %s);"""
db_connection.sql_data(sql_query, "write", credentials)
def hottest_questions():
sql_query = """SELECT * FROM question ORDER BY view_number DESC LIMIT 5"""
questions = db_connection.sql_data(sql_query, "read")
return questions
def verify_email(data):
sql_query = """SELECT user_id FROM credentials WHERE user_email = %s"""
email_check = db_connection.sql_data(sql_query, "read", (data, ))
if email_check:
return "email taken"
return None
def add_answer(form_data, id):
new_answer = (datetime.now().strftime("%Y-%m-%d %H:%M:%S"), 0, id,
form_data['message'], None)
sql_query = """INSERT INTO answer (submission_time, vote_number, question_id, message, image)
VALUES (%s, %s, %s, %s, %s)"""
db_connection.sql_data(sql_query, "write", new_answer)
