def get_row_from_table(table, id_num): query = ''' SELECT * FROM {} WHERE id = %s'''.format(table) list_of_var = [id_num] return connection.db_mod_list_with_return(query=query, list_of_var=list_of_var)
def get_data_by_user_id(table_name, user_id): query = ''' SELECT * FROM {} WHERE user_id = %s'''.format(table_name) list_of_var = [user_id] return connection.db_mod_list_with_return(query=query, list_of_var=list_of_var)
def get_user_id_by_id(table, question_id): query = ''' SELECT user_id FROM {} WHERE id = %s'''.format(table) list_of_var = [question_id] return connection.db_mod_list_with_return(query=query, list_of_var=list_of_var)
def get_user_id(username): query = ''' SELECT id FROM users WHERE username = %s''' list_of_var = [username] return connection.db_mod_list_with_return(query=query, list_of_var=list_of_var)
def get_user_name_by_id(id_num): query = ''' SELECT username FROM users WHERE id = %s''' list_of_var = [id_num] return connection.db_mod_list_with_return(query=query, list_of_var=list_of_var)
def get_question_id_from_tag_id(tag_id): query = ''' SELECT question_id FROM question_tag WHERE tag_id=%s''' list_of_var = [tag_id] return connection.db_mod_list_with_return(query=query, list_of_var=list_of_var)
def get_comment(id_type, id_num): query = ''' SELECT * FROM comment WHERE {} = %s'''.format(id_type) list_of_var = [id_num] return connection.db_mod_list_with_return(query=query, list_of_var=list_of_var)
def get_question_with_username(id_num): query = ''' SELECT q.id, q.submission_time, q.edit_submission_time, q.view_number, q.vote_number, q.title, q.message, q.image, q.user_id, u.username FROM question q JOIN users u ON q.user_id = u.id WHERE q.id = %s''' list_of_var = [id_num] return connection.db_mod_list_with_return(query=query, list_of_var=list_of_var)
def get_tags_data(): query = ''' SELECT t.name as name, (SELECT COUNT(*) FROM question_tag WHERE tag_id = t.id) as count_of_marked FROM tag t GROUP BY t.name, t.id ORDER BY count_of_marked''' list_of_var = [] return connection.db_mod_list_with_return(query=query, list_of_var=list_of_var)
def get_comment_with_username(id_type, id_num): query = ''' SELECT c.id, c.question_id, c.answer_id, c.message, c.submission_time, c.edit_submission_time, c.edited_count, c.user_id, u.username FROM comment c JOIN users u on c.user_id = u.id WHERE {} = %s'''.format(id_type) list_of_var = [id_num] return connection.db_mod_list_with_return(query=query, list_of_var=list_of_var)
def get_answer_comments_with_username(q_id): query = ''' SELECT c.id, c.question_id, c.answer_id, c.message, c.submission_time, c.edit_submission_time, c.edited_count, c.user_id, u.username FROM comment c JOIN users u on c.user_id = u.id WHERE answer_id IN (SELECT answer.id FROM answer WHERE answer.question_id = %s)''' list_of_var = [q_id] return connection.db_mod_list_with_return(query=query, list_of_var=list_of_var)
def get_whole_tags(tag_name): query = ''' SELECT question_id FROM question_tag RIGHT JOIN tag ON question_tag.tag_id=tag.id WHERE tag.name = %s''' list_of_var = [tag_name] return connection.db_mod_list_with_return(query=query, list_of_var=list_of_var)
def get_tags(question_id): query = ''' SELECT tag.name FROM question_tag INNER JOIN tag ON question_tag.tag_id=tag.id WHERE question_id = %s''' list_of_var = [question_id] return connection.db_mod_list_with_return(query=query, list_of_var=list_of_var)
def get_all_questions_without_limit(order_by='id', desc='DESC'): query = ''' SELECT q.id, q.submission_time, q.edit_submission_time, q.view_number, q.vote_number, q.title, q.message, q.image, q.user_id, u.username FROM question q JOIN users u on q.user_id = u.id ORDER BY {} {}'''.format(order_by, desc) list_of_var = [] return connection.db_mod_list_with_return(query=query, list_of_var=list_of_var)
def check_user_data(column, data): query = ''' SELECT {} FROM users WHERE {} = %s'''.format(column, column) list_of_var = [data] if connection.db_mod_list_with_return(query=query, list_of_var=list_of_var): return True return False
def check_if_user_voted_answer(answer_id, u_id, value): query = ''' SELECT * FROM votes WHERE answer_id = %s AND user_id = %s AND updown = %s''' list_of_var = [answer_id, u_id, value] if connection.db_mod_list_with_return(query=query, list_of_var=list_of_var): return True return False
def get_answers_by_question_id(question_id): query = ''' SELECT a.id, a.submission_time, a.edit_submission_time, a.vote_number, a.question_id, a.message, a.image, a.user_id, a.accepted, u.username, v.updown, v.user_id as v_user FROM answer a JOIN users u on a.user_id = u.id LEFT JOIN votes v ON v.answer_id = a.id WHERE a.question_id = %s ORDER BY id''' list_of_var = [question_id] return connection.db_mod_list_with_return(query=query, list_of_var=list_of_var)
def search_answer(search): search = util.modify_search(search) search = '%' + search + '%' query = """ SELECT a.id, a.submission_time, a.edit_submission_time, a.vote_number, a.question_id, a.message, a.image, a.user_id, a.accepted, u.username FROM answer a JOIN users u ON u.id = a.user_id WHERE message ILIKE %s""" list_of_var = [search] return connection.db_mod_list_with_return(query=query, list_of_var=list_of_var)
def search_question(search): search = util.modify_search(search) search = '%' + search + '%' query = ''' SELECT q.id, q.submission_time, q.edit_submission_time, q.view_number, q.vote_number, q.title, q.message, q.image, q.user_id, u.username FROM question q JOIN users u ON u.id = q.user_id WHERE title ILIKE %s OR message ILIKE %s ''' list_of_var = [search, search] return connection.db_mod_list_with_return(query=query, list_of_var=list_of_var)
def get_votes_by_user(column, u_id, value): query = ''' SELECT {} FROM votes WHERE user_id = %s AND updown = %s'''.format(column) list_of_var = [u_id, value] retard_list_of_id = connection.db_mod_list_with_return(query=query, list_of_var=list_of_var) list_of_id = [] for element in retard_list_of_id: list_of_id.append(element[column]) return list_of_id
def get_users(): query = ''' SELECT u.id, username, registration_date, (SELECT count(*) FROM question WHERE user_id = u.id) AS count_of_asked, (SELECT COUNT(*) FROM answer WHERE user_id = u.id) AS count_of_answers, (SELECT COUNT(*) FROM comment WHERE user_id = u.id) AS count_of_comments, reputation FROM users u GROUP BY u.id''' list_of_var = [] return connection.db_mod_list_with_return(query=query, list_of_var=list_of_var)
def get_all_comment(): query = ''' SELECT * FROM comment''' list_of_var = [] return connection.db_mod_list_with_return(query=query, list_of_var=list_of_var)
def get_all_tag_names(): query = ''' SELECT name FROM tag''' list_of_var = [] return connection.db_mod_list_with_return(query=query, list_of_var=list_of_var)