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)
