def get_user_activities(cursor:RealDictCursor, user_id: int) -> list: id = {'user_id': user_id} question_query = """ SELECT title AS question, id AS question_id FROM question WHERE question.user_id = %(user_id)s""" answer_query = """ SELECT message AS answer, question_id FROM answer WHERE answer.user_id = %(user_id)s""" comment_query = """ SELECT message AS comment, CASE WHEN question_id IS NULL THEN (SELECT question_id FROM answer WHERE id = comment.answer_id) WHEN answer_id IS NULL THEN question_id END AS question_id FROM comment WHERE comment.user_id = %(user_id)s""" cursor.execute(question_query, id) question = cursor.fetchall() cursor.execute(answer_query, id) answer = cursor.fetchall() cursor.execute(comment_query, id) comment = cursor.fetchall() return question, answer, comment
def get_comments(cursor: RealDictCursor) -> list: query = """ SELECT * FROM comment ORDER BY submission_time""" cursor.execute(query) return cursor.fetchall()
def get_emails(cursor:RealDictCursor): query = """ SELECT users.email FROM users; """ cursor.execute(query) return cursor.fetchall()
def get_max_tag_id(cursor: RealDictCursor): query = """ SELECT MAX(id) FROM tag """ cursor.execute(query) [max_value] = cursor.fetchall() return max_value['max']
def get_views(cursor: RealDictCursor, id): query = """ SELECT view_number FROM question WHERE id=%(id)s""" cursor.execute(query, {'id': id}) [view_num] = cursor.fetchall() return int(view_num['view_number'])
def get_question_tags(cursor: RealDictCursor, id) ->list: query = """ SELECT A.* FROM tag A WHERE A.id IN (SELECT B.tag_id from question_tag B WHERE B.question_id = %(input)s) """ cursor.execute(query, {'input':id}) return cursor.fetchall()
def get_answer_user_id(cursor: RealDictCursor, answer_id: int): query = """ SELECT user_id FROM answer WHERE id = %(answer_id)s""" cursor.execute(query, {'answer_id': answer_id}) [result] = cursor.fetchall() return result['user_id']
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}) [data] = cursor.fetchall() return data
def get_question_id_by_answer(cursor: RealDictCursor, answer_id: int): query = """ SELECT question_id FROM answer WHERE id = %(answer_id)s""" cursor.execute(query, {'answer_id': answer_id}) [data] = cursor.fetchall() return data['question_id']
def get_answer_data(cursor:RealDictCursor, email): sql = """ SELECT * FROM answer WHERE email = %(email)s """ cursor.execute(sql,{'email':email}) data = cursor.fetchall() return data
def get_answer_message(cursor: RealDictCursor, search_phrase) -> list: query = """ SELECT message FROM answer WHERE message ILIKE %(search_phrase)s """ cursor.execute(query, {'search_phrase': '%' + search_phrase + '%'}) return cursor.fetchall()
def get_latest_questions(cursor: RealDictCursor) -> list: query = """ SELECT * FROM question ORDER BY submission_time DESC LIMIT 5;""" cursor.execute(query) return cursor.fetchall()
def get_single_answer(cursor: RealDictCursor, id) -> list: query = """ SELECT * FROM answer WHERE id=%(id)s""" cursor.execute(query, {'id': id}) [data] = cursor.fetchall() return data
def get_question_user_id(cursor: RealDictCursor, question_id: int): query = """ SELECT user_id FROM question WHERE id = %(question_id)s""" cursor.execute(query, {'question_id': question_id}) [result] = cursor.fetchall() return result['user_id']
def get_question_vote_count(cursor: RealDictCursor, id): query = """ SELECT vote_number FROM question WHERE id=%(id)s""" cursor.execute(query, {'id': id}) [vote_num_data] = cursor.fetchall() return vote_num_data
def get_answer_vote_count(cursor: RealDictCursor, id) -> list: query = """ SELECT vote_number, question_id FROM answer WHERE id=%(id)s""" cursor.execute(query, {'id': id}) [vote_num_data] = cursor.fetchall() return vote_num_data
def get_list(sql, args): conn = psycopg2.connect(**PG_SQL_LOCAL) cursor = conn.cursor() cursor.execute(sql, args) result = cursor.fetchall() cursor.close() conn.close() return result
def get_players_data(cursor, room_id): query = """ SELECT name, points, is_drawer, word, max_round, round_counter, drawing_time, player.id AS player_id, avatar FROM player JOIN room ON player.room_id = room.id WHERE room.id = %(room_id)s """ cursor.execute(query, {"room_id": room_id}) return cursor.fetchall()
def get_question_id_by_comment(cursor: RealDictCursor, comment_id): query = """ SELECT question_id FROM comment WHERE id = %(comment_id)s;""" cursor.execute(query, {'comment_id': comment_id}) [data] = cursor.fetchall() question_id = data['question_id'] if question_id is None: sec_query = """ SELECT answer_id FROM comment WHERE id = %(comment_id)s;""" cursor.execute(sec_query, {'comment_id': comment_id}) [sec_data] = cursor.fetchall() answer_id = sec_data['answer_id'] question_id = get_question_id_by_answer(answer_id) return question_id
def get_tag_id(cursor: RealDictCursor, name): query = """ SELECT id FROM tag WHERE name = %(name)s """ cursor.execute(query, {'name': name}) [tag_id] = cursor.fetchall() return tag_id['id']
def fancy_search(cursor: RealDictCursor, input): query = """ SELECT message, question_id FROM answer WHERE message ILIKE %(input)s AND %(input)s NOT IN(SELECT title FROM question) AND %(input)s NOT IN(SELECT message FROM question) """ cursor.execute(query, {'input':'%' + input + '%'}) search_data = cursor.fetchall() return search_data
def search(cursor: RealDictCursor, input): query = """ SELECT A.* FROM question A WHERE title ILIKE %(input)s OR message ILIKE %(input)s OR A.id IN (SELECT B.question_id from answer B WHERE message ILIKE %(input)s) """ cursor.execute(query, {'input': '%' + input + '%'}) search_data = cursor.fetchall() return search_data
def get_answers(cursor: RealDictCursor, id) -> list: query = """ SELECT users.email user_email, answer.* FROM users JOIN answer ON users.id = answer.user_id WHERE answer.question_id =%(id)s""" cursor.execute(query, {'id': id}) answer = cursor.fetchall() return answer
def tag_occurence(cursor: RealDictCursor): query = """ SELECT tag.name as tag_name, COUNT(question_tag.question_id) as occurence FROM tag JOIN question_tag ON tag.id = question_tag.tag_id GROUP BY tag_name; """ cursor.execute(query) return cursor.fetchall()
def get_existing_room(cursor): query = ''' SELECT room.id AS room_id, player.name AS player_name, player.id AS player_id FROM room JOIN player ON room.id = player.room_id ORDER BY player.id ASC ''' cursor.execute(query) return cursor.fetchall()
def login(): if request.method == "POST": name = request.form.get("name") db = Database() with db.get_cursor() as cursor: cursor.execute("SELECT * FROM Kullanici WHERE kullanici_adi= %s", (name,)) rows = cursor.fetchall() for row in rows: if request.form.get("password") == str(row[2]): usertype = str(row[3]) return render_template("exams.html", user_type=usertype, exam=createdexams) else: return "<script> alert('Wrong username or password!'); </script>" + render_template("home.html") return render_template('home.html')
def get_rooms(cursor): query = ''' SELECT STRING_AGG(player.id::text, ',') AS player_id, player.room_id, STRING_AGG(player.name, ',') AS player_name, STRING_AGG(player.avatar, ',') AS player_avatar, room.is_open, room.owner_id FROM player JOIN room ON player.room_id = room.id GROUP BY player.room_id, room.is_open, room.owner_id; ''' cursor.execute(query) return cursor.fetchall()
def login(): if request.method == "POST": name = request.form.get("name") db = Database() with db.get_cursor() as cursor: cursor.execute("SELECT * FROM Kullanici WHERE kullanici_adi= %s", (name, )) rows = cursor.fetchall() for row in rows: if request.form.get("password") == str(row[2]): global current_usertype global current_user_id current_user_id = row[0] current_usertype = str(row[3]) return redirect(url_for("show_exams")) else: return "<script> alert('Wrong username or password!'); </script>" + render_template( "home.html") return render_template('home.html')
def list_user_data(cursor:RealDictCursor, user_id=False): query = """ SELECT users.id, users.email, users.registration_date, COUNT(question.id) as asked_questions, COUNT(distinct answer.id) as answer, COUNT(distinct comment.id) as comment, users.reputation FROM users LEFT JOIN question ON users.id = question.user_id LEFT JOIN answer ON users.id = answer.user_id LEFT JOIN comment ON users.id = comment.user_id""" if user_id: query += f""" WHERE users.id={user_id}""" query += """ GROUP BY users.id;""" cursor.execute(query) return cursor.fetchall()
def get_answers_data(cursor: RealDictCursor) -> list: query = """ SELECT * FROM answer""" cursor.execute(query) return cursor.fetchall()