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)