def delete_applicant(cursor: RealDictCursor, code: str) -> list: query = """ DELETE FROM applicant WHERE application_code = %s """ args = code cursor.execute(query, (args, )) return "Done"
def get_applicant(cursor: RealDictCursor) -> list: query = """ SELECT * FROM applicant ORDER BY first_name """ cursor.execute(query) return cursor.fetchall()
def update_phone(cursor: RealDictCursor, code, phone): command = """ UPDATE applicant SET phone_number = %(phone)s WHERE application_code = %(code)s; """ param = {'code': code, 'phone': phone} cursor.execute(command, param)
def get_mentors_by_first_name(cursor: RealDictCursor, first_name: str) -> list: query = """ SELECT first_name, last_name, city FROM mentor WHERE first_name = %(first_name)s ORDER BY first_name""" cursor.execute(query, {'first_name': first_name}) return cursor.fetchall()
def delete_applicant(cursor:RealDictCursor, code: int): command = """ DELETE FROM applicant WHERE email = %(code)s """ param = {'code': code} cursor.execute(command,param)
def get_mentors_by_city(cursor: RealDictCursor, city_name: str): query = """ SELECT first_name, last_name, city FROM mentor WHERE city = %(city_name)s ORDER BY first_name""" cursor.execute(query, {'city_name': city_name}) return cursor.fetchall()
def get_last_board(cursor: RealDictCursor): query = """ SELECT id, title, open FROM boards ORDER BY id DESC LIMIT 1 """ cursor.execute(query) return cursor.fetchall()
def get_comment_by_id(cursor: RealDictCursor, id) -> list: query = """ SELECT * FROM comment WHERE question_id =%(id)s """ cursor.execute(query, {'id': id}) return cursor.fetchall()
def execute(self, sql, args=None): BaseModel.logger.info(self.mogrify(sql, args)) try: RealDictCursor.execute(self, sql, args) except Exception, e: BaseModel.logger.error("%s: %s" % (e.__class__.__name__, e)) raise
def get_user_info(cursor: RealDictCursor, email) -> list: query = f""" SELECT * FROM users WHERE username = '******' """ cursor.execute(query) return cursor.fetchall()
def update_statuses(cursor: RealDictCursor, boards_id: int, statuses_id: int, ordered: int, id: int) -> list: query = """ UPDATE cards SET boards_id = %(boards_id)s, statuses_id = %(statuses_id)s, ordered = %(ordered)s WHERE id = %(id)s """ args = {'boards_id': boards_id, 'statuses_id': statuses_id, 'ordered': ordered, 'id': id} cursor.execute(query, args)
def search_questions(cursor: RealDictCursor, s_t) -> list: search_expression = f"%{s_t}%" query = """ SELECT * FROM question WHERE message ILIKE %(search)s ORDER BY submission_time""" cursor.execute(query, {'search': search_expression}) return cursor.fetchall()
def get_tag_id(cursor: RealDictCursor, tag_name): query = """ SELECT id FROM tag WHERE name = %(tag_name)s """ param = {"tag_name": tag_name} cursor.execute(query, param) return cursor.fetchone()
def get_tags_name(cursor: RealDictCursor, tag_id): query = """ SELECT name FROM tag WHERE id = %(tag_id)s """ param = {"tag_id": tag_id} cursor.execute(query, param) return cursor.fetchone()
def get_tags(cursor: RealDictCursor, question_id): query = """ SELECT * FROM question_tag WHERE question_id = %(question_id)s; """ param = {'question_id': question_id} cursor.execute(query, param) return cursor.fetchall()
def delete_question_tag(cursor: RealDictCursor, question_id): command = """ DELETE FROM question_tag WHERE question_id =%(id)s """ param = {"id": str(question_id)} cursor.execute(command, param) return None
def leave_feedback(cursor: RealDictCursor, username, message): cursor.execute( """ INSERT INTO feedback(username, message) VALUES (%(username)s, %(message)s) """, { 'username': username, 'message': message })
def get_answers_for_question(cursor: RealDictCursor, question_id: int): cursor.execute( """ SELECT * FROM answer WHERE question_id = %(q_id)s ORDER BY accepted DESC, vote_number DESC, submission_time DESC; """, {'q_id': question_id}) return cursor.fetchall()
def edit_comment(cursor: RealDictCursor, new_record: dict): query = """ UPDATE comment SET message = %s, submission_time = %s, edited_number = %s WHERE id = %s """ cursor.execute( query, (new_record.get("message"), new_record.get("submission_time"), new_record.get("edited_number"), new_record.get("id")))
def get_user(cursor: RealDictCursor, user_data): query = f""" SELECT id, login from users WHERE login=%(login)s AND password=%(password)s; """ cursor.execute(query, user_data) return cursor.fetchone()
def add_new_board(cursor: RealDictCursor, board_data): command = f""" INSERT INTO board(title, private) VALUES (%(title)s, 0) RETURNING * """ cursor.execute(command, board_data) return dict(cursor.fetchone())
def add_new_column(cursor: RealDictCursor, column_data): command = f""" INSERT INTO status(title) VALUES (%(title)s) RETURNING * """ cursor.execute(command, {'title': column_data['title']}) return cursor.fetchone()
def get_tag_from_question(cursor: RealDictCursor, question_id): query = f""" SELECT "name" FROM tag INNER JOIN question_tag ON tag.id=question_tag.tag_id WHERE question_id = {question_id} """ cursor.execute(query) return cursor.fetchall()
def update_question(cursor: RealDictCursor, question_id: str): message = request.form.get('message') title = request.form.get('title') query = """ UPDATE question SET message = %(m)s, title = %(t)s WHERE id = %(q_i)s """ cursor.execute(query, {'m': message, 'q_i': question_id, 't': title})
def check_question_author_id(cursor: RealDictCursor, question_id: int) -> int: query = f""" SELECT user_id FROM question WHERE {question_id} = id """ cursor.execute(query) return cursor.fetchone()
def delete_tag(cursor: RealDictCursor, question_id: int, tag_id: int): cursor.execute( """ DELETE FROM question_tag WHERE question_id = %(question_id)s AND tag_id = %(tag_id)s; """, { 'question_id': question_id, 'tag_id': tag_id })
def toggle_answer_acceptance(cursor: RealDictCursor, question_id: int, answer_id: int): query = f""" UPDATE answer as a SET accepted = NOT accepted WHERE {answer_id} = a.id AND {question_id} = a.question_id """ cursor.execute(query)
def get_all_data(cursor: RealDictCursor): query = f""" SELECT name, scores FROM players ORDER BY scores DESC FETCH FIRST 5 ROWS ONLY """ cursor.execute(query) return cursor.fetchall()
def get_all_question(cursor: RealDictCursor, limit): query = f""" SELECT * FROM question ORDER BY submission_time LIMIT %(limit)s """ cursor.execute(query, {'limit': limit}) return cursor.fetchall()
def insert_registration(cursor: RealDictCursor, users: dict): query = """ INSERT INTO users (username, password) VALUES (%(u_name)s, %(p_word)s);""" cursor.execute(query, { 'u_name': users['username'], 'p_word': users['password'] }) return
def get_comments_for_question(cursor: RealDictCursor, question_id_int: int): query = """ SELECT id, submission_time as post_time, message, edited_count FROM comment WHERE question_id=%(question_id)s """ cursor.execute(query, {'question_id': question_id_int}) return cursor.fetchall()