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)
