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()
