def insert_to_database_question(cursor: RealDictCursor, new_question):
if new_question.get('image') is not None:
query = """
INSERT INTO question (submission_time, view_number, vote_number, title, message, image)
VALUES (%(submission_time)s, %(view_number)s, %(vote_number)s, %(title)s, %(message)s, %(image)s )
RETURNING id;
"""
cursor.execute(
query, {
'submission_time': new_question['submission_time'],
'view_number': new_question['view_number'],
'vote_number': new_question['vote_number'],
'title': new_question['title'],
'message': new_question.get('message'),
'image': new_question.get('image')
})
return cursor.fetchone().get('id')
else:
query = """
INSERT INTO question (submission_time, view_number, vote_number, title, message)
VALUES (%(submission_time)s, %(view_number)s, %(vote_number)s, %(title)s, %(message)s)
RETURNING id;
"""
cursor.execute(
query, {
'submission_time': new_question['submission_time'],
'view_number': new_question['view_number'],
'vote_number': new_question['vote_number'],
'title': new_question['title'],
'message': new_question.get('message')
})
return cursor.fetchone().get('id')
def get_length_of_questions(cursor: RealDictCursor):
query = """
SELECT COUNT(*)
FROM question
"""
cursor.execute(query)
return cursor.fetchone()
def get_user_id_by_activity(cursor: RealDictCursor, table, item_id: int):
query = f"""
SELECT user_id
FROM {table}
WHERE id = {item_id}"""
cursor.execute(query)
return cursor.fetchone()['user_id']
def get_question_id_by_tag_id(cursor: RealDictCursor, tag_id: int):
query = f"""
SELECT question_id
FROM question_tag
WHERE tag_id = {tag_id}"""
cursor.execute(query)
return cursor.fetchone()["question_id"]
def get_question_id_by_answer_id(cursor: RealDictCursor, answer_id: int):
query = f"""
SELECT question_id
FROM answer
WHERE id = {answer_id}"""
cursor.execute(query)
return cursor.fetchone()["question_id"]
def get_answer_id_for_comment(cursor: RealDictCursor, data_id: str):
query = "SELECT answer_id FROM comment WHERE id = {0}".format(data_id)
cursor.execute(query)
try:
return cursor.fetchone()['answer_id']
except TypeError:
return None
def get_data_by_email(cursor: RealDictCursor, email) -> list:
query = """
SELECT *
FROM users
WHERE email = %s"""
cursor.execute(query, (email,))
return cursor.fetchone()
def add_new_entry(cursor: RealDictCursor, table_name: str, form_data=None, request_files=None, question_id=None, user_id=None):
complete_dict_data = init_complete_dict_entry(table_name, form_data, request_files, question_id)
columns_sql_str = ", ".join([str(key) for key in complete_dict_data.keys()])
values_sql_str = ", ".join(f'%({key})s' for key in complete_dict_data.keys())
comment = f"""
INSERT INTO
{table_name} ({columns_sql_str})
VALUES ({values_sql_str})
RETURNING id
"""
cursor.execute(comment, complete_dict_data)
entry_id = str(cursor.fetchone()['id'])
if table_name == 'answer':
add_user_answer_activity(user_id, entry_id)
else:
add_user_question_activity(user_id, entry_id)
if request_files['image'].filename:
data_handler.save_image(request_files['image'], table_name+'s', entry_id)
return entry_id
def create_board(cursor: RealDictCursor, title):
cursor.execute("""
INSERT INTO boards(title)
VALUES (%(title)s)
RETURNING id;
""", {'title': title})
return cursor.fetchone().get('id')
def get_user_by_id(cursor: RealDictCursor, user_name):
cursor.execute("""
SELECT *
FROM users
WHERE username = %(user_name)s
""", {'user_name': user_name})
return cursor.fetchone()
def check_username_exists(cursor: RealDictCursor, username: str):
query = """
SELECT * FROM users
WHERE username = %(username)s;
"""
cursor.execute(query, {'username': username})
return cursor.fetchone()
def get_display_question(cursor: RealDictCursor, question_id) -> list:
cursor.execute(f"""
SELECT *
FROM question
WHERE id = {question_id}
""")
return cursor.fetchone()
def get_question_id_by_answer_id(cursor: RealDictCursor, answer_id) -> list:
cursor.execute(f"""
SELECT question_id
FROM answer
WHERE id = {answer_id}
""")
return cursor.fetchone()
def create_new_user(cursor: RealDictCursor, login, password):
registration_time = get_datetime_now()
command = f"""
INSERT INTO users
(login, password, registration_date)
VALUES (%(login)s, %(password)s, %(registration_time)s)
RETURNING id
"""
cursor.execute(
command, {
'login': login,
'registration_time': registration_time,
'password': str(password)[2:-1]
})
new_user_id = cursor.fetchone()['id']
query = f"""
INSERT INTO users_statistics
(user_id, question_count, answer_count, comment_count, reputation_value)
VALUES (%(new_user_id)s, 1, 1, 1, 1)
"""
cursor.execute(query, {'new_user_id': new_user_id})
def get_last_board_id(cursor: RealDictCursor) -> list:
query = """
SELECT MAX(id)
FROM boards
"""
cursor.execute(query)
return cursor.fetchone()
def get_comment_by_id(cursor: RealDictCursor, comment_id: int):
query = """
SELECT * FROM comment
WHERE id = %(comment_id)s
"""
cursor.execute(query, {'comment_id': comment_id})
return cursor.fetchone()
def has_question_comment(cursor: RealDictCursor, question_id: int):
query = f"""
SELECT id
FROM comment
WHERE question_id = {question_id}"""
cursor.execute(query)
return cursor.fetchone()
def get_login(cursor: RealDictCursor, email, password):
print(email)
print(password)
user = "******".format(email, password)
cursor.execute(user)
return cursor.fetchone()
def has_answer_comment(cursor: RealDictCursor, answer_id: int):
query = f"""
SELECT id
FROM comment
WHERE answer_id = {answer_id}"""
cursor.execute(query)
return cursor.fetchone()
def get_votes_by_planet_name_and_user_id(cursor: RealDictCursor, planet_name, user_id) -> list:
query = """
SELECT planet_name
FROM planet_votes
WHERE planet_name = %s and user_id = %s"""
cursor.execute(query, (planet_name, user_id,))
return cursor.fetchone()
def get_questions_by_id(cursor: RealDictCursor, id):
query = """
SELECT *
FROM question
WHERE id=%(id)s"""
cursor.execute(query, {'id': id})
return cursor.fetchone()
def get_user(cursor: RealDictCursor, username):
cursor.execute(
"""
SELECT * FROM "user"
WHERE username like %(username)s
""", {'username': username})
return cursor.fetchone()
def create_board(cursor: RealDictCursor, title) -> list:
query = """
INSERT INTO boards (title) values (%s) RETURNING *
"""
cursor.execute(query, (title, ))
result = cursor.fetchone()
return result
def get_user(cursor: RealDictCursor, username):
query = """
SELECT * FROM "user"
WHERE username = %(username)s
"""
cursor.execute(query, {'username': username})
return cursor.fetchone()
def add_player_and_scores(cursor: RealDictCursor, player_name: dict):
query = f"""
INSERT INTO players ("name", scores)
VALUES ((%(name)s), 0)
RETURNING id
"""
cursor.execute(query, player_name)
return cursor.fetchone()
def count_answers_for_question(cursor: RealDictCursor, question_id: int):
cursor.execute(
"""
SELECT COUNT(*)
FROM answer
WHERE question_id = %(q_id)s;
""", {'q_id': question_id})
return cursor.fetchone()
def get_question_owner(cursor: RealDictCursor, question_id: int):
query = """
SELECT user_id
FROM question
WHERE id = %(question_id)s
"""
cursor.execute(query, {'question_id': question_id})
return cursor.fetchone()
def get_question_owner_based_on_answer(cursor: RealDictCursor, answer_id: int):
query = """
SELECT question.user_id
FROM question JOIN answer ON question.id = answer.question_id
WHERE answer.id = %(answer_id)s
"""
cursor.execute(query, {'answer_id': answer_id})
return cursor.fetchone()
def get_user_by_id(cursor: RealDictCursor, user_id: int):
query = """
SELECT u.id, u.email, u.registration_time, u.reputation, COUNT(DISTINCT q.*) AS questions_number, COUNT(DISTINCT a.*) AS answers_number, COUNT(DISTINCT c.*) AS comments_number
FROM users u LEFT JOIN question q ON u.id = q.user_id LEFT JOIN answer a ON q.user_id = a.user_id LEFT JOIN comment c ON u.id = c.user_id
WHERE u.id = %(user_id)s GROUP BY u.id ORDER BY u.id;
"""
cursor.execute(query, {"user_id": user_id})
return cursor.fetchone()
def get_user_data(cursor: RealDictCursor, email: str):
cursor.execute(
f"""
SELECT *
FROM users
WHERE email = (%(email)s);
""", {'email': email})
return cursor.fetchone()
