def get_boards(cursor: RealDictCursor): query = """ SELECT * FROM boards ORDER BY id; """ cursor.execute(query) boards = cursor.fetchall() return boards
def get_cards_from_board(cursor: RealDictCursor, board_id): query = """ SELECT * FROM cards WHERE cards.board_id = %(board_id)s ORDER BY id; """ cursor.execute(query, {'board_id': board_id}) return cursor.fetchall()
def add_new_board(cursor: RealDictCursor, new_board_title): query = """ INSERT INTO boards(title) VALUES (%(new_board_title)s) returning id, title; """ cursor.execute(query, {'new_board_title': new_board_title}) new_board_id = cursor.fetchall() return new_board_id
def get_statuses(cursor: RealDictCursor): query = """ SELECT * FROM statuses ORDER BY id; """ cursor.execute(query) statuses = cursor.fetchall() return statuses
def get_questions_of_user(cursor: RealDictCursor, user_id): query = """ SELECT qst.* FROM question as qst INNER JOIN users_activity as act ON qst.id = act.question_id WHERE act.user_id = %(user_id)s """ cursor.execute(query, {'user_id': user_id}) return cursor.fetchall()
def get_answers_of_user(cursor: RealDictCursor, user_id): query = """ SELECT ans.* FROM answer as ans INNER JOIN users_activity as act ON ans.id = act.answer_id WHERE act.user_id = %(user_id)s """ cursor.execute(query, {'user_id': user_id}) return cursor.fetchall()
def get_comments_of_user(cursor: RealDictCursor, user_id): query = """ SELECT com.* FROM comment as com INNER JOIN users_activity as act ON com.id = act.comment_id WHERE act.user_id = %(user_id)s """ cursor.execute(query, {'user_id': user_id}) return cursor.fetchall()
def get_mentors_by_city(cursor: RealDictCursor, city_name: str) -> list: query = f""" SELECT first_name, last_name, city FROM mentor WHERE city LIKE '{city_name}' ORDER BY first_name """ cursor.execute(query) return cursor.fetchall()
def get_application_code_by_id(cursor: RealDictCursor, applicant_id: int) -> list: query = """ SELECT application_code FROM applicant WHERE id = %s """ args = applicant_id cursor.execute(query, (args, )) return cursor.fetchall()
def planets_votes(cursor: RealDictCursor, user_id: int) -> list: query = """ SELECT pv.planet_name planet, COUNT(pv.planet_name) AS count, u.id, pv.user_id FROM planet_votes AS pv LEFT JOIN users u ON pv.user_id = u.id WHERE u.id = %(user_id)s GROUP BY planet, u.id, pv.user_id """ args = {'user_id': user_id} cursor.execute(query, args) return cursor.fetchall()
def greatest_answer_id(cursor: RealDictCursor): query = """ SELECT id FROM answer ORDER BY id DESC LIMIT 1 """ cursor.execute(query) result = cursor.fetchall() return result[0]['id']
def get_boards_data(cursor: RealDictCursor) -> list: """Get id of last added record""" query = """ SELECT * FROM boards """ cursor.execute(query) return cursor.fetchall()
def get_table_question(cursor: RealDictCursor) -> list: """ :return: all data from table question """ query = """ SELECT * FROM question """ cursor.execute(query) return cursor.fetchall()
def comments_for_question_id(cursor: RealDictCursor, user_id: int) -> list: query = """ SELECT comment.*, question.id, question.title FROM comment LEFT JOIN question ON comment.question_id = question.id WHERE comment.user_id = %(user_id)s AND comment.answer_id IS NULL """ args = {'user_id': user_id} cursor.execute(query, args) return cursor.fetchall()
def answers_for_question_id(cursor: RealDictCursor, user_id: int) -> list: query = """ SELECT answer.*, question.id, question.title FROM answer LEFT JOIN question ON answer.question_id = question.id WHERE answer.user_id = %(user_id)s """ args = {'user_id': user_id} cursor.execute(query, args) return cursor.fetchall()
def rename_board(cursor: RealDictCursor, board_id, new_title): query = """ UPDATE boards SET title = %(new_title)s WHERE boards.id = %(board_id)s returning title """ cursor.execute(query, {'new_title': new_title, 'board_id': board_id}) new_board_title_content = cursor.fetchall() return new_board_title_content
def get_applicant_data_by_email_ending(cursor: RealDictCursor, email_ending: str) -> list: query = """ SELECT first_name, last_name, phone_number FROM applicant WHERE email LIKE %s ORDER BY first_name""" name_ap = "%" + email_ending cursor.execute(query, (name_ap, )) return cursor.fetchall()
def get_all_data(cursor: RealDictCursor) -> list: query = """ SELECT id, submission_time as post_time, title, message, view_number as views, vote_number as votes, image FROM question ORDER BY submission_time DESC""" cursor.execute(query) questions = cursor.fetchall() add_answer_snippets(questions) return questions
def get_comments_by_question_id(cursor: RealDictCursor, question_id: int): query = f""" SELECT comment.*, forum_user.id as forum_user_id, forum_user.mail as user_mail, forum_user.reputation as reputation FROM comment LEFT JOIN forum_user ON comment.user_id = forum_user.id WHERE comment.question_id = {question_id} ORDER BY submission_time DESC""" cursor.execute(query) return cursor.fetchall()
def get_applicant_data_by_email(cursor: RealDictCursor, email_ending: str) -> list: query = """ SELECT first_name, last_name, phone_number FROM applicant WHERE email LIKE %s """ args = '%' + email_ending cursor.execute(query, (args, )) return cursor.fetchall()
def get_mentors_by_last_name(cursor: RealDictCursor, last_name: str) -> list: query = """ SELECT first_name, last_name, city FROM mentor WHERE last_name LIKE %s ORDER BY first_name """ args = last_name cursor.execute(query, (args, )) return cursor.fetchall()
def get_data_statuses(cursor: RealDictCursor) -> list: """ :return: all data from statuses table """ query = """ SELECT * FROM statuses """ cursor.execute(query) return cursor.fetchall()
def get_questions_by_tag(cursor: RealDictCursor, tag_id): query = f""" select q.title, q.message, tag.name, q.id from question as q left join question_tag as qt on q.id = qt.question_id join tag ON tag.id = qt.tag_id where tag.id = {tag_id} group by q.title, q.message, tag.name, q.id """ cursor.execute(query) return cursor.fetchall()
def get_table_comment(cursor: RealDictCursor) -> list: """ :return: all data from table comment """ query = """ SELECT * FROM comment """ cursor.execute(query) return cursor.fetchall()
def rename_card(cursor: RealDictCursor, card_id, new_title): query = """ UPDATE cards SET title = %(new_title)s WHERE cards.id = %(card_id)s returning title """ cursor.execute(query, {'card_id': card_id, 'new_title': new_title}) new_card_title = cursor.fetchall() return new_card_title
def get_sorted_questions(cursor: RealDictCursor, criteria_and_direction): if criteria_and_direction[ 0] in QUESTION_HEADERS and criteria_and_direction[1] in [ 'ASC', 'DESC' ]: cursor.execute(f""" SELECT * FROM question ORDER BY {criteria_and_direction[0]} {criteria_and_direction[1]}; """) return cursor.fetchall()
def get_questions_with_specific_tag(cursor: RealDictCursor, tag: str): cursor.execute( """ SELECT * FROM question LEFT JOIN question_tag ON question.id = question_tag.question_id LEFT JOIN tag ON question_tag.tag_id = tag.id WHERE tag.name = %(tag)s; """, {'tag': tag}) return cursor.fetchall()
def search_for_phrase_questions(cursor: RealDictCursor, search_phrase: str): cursor.execute( f""" SELECT DISTINCT question.* FROM question FULL OUTER JOIN answer ON question.id = answer.question_id WHERE question.message ILIKE %(phrase)s OR question.title ILIKE %(phrase)s OR answer.message ILIKE %(phrase)s; """, {'phrase': '%' + search_phrase + '%'}) return cursor.fetchall()
def get_phrase_match_data(cursor: RealDictCursor, phrase: str) -> dict: question_query = """ SELECT question.id, question.submission_time, question.view_number, question.vote_number, question.title, question.message, question.image FROM question FULL JOIN answer ON answer.question_id = question.id WHERE question.title LIKE %(phrase)s OR question.message LIKE %(phrase)s OR answer.message LIKE %(phrase)s""" cursor.execute(question_query, {'phrase': '%' + phrase + '%'}) data = remove_duplicates(cursor.fetchall(), 'id') return data
def get_statuses(cursor: RealDictCursor): query = """ SELECT * FROM status ORDER BY id """ cursor.execute(query) dictStatusesList = [] for entry in cursor.fetchall(): dictStatusesList.append(dict(entry)) return dictStatusesList