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