def refresh_image(cursor, user_id, image): query = ''' UPDATE player SET avatar = %(image)s WHERE id = %(user_id)s ''' cursor.execute(query, {'user_id': user_id, 'image': image})
def get_comments(cursor: RealDictCursor) -> list: query = """ SELECT * FROM comment ORDER BY submission_time""" cursor.execute(query) return cursor.fetchall()
def write_answer_data(cursor: RealDictCursor, new_answer): sql = """ INSERT INTO answer VALUES (%s, %s, %s, %s, %s, %s, %s); """ cursor.execute(sql, (new_answer['id'], new_answer['submission'],new_answer['vote'], new_answer['question_id'], new_answer['message'], new_answer['filename'], new_answer['user_id']))
def update_answer(cursor: RealDictCursor, id: int, message: str): query = """ UPDATE answer SET message = %(message)s WHERE id = %(id)s """ cursor.execute(query, {'id':id, 'message': message})
def delete_tag(cursor: RealDictCursor, question_id, tag_id): query = """ DELETE FROM question_tag WHERE question_id = %(question_id)s AND tag_id = %(tag_id)s """ cursor.execute(query, {'question_id': question_id, 'tag_id': tag_id})
def insert_owner_id_to_room(cursor, player_id, room_id): query = ''' UPDATE room SET owner_id = %(player_id)s WHERE id = %(room_id)s ''' cursor.execute(query, {'room_id': room_id, 'player_id': player_id})
def modify(sql, args): conn = psycopg2.connect(**PG_SQL_LOCAL) cursor = conn.cursor() cursor.execute(sql, args) conn.commit() cursor.close() conn.close()
def get_emails(cursor:RealDictCursor): query = """ SELECT users.email FROM users; """ cursor.execute(query) return cursor.fetchall()
def close_room(cursor, room_id): query = ''' UPDATE room SET is_open = FALSE WHERE id = %(room_id)s ''' cursor.execute(query, {'room_id': room_id})
def approve(cursor: RealDictCursor, id: int): query = """ UPDATE answer SET accepted = TRUE WHERE id= %(id)s """ cursor.execute(query, {'id': id})
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_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_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_name_by_id(cursor, owner_id): query = """ SELECT name FROM player WHERE id = %(owner_id)s """ cursor.execute(query, {'owner_id': owner_id}) return cursor.fetchone()['name']
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_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_one(sql, args): conn = psycopg2.connect(**PG_SQL_LOCAL) cursor = conn.cursor() cursor.execute(sql, args) result = cursor.fetchone() cursor.close() conn.close() return result
def insert_word(cursor, room_id, word): query = ''' UPDATE room SET word = %(word)s WHERE id = %(room_id)s ''' cursor.execute(query, {'room_id': room_id, 'word': word})
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_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_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 write_question_data(cursor: RealDictCursor, new_question): sql = """ INSERT INTO question VALUES (%s, %s, %s, %s, %s, %s, %s, %s); """ cursor.execute(sql, (new_question['id'], new_question['submission'], new_question['view'], new_question['vote'], new_question['title'], new_question['message'], new_question['filename'], new_question['user_id']))
def get_username(cursor, user_id): query = ''' SELECT name FROM player WHERE id = %(user_id)s; ''' cursor.execute(query, {'user_id': user_id}) return cursor.fetchone()
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_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_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_drawer(cursor): query = """ SELECT id, name FROM player WHERE is_drawer = true """ cursor.execute(query) return cursor.fetchone()
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_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 add_tag_to_question(cursor: RealDictCursor, question_id, tag_id): query=""" INSERT INTO question_tag VALUES (%(question_id)s, %(tag_id)s) """ try: cursor.execute(query, {'question_id': question_id, 'tag_id': tag_id}) except: pass
#query = '''CREATE TABLE customers(id SERIAL PRIMARY KEY, name VARCHAR, age INTEGER);''' #cursor.execute(query) # SQL query insert data namedict =({'name':'Dam', 'age': 37}, {'name':'Dober', 'age': 35}) #query = '''INSERT into customers (name, age) values(%(name)s, %(age)i)''', namedict #cursor.executemany('''INSERT into customers (name, age) values(%(name)s, %(age)s)''', namedict) #conn.commit() query = '''Select * from customers''' cursor.execute(query) #rows = cursor.fetchone() print('\n rows:\n') res = [] D = {} for row in cursor: res.append(row) D[row[1]]= row[2] cursor.close() conn.close() print(res) print(D)