Parts Implemented by Mert Şeker¶
Database Operations for Each Entity¶
For each database operations of entities, appropriate SQL queries are written and they are executed within the functions in the .py class files.
Team¶
Team tuples have three columns; id, name and coach. Coach is a foreign key to the person table.
Get Team By Id¶
In order to get teams and use them in functions, the primary key(team_id) is used. A dictionary is created with the chosen team’s data and it is returned. You can see how this operation is done in the code below:
def get_team_by_id(self, get_id=None): connection = db_connect() cursor = connection.cursor() if get_id is not None: query = """SELECT t.team_id, t.team_name, t.team_couch,person.person_name FROM team AS t LEFT OUTER JOIN person ON person.person_id = t.team_couch WHERE team_id = %s""" try: cursor.execute(query, (get_id,)) connection.commit() data = cursor.fetchone() if data is not None: self.id = data[0] self.name = data[1] self.couch = data[2] cursor.close() connection.close() return self else: cursor.close() connection.close() return None except connection.Error as error: print(error) connection.rollback() else: query = """SELECT team.team_id, team.team_name,team.team_couch,person.person_id,person.person_name FROM team LEFT OUTER JOIN person ON person.person_id = team.team_couch""" try: cursor.execute(query, (get_id,)) connection.commit() array = [] data = cursor.fetchall() for team in data: array.append( { 'id': team[0], 'name': team[1], 'couch': team[4] } ) cursor.close() connection.close() return array except connection.Error as error: print(error) connection.rollback()
Add Team To Database¶
In order to add team tuples to the database, INSERT INTO queries are used and executed. The foreign keys are selected from the referenced tables by id.You can see it in the code below:
def add_to_db(self): connection = db_connect() cursor = connection.cursor() select_person = """SELECT person_id FROM person WHERE person_name = %s""" # query to add given team tuple to database query = """INSERT INTO team (team_name, team_couch) VALUES (%s, %s)""" try: cursor.execute(select_person, (self.couch,)) connection.commit() new_person = cursor.fetchone() cursor.execute(query, (self.name, new_person)) connection.commit() status = True except connection.Error as error: print(error) connection.rollback() status = False cursor.close() connection.close() return status
Delete Team From Database¶
The team to be deleted is selected by id and deleted by using DELETE FROM query. You can see it in the code below:
def delete_from_db(self): connection = db_connect() cursor = connection.cursor() query = """DELETE FROM team WHERE team_id = %s""" try: cursor.execute(query, (self.id,)) connection.commit() status = True except connection.Error as error: print(error) connection.rollback() status = False cursor.close() connection.close() return status
Update Team¶
The team to be updated is selected by id and updated by the UPDATE query. Just like in add operation,the foreign keys are selected from the referenced table by id. You can see it in the code below:
def update_db(self): connection = db_connect() cursor = connection.cursor() select_person = """SELECT person_id FROM person WHERE person_name = %s""" query = """UPDATE team SET team_name=%s, team_couch=%s WHERE team_id=%s""" try: cursor.execute(select_person, (self.couch,)) connection.commit() person_id = cursor.fetchone() cursor.execute(query, (self.name, person_id, self.id)) connection.commit() status = True except connection.Error as error: print(error) connection.rollback() status = False cursor.close() connection.close() return status
Player¶
Player tuples have four columns; id,name, team and number of goals. Team is a foreign key to the teams table.
Get Player By Id¶
In order to get players and use them in functions, the primary key(player_id) is used. A dictionary is created with the chosen player’s data and it is returned. You can see how this operation is done in the code below:
def get_player_by_id(self, get_id=None): connection = db_connect() cursor = connection.cursor() if get_id is not None: query = """SELECT * FROM player JOIN team ON team.team_id = player.player_team WHERE player_id = %s""" try: cursor.execute(query, (get_id,)) connection.commit() data = cursor.fetchone() if data is not None: self.id = data[0] self.name = data[1] self.goals = data[3] self.team = data[5] cursor.close() connection.close() return self else: cursor.close() connection.close() return None except connection.Error as error: print(error) connection.rollback() else: query = """SELECT * FROM player JOIN team ON team.team_id = player.player_team""" try: cursor.execute(query) connection.commit() except connection.Error as error: print(error) connection.rollback() array = [] data = cursor.fetchall() for player in data: array.append( { 'id': player[0], 'name': player[1], 'goals': player[3], 'team': player[5] } ) print(array) cursor.close() connection.close() return array
Add Player To Database¶
In order to add player tuples to the database, INSERT INTO queries are used and executed. The foreign keys are selected from the referenced tables by id.You can see it in the code below:
def add_to_db(self): connection = db_connect() cursor = connection.cursor() # query to get referenced team by its id query_team = """SELECT team_id FROM team WHERE team_name = %s""" # query to add given player tuple to database query = """INSERT INTO player (player_name, player_team, player_goals) VALUES (%s, %s, %s)""" try: cursor.execute(query_team, (self.team,)) connection.commit() team_id = cursor.fetchone() cursor.execute(query, (self.name, team_id, self.goals,)) connection.commit() status = True except connection.Error as error: print(error) connection.rollback() status = False cursor.close() connection.close() return status
Delete Player From Database¶
The player to be deleted is selected by id and deleted by using DELETE FROM query. You can see it in the code below:
def delete_from_db(self): connection = db_connect() cursor = connection.cursor() query = """DELETE FROM player WHERE player_id = %s""" try: cursor.execute(query, (self.id, )) connection.commit() status = True except connection.Error as error: print(error) connection.rollback() status = False cursor.close() connection.close() return status
Update Player¶
The player to be updated is selected by id and updated by the UPDATE query. Just like in add operation,the foreign keys are selected from the referenced table by id. You can see it in the code below:
def update_db(self): connection = db_connect() cursor = connection.cursor() query_team = """SELECT team_id FROM team WHERE team_name=%s""" query = """UPDATE player SET player_name=%s, player_team=%s, player_goals=%s WHERE player_id=%s""" try: cursor.execute(query_team, (self.team, )) connection.commit() team_id = cursor.fetchone() cursor.execute(query, (self.name, team_id, self.goals, self.id,)) connection.commit() status = True except connection.Error as error: print(error) connection.rollback() status = False finally: cursor.close() connection.close() return status
Tournament¶
Tournament tuples have seven columns; id,name,number of matches,start date,end date,country and prize. Country is a foreign key to the countries table.
Get Tournament By Id¶
In order to get tournaments and use them in functions, the primary key(tournament_id) is used. A dictionary is created with the chosen tournament’s data and it is returned. You can see how this operation is done in the code below:
def get_tournament_by_id(self, get_id=None): connection = db_connect() cursor = connection.cursor() if get_id is not None: query = """SELECT * FROM tournament JOIN country ON country.country_id = tournament.tournament_country WHERE tournament_id = %s""" try: cursor.execute(query, (get_id,)) connection.commit() data = cursor.fetchone() if data is not None: self.id = data[0] self.name = data[1] self.matches = data[2] self.start_date = data[3] self.end_date = data[4] self.country = data[8] self.prize = data[6] cursor.close() connection.close() return self else: cursor.close() connection.close() return None except connection.Error as error: print(error) connection.rollback() else: query = """SELECT * FROM tournament JOIN country ON country.country_id = tournament.tournament_country""" try: cursor.execute(query) connection.commit() except connection.Error as error: print(error) connection.rollback() array = [] data = cursor.fetchall() for tournament in data: array.append( { 'id': tournament[0], 'name': tournament[1], 'matches': tournament[2], 'start_date': tournament[3].strftime('%d/%m/%Y'), 'end_date': tournament[4].strftime('%d/%m/%Y'), 'country': tournament[8], 'prize': tournament[6] } ) cursor.close() connection.close() return array
Add Tournament To Database¶
In order to add tournament tuples to the database, INSERT INTO queries are used and executed. The foreign keys are selected from the referenced tables by id.You can see it in the code below:
def add_to_db(self): connection = db_connect() cursor = connection.cursor() # query to get referenced country by its id query_country = """SELECT country_id FROM country WHERE country_name = %s""" # query to add given tournament tuple to database query = """INSERT INTO tournament (tournament_name, tournament_matches, tournament_start_date, tournament_end_date, tournament_country, tournament_prize) VALUES (%s, %s, %s, %s, %s, %s)""" try: cursor.execute(query_country, (self.country,)) connection.commit() country_id = cursor.fetchone() cursor.execute(query, (self.name, self.matches, self.start_date, self.end_date, country_id, self.prize)) connection.commit() status = True except connection.Error as error: print(error) connection.rollback() status = False cursor.close() connection.close() return status
Delete Tournament From Database¶
The tournament to be deleted is selected by id and deleted by using DELETE FROM query. You can see it in the code below:
def delete_from_db(self): connection = db_connect() cursor = connection.cursor() query = """DELETE FROM tournament WHERE tournament_id = %s""" try: cursor.execute(query, (self.id, )) connection.commit() status = True except connection.Error as error: print(error) connection.rollback() status = False cursor.close() connection.close() return status
Update Tournament¶
The tournament to be updated is selected by id and updated by the UPDATE query. Just like in add operation,the foreign keys are selected from the referenced table by id. You can see it in the code below:
def update_db(self): connection = db_connect() cursor = connection.cursor() query_country = """SELECT country_id FROM country WHERE country_name=%s""" query = """UPDATE tournament SET tournament_name=%s, tournament_matches=%s, tournament_start_date=%s, tournament_end_date=%s, tournament_country=%s, tournament_prize=%s WHERE tournament_id=%s""" try: cursor.execute(query_country, (self.country, )) connection.commit() country_id = cursor.fetchone() cursor.execute(query, (self.name, self.matches, self.start_date, self.end_date, country_id, self.prize, self.id,)) connection.commit() status = True except connection.Error as error: print(error) connection.rollback() status = False finally: cursor.close() connection.close() return status