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)