def update_users_db(username, password): query = "UPDATE Users SET password = %s WHERE name = %s" url = get_db_url() with dbapi2.connect(url) as connection: cursor = connection.cursor() cursor.execute(query, (password, username, )) cursor.close()
def delete_stadium_db(stadium_name): query = "DELETE FROM stadium WHERE name = %s " url = get_db_url() with dbapi2.connect(url) as connection: cursor = connection.cursor() cursor.execute(query, (stadium_name, )) cursor.close()
def insert_users_db(user): query = "INSERT INTO USERS (name, password) VALUES(%s, %s)" url = db_url.get_db_url() with dbapi2.connect(url) as connection: cursor = connection.cursor() cursor.execute(query, (user.name, user.password)) cursor.close()
def delete_users_db(username): query = "DELETE FROM Users WHERE name = %s " url = get_db_url() with dbapi2.connect(url) as connection: cursor = connection.cursor() cursor.execute(query, (username, )) cursor.close()
def insert_stadiums_db(stadium): query = "INSERT INTO STADIUM (name) VALUES(%s)" url = get_db_url() with dbapi2.connect(url) as connection: cursor = connection.cursor() cursor.execute(query, (stadium.name, )) cursor.close() return True
def update_teams_db(team_name, old_team_name): query = "UPDATE Team SET name = %s WHERE name = %s" url = get_db_url() with dbapi2.connect(url) as connection: cursor = connection.cursor() cursor.execute(query, (team_name, old_team_name)) cursor.close() return True
def insert_teams_db(team): query = "INSERT INTO TEAM (name, rating) VALUES(%s, %s)" url = get_db_url() with dbapi2.connect(url) as connection: cursor = connection.cursor() cursor.execute(query, (team.name, team.rating)) cursor.close() return True
def update_stadiums_db(stadium_old, stadium_new): query = "UPDATE Stadium SET name = %s WHERE name = %s" url = get_db_url() with dbapi2.connect(url) as connection: cursor = connection.cursor() cursor.execute(query, (stadium_new, stadium_old)) cursor.close() return True
def delete_players_db(user_name): user_id = get_user_id_with_username(user_name) query = "DELETE FROM player WHERE user_id = %s " url = get_db_url() with dbapi2.connect(url) as connection: cursor = connection.cursor() cursor.execute(query, (user_id, )) cursor.close()
def get_users_db(): query = "SELECT * FROM Users" url = db_url.get_db_url() #"dbname='postgres' user='******' host='localhost' password='******'" with dbapi2.connect(url) as connection: cursor = connection.cursor() cursor.execute(query) users = cursor.fetchall() cursor.close() return users
def get_user_id_with_username(user_name): query = "SELECT user_id FROM Users WHERE name = %s" url = db_url.get_db_url() with dbapi2.connect(url) as connection: cursor = connection.cursor() cursor.execute(query, (user_name, )) user_id = cursor.fetchone() cursor.close() return user_id
def get_match_db(): query = "SELECT * FROM Match" url = get_db_url() with dbapi2.connect(url) as connection: cursor = connection.cursor() cursor.execute(query) users = cursor.fetchall() cursor.close() return users
def get_stad_id_with_stad_name(stadium_name): query = "SELECT stadium_id FROM Stadium WHERE name = %s" url = get_db_url() with dbapi2.connect(url) as connection: cursor = connection.cursor() cursor.execute(query, (stadium_name,)) stad_id = cursor.fetchone() cursor.close() return stad_id
def get_team_players_with_team_id(team_id): query = "SELECT distinct p.name, p.rating, p.age FROM Team as t, Player as p WHERE p.team_id = %s" url = get_db_url() with dbapi2.connect(url) as connection: cursor = connection.cursor() cursor.execute(query, (team_id, )) players = cursor.fetchall() cursor.close() return players
def get_stadiums_db(): query = "SELECT * FROM Stadium" url = get_db_url() with dbapi2.connect(url) as connection: cursor = connection.cursor() cursor.execute(query) stads = cursor.fetchall() cursor.close() return stads
def update_players_db(player, user_name, team_name): user_id = get_user_id_with_username(user_name) team_id = get_team_id_with_teamname(team_name) query = "UPDATE Player SET name = %s, age = %s, team_id = %s WHERE user_id = %s" url = get_db_url() with dbapi2.connect(url) as connection: cursor = connection.cursor() cursor.execute(query, (player.name, player.age, team_id, user_id)) cursor.close() return True
def get_team_id_with_teamname(team_name): query = "SELECT team_id FROM Team WHERE name = %s" url = get_db_url() with dbapi2.connect(url) as connection: cursor = connection.cursor() cursor.execute(query, (team_name, )) team_id = cursor.fetchone() cursor.close() print("team_id: ", team_id) return team_id
def insert_players_db(player, user_name, team_name): user_id = get_user_id_with_username(user_name) team_id = get_team_id_with_teamname(team_name) query = "INSERT INTO PLAYER (name, age, user_id, team_id ) VALUES(%s, %s, %s, %s )" url = get_db_url() with dbapi2.connect(url) as connection: cursor = connection.cursor() cursor.execute(query, (player.name, player.age, user_id, team_id)) cursor.close() return True
def get_user_pw_with_username(user_name): query = "SELECT password FROM Users WHERE name = %s" url = db_url.get_db_url() with dbapi2.connect(url) as connection: cursor = connection.cursor() cursor.execute(query, (user_name, )) password = cursor.fetchone() print(password) cursor.close() return password
def get_players_db(): query = """select * from player INNER JOIN team ON team.team_id = player.team_id""" url = get_db_url() with dbapi2.connect(url) as connection: cursor = connection.cursor() cursor.execute(query) users = cursor.fetchall() cursor.close() return users
def insert_match_db(match): team1_id = get_team_id_with_teamname(match.team1_name) team2_id = get_team_id_with_teamname(match.team2_name) query = "INSERT INTO MATCH (team1_id, team2_id) VALUES(%s, %s) RETURNING match_id;" url = get_db_url() with dbapi2.connect(url) as connection: cursor = connection.cursor() cursor.execute(query, ( team1_id, team2_id, )) match_id = cursor.fetchone() cursor.close() return match_id
def get_player_with_username(user_name): user_id = get_user_id_with_username(user_name) query = "select count(*) from player where user_id = %s" url = db_url.get_db_url() with dbapi2.connect(url) as connection: cursor = connection.cursor() cursor.execute(query, (user_id, )) exists = cursor.fetchone() print("Exists:", exists) cursor.close() if exists[0] == 1: return True else: return False
def insert_appointments_db(appointment): query = "INSERT INTO APPOINTMENT (name, match_id, stadium_id, start_time, end_time, date) VALUES(%s, %s, %s, %s, %s, %s)" url = get_db_url() with dbapi2.connect(url) as connection: cursor = connection.cursor() cursor.execute(query, ( appointment.name, appointment.match_id, appointment.stadium_id, appointment.start_time, appointment.end_time, appointment.date, )) cursor.close() return True
def update_appointments_db(appointment_id, username): user_id = get_user_id_with_username(username) query = """ UPDATE MATCH SET team2_id = (select team_id from player where user_id = %s) WHERE match_id = (select match_id from APPOINTMENT where appointment_id = %s)""" url = get_db_url() with dbapi2.connect(url) as connection: cursor = connection.cursor() cursor.execute(query, ( user_id, appointment_id, )) cursor.close() return True
def check_profile_exists(user_name): user_id = db_usr.get_user_id_with_username(user_name) query = "SELECT CASE WHEN EXISTS ( SELECT * FROM Player WHERE user_id = %s ) " \ "THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END" url = db_url.get_db_url() with dbapi2.connect(url) as connection: cursor = connection.cursor() cursor.execute(query, (user_id, )) result = cursor.fetchone() result = result[0] cursor.close() if result == "1": print("yes, exists:", result) else: print("not exists:", result) return result