def uninitDb():
statement="""DROP TABLE PLAYERSTATISTICS, MATCHES, PLAYERS, COACHES, REFEREES, TEAMS, TOURNAMENTS, USERS, MATCHSTATISTICS, TECHNICMEMBERS, STADIUMS CASCADE"""
cursor = create_connection()
cursor.execute(statement)
cursor.connection.commit()
close_connection(cursor)
return render_template('home.html')
def update_coach(id, name_update, gender_update, nationality_update, birth_date_update, current_team_update):
cursor = create_connection()
statement = """UPDATE COACHES SET NAME = '{}', GENDER = '{}', NATIONALITY = '{}', BIRTH_DATE = '{}', CURRENT_TEAM = '{}' WHERE ID = {}""".format(name_update, gender_update, nationality_update, birth_date_update, current_team_update,id)
cursor.execute(statement)
cursor.connection.commit()
close_connection(cursor)
def update_matchstatistic(id, home_team_update, away_team_update, match_date_update, score_update, referee_update):
cursor = create_connection()
statement = """UPDATE MATCHSTATISTICS SET HOME_TEAM = '{}', AWAY_TEAM = '{}', MATCH_DATE = '{}', SCORE = '{}', REFEREE = '{}' WHERE ID = {}""".format( home_team_update, away_team_update, match_date_update, score_update, referee_update, id)
cursor.execute(statement)
cursor.connection.commit()
close_connection(cursor)
def update_tournament(id, nameUpdate, yearUpdate, winnerUpdate, best_playerUpdate):
cursor = create_connection()
statement = """UPDATE TOURNAMENTS SET NAME = '{}', YEAR = '{}', WINNER = '{}', BEST_PLAYER = {} WHERE ID={} """.format(nameUpdate, yearUpdate, winnerUpdate, best_playerUpdate, id)
cursor.execute(statement)
cursor.connection.commit()
close_connection(cursor)
def update_referee(id, name_update, gender_update, nationality_update, birth_date_update, times_match_update):
cursor = create_connection()
statement = """UPDATE REFEREES SET NAME = '{}', GENDER = '{}', NATIONALITY = '{}', BIRTH_DATE = '{}', TIMES_MATCH = '{}' WHERE ID = {}""".format(name_update, gender_update, nationality_update, birth_date_update, times_match_update,id)
cursor.execute(statement)
cursor.connection.commit()
close_connection(cursor)
def update_user(id, username_update, password_update):
cursor = create_connection()
statement = """UPDATE USERS SET USERNAME = '******', PASSWORD = '******' WHERE ID = {}""".format(username_update, password_update,id)
cursor.execute(statement)
cursor.connection.commit()
close_connection(cursor)
def update_playerstatistic(id, matches_played_update, matches_won_update, win_rate_update, average_score_update, player_update):
cursor = create_connection()
statement = """UPDATE playerstatistics SET matches_played = '{}', matches_won = '{}', win_rate = '{}', average_score = '{}', player = '{}' WHERE ID = {}""".format( matches_played_update, matches_won_update, win_rate_update, average_score_update, player_update, id)
cursor.execute(statement)
cursor.connection.commit()
close_connection(cursor)
def delete_coach(id):
cursor = create_connection()
statement = """DELETE FROM COACHES WHERE ID={}""".format(id)
cursor.execute(statement)
cursor.connection.commit()
close_connection(cursor)
def delete_tournament(id):
cursor = create_connection()
statement = """DELETE FROM TOURNAMENTS WHERE ID={}""".format(id)
cursor.execute(statement)
cursor.connection.commit()
close_connection(cursor)
def update_match(id, tournamentUpdate, team1Update, team2Update, scoreUpdate):
cursor = create_connection()
statement = """UPDATE MATCHES SET TOURNAMENT = '{}', TEAM1 = '{}', TEAM2 = '{}', SCORE = '{}' WHERE ID={} """.format(tournamentUpdate, team1Update, team2Update, scoreUpdate, id)
cursor.execute(statement)
cursor.connection.commit()
close_connection(cursor)
def delete_matchstatistic(id):
cursor = create_connection()
statement = """DELETE FROM MATCHSTATISTICS WHERE ID={}""".format(id)
cursor.execute(statement)
cursor.connection.commit()
close_connection(cursor)
def delete_stadium(id):
cursor = create_connection()
statement = """DELETE FROM STADIUMS WHERE ID={}""".format(id)
cursor.execute(statement)
cursor.connection.commit()
close_connection(cursor)
def delete_playerstatistic(id):
cursor = create_connection()
statement = """DELETE FROM playerstatistics WHERE ID={}""".format(id)
cursor.execute(statement)
cursor.connection.commit()
close_connection(cursor)
def add_new_user(username, password):
cursor = create_connection()
cursor.execute("INSERT INTO USERS (USERNAME, PASSWORD) VALUES (%s, %s)", (username, password))
cursor.connection.commit()
close_connection(cursor)
return True
def showJointTables():
cursor = create_connection()
statement= """ SELECT MATCHES.ID, TOURNAMENTS.NAME, t1.NATION, t2.NATION, MATCHES.SCORE FROM MATCHES INNER JOIN TOURNAMENTS ON TOURNAMENTS.ID=MATCHES.TOURNAMENT INNER JOIN TEAMS t1 ON t1.ID = MATCHES.TEAM1 INNER JOIN TEAMS t2 ON t2.ID=MATCHES.TEAM2 """
cursor.execute(statement)
matches = cursor.fetchall()
cursor.connection.commit()
close_connection(cursor)
return matches
def add_new_match(tournament, team1, team2, score):
cursor = create_connection()
cursor.execute("INSERT INTO matches (tournament, team1, team2, score) VALUES (%s, %s, %s, %s)", (tournament, team1, team2, score))
cursor.connection.commit()
close_connection(cursor)
return True
def join_tables():
cursor = create_connection()
statement = """ SELECT STADIUMS.ID, STADIUMS.NAME, STADIUMS.CAPACITY, CITY, TEAMS.NATION FROM STADIUMS INNER JOIN TEAMS ON TEAMS.ID=STADIUMS.COUNTRY """
cursor.execute(statement)
stadiums = cursor.fetchall()
cursor.connection.commit()
close_connection(cursor)
return stadiums
def get_playerstatistics():
cursor = create_connection()
cursor.execute("SELECT * FROM playerstatistics;")
playerstatistics = cursor.fetchall()
close_connection(cursor)
return playerstatistics
def get_users():
cursor = create_connection()
cursor.execute("SELECT * FROM USERS;")
users = cursor.fetchall()
close_connection(cursor)
return users
def get_coaches():
cursor = create_connection()
cursor.execute("SELECT * FROM coaches;")
coaches = cursor.fetchall()
close_connection(cursor)
return coaches
def get_referees():
cursor = create_connection()
cursor.execute("SELECT * FROM referees;")
referees = cursor.fetchall()
close_connection(cursor)
return referees
def join_tables():
cursor = create_connection()
statement= """ SELECT playerstatistics.ID, matches_played, matches_won, win_rate, average_score, players.NAME FROM playerstatistics INNER JOIN players ON players.ID=playerstatistics.player """
cursor.execute(statement)
playerstatistics = cursor.fetchall()
cursor.connection.commit()
close_connection(cursor)
return playerstatistics
def add_new_playerstatistic(matches_played, matches_won, win_rate, average_score, player):
cursor = create_connection()
cursor.execute("INSERT INTO playerstatistics (matches_played, matches_won, win_rate, average_score, player) VALUES (%s, %s, %s, %s, %s)", (matches_played, matches_won, win_rate, average_score, player))
cursor.connection.commit()
close_connection(cursor)
return True
def get_stadiums():
cursor = create_connection()
cursor.execute("SELECT * FROM stadiums;")
stadiums = cursor.fetchall()
close_connection(cursor)
return stadiums
def showJointTables():
cursor = create_connection()
statement= """ SELECT TOURNAMENTS.ID, TOURNAMENTS.NAME, YEAR, TEAMS.NATION , PLAYERS.NAME FROM TOURNAMENTS INNER JOIN PLAYERS ON PLAYERS.ID=TOURNAMENTS.BEST_PLAYER INNER JOIN TEAMS ON TEAMS.ID=TOURNAMENTS.WINNER """
cursor.execute(statement)
tournaments = cursor.fetchall()
cursor.connection.commit()
close_connection(cursor)
return tournaments
def add_new_referee(name, gender, nationality, birth_date, times_match):
cursor = create_connection()
cursor.execute("INSERT INTO referees (name, gender, nationality, birth_date, times_match) VALUES (%s, %s, %s, %s, %s)", (name, gender, nationality, birth_date, times_match))
cursor.connection.commit()
close_connection(cursor)
return True
def add_new_tournament(name, year, winner, best_player):
cursor = create_connection()
cursor.execute("INSERT INTO tournaments (name, year, winner, best_player) VALUES (%s, %s, %s, %s)", (name, year, winner, best_player))
cursor.connection.commit()
close_connection(cursor)
return True
def join_tables():
cursor = create_connection()
statement= """ SELECT COACHES.ID, COACHES.NAME, COACHES.GENDER, NATIONALITY, BIRTH_DATE, TEAMS.NATION FROM COACHES INNER JOIN TEAMS ON TEAMS.ID=COACHES.CURRENT_TEAM """
cursor.execute(statement)
coaches = cursor.fetchall()
cursor.connection.commit()
close_connection(cursor)
return coaches
def update_stadium(id, name_update, capacity_update, city_update, country_update):
cursor = create_connection()
statement = """UPDATE STADIUMS SET NAME = '{}', CAPACITY = '{}', CITY = '{}', COUNTRY = '{}' WHERE ID = {}""".format(
name_update, capacity_update, city_update, country_update, id
)
cursor.execute(statement)
cursor.connection.commit()
close_connection(cursor)
def add_new_coach(name, gender, nationality, birth_date, current_team):
cursor = create_connection()
cursor.execute("INSERT INTO coaches (name, gender, nationality, birth_date, current_team) VALUES (%s, %s, %s, %s, %s)", (name, gender, nationality, birth_date, current_team))
cursor.connection.commit()
close_connection(cursor)
return True
def put_local(cod_local):
try:
# Establish the connection and creation of the cursor
connection = create_connection()
cur = connection.cursor()
# Get the Data as Json
data_json = request.get_json()
# Encode to "" instead of '
data_json = str(data_json).replace("'", '"')
# Creating the SQL Command
encoded_command = (
f"{UPDATE(definers[1], cod_local, data_json)}")
print(encoded_command)
# Executing the SQL Command
cur.execute(encoded_command)
return jsonify({
"Message": (f"{definers[1]} updated with success!")
}), 201
except (Exception, psycopg2.Error) as error:
# Convert error to string, break the string on "!"
error = str(error)
error = error.split("\n")
return jsonify({
"Message": {
"Message": (f"There was an error creating the {definers[1]}!"),
"Error": error[0]
}
}), 500
finally:
# commiting and closing database connection.
if(connection):
commit_destroy_connection(connection, cur)
def get_comsumption(cod_comsumption):
try:
# Establish the connection and creation of the cursor
connection = create_connection()
cur = connection.cursor()
# Creating the SQL Command
encoded_command = (f"{GET_SINGLE(definers[1],cod_comsumption)}")
print(encoded_command)
cur.execute(encoded_command)
# Fetching all the records from the cursor
database_record = cur.fetchall()
print(database_record)
# Returning the records complete list
return {
"Message": (f"{definers[1]} returned with success!"),
"Record": database_record
}, 200
except (Exception, psycopg2.Error) as error:
# Convert error to string, break the string on "!"
error = str(error)
error = error.split("\n")
return jsonify({
"Message": {
"Message": (f"There was an error getting the {definers[1]}!"),
"Error": error[0]
}
}), 500
finally:
# commiting and closing database connection.
if (connection):
commit_destroy_connection(connection, cur)
