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_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 post_hander(post):
for key in post:
cursor.execute('select * from COUNTER where word=%s', (key,))
if cursor.rowcount != 0:
row = cursor.fetchone()
cursor.execute('UPDATE COUNTER SET ENCOUNTERS = %s WHERE word=%s', (row[1] + 1, key))
else:
cursor.execute('INSERT INTO COUNTER (WORD, ENCOUNTERS) VALUES (%s, %s)', (key, 0))
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_drawer(cursor):
query = """
SELECT id, name
FROM player
WHERE is_drawer = true
"""
cursor.execute(query)
return cursor.fetchone()
def get_solution(cursor, room_id):
query = '''
SELECT word
FROM room
WHERE id = %(room_id)s
'''
cursor.execute(query, {'room_id': room_id})
return cursor.fetchone()['word']
def insert_new_room(cursor):
query = '''
INSERT INTO room
(id)
VALUES (DEFAULT)
RETURNING id
'''
cursor.execute(query)
return cursor.fetchone()['id']
def update_points(cursor, player_id):
query = '''
UPDATE player
SET points = points + 1
WHERE id = %(player_id)s
RETURNING player.points
'''
cursor.execute(query, {'player_id': player_id})
return cursor.fetchone()['points']
def get_word(cursor, word_id, room_id):
query = '''
SELECT text
FROM words
WHERE id = %(word_id)s
'''
cursor.execute(query, {'word_id': word_id})
word = cursor.fetchone()['text']
insert_word(room_id, word)
return word
def insert_new_player(cursor, player_name, room_id, avatar, is_drawer=False):
drawer_boolean = 'TRUE' if is_drawer else 'FALSE'
query = '''
INSERT INTO player
(id, name, avatar, room_id, is_drawer)
VALUES (DEFAULT, {username}, {avatar}, {room_id}, {drawer_boolean})
RETURNING id
'''
cursor.execute(
sql.SQL(query).format(username=sql.Literal(player_name),
avatar=sql.Literal(avatar),
room_id=sql.Literal(room_id),
drawer_boolean=sql.SQL(drawer_boolean)))
return cursor.fetchone()['id']
def init():
try:
global connection
if connection is not None:
return
connection = psycopg2.connect(user="******",
password="******",
host="mywarmplace.tk",
port="18636",
database="hospital")
cursor = connection.cursor()
# Print PostgreSQL version
cursor.execute("SELECT version();")
record = cursor.fetchone()
print("You are connected to ", record, "\n")
except (Exception, psycopg2.Error) as error:
print("Error while connecting to PostgreSQL", error)
connection = None
cursor = None