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
