def update_db(self): connection = db_connect() cursor = connection.cursor() # Get geocode using Google Maps API # url = "https://maps.googleapis.com/maps/api/geocode/json" params = {'sensor': 'false', 'address': self.name, 'key': 'AIzaSyCZGB4LyytNSiSk3hsmIrgM0ikaOO3NMUs'} # print(params) respond = requests.get(url, params=params) respond_data = respond.json() # print(respond_data) result_data = respond_data['results'] location = result_data[0]['geometry']['location'] self.coordinates = str(location['lat']) + "," + str(location['lng']) query = """UPDATE city SET city_name=%s, city_population=%s, city_coordinates=%s WHERE city_id=%s""" try: cursor.execute(query, (self.name, self.population, self.coordinates, self.id)) connection.commit() status = True except connection.Error as error: print(error) connection.rollback() status = False finally: cursor.close() connection.close() return status
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 league 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
def add_to_db(self): conn = db_connect() cursor = conn.cursor() query_type = """SELECT id FROM person_types WHERE person_type_name = %s""" query_city = """SELECT city_id FROM city WHERE city_name = %s""" query = """INSERT INTO person(person_name, person_birth_date, person_birth_location, person_type) VALUES (%s, %s, %s, %s)""" try: cursor.execute(query_type, (self.type,)) conn.commit() type_id = cursor.fetchone() cursor.execute(query_city, (self.birth_place,)) conn.commit() city_id = cursor.fetchone() cursor.execute(query, (self.name, self.birth_date, city_id, type_id)) conn.commit() status = True except conn.Error as error: print(error) conn.rollback() status = False cursor.close() conn.close() return status
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
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
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
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
def update_db(self): conn = db_connect() cursor = conn.cursor() status = False query_city = """SELECT city_id FROM city WHERE city_name=%s""" query_type = """SELECT id FROM person_types WHERE person_type_name=%s""" query = """UPDATE person SET person_name=%s, person_birth_date=%s, person_birth_location=%s, person_type=%s WHERE person_id=%s""" try: cursor.execute(query_city, (self.birth_place, )) conn.commit() city_id = cursor.fetchone() cursor.execute(query_type, (self.type, )) conn.commit() type_id = cursor.fetchone() cursor.execute( query, (self.name, self.birth_date, city_id, type_id, self.id)) conn.commit() status = True except conn.Error as error: print(error) conn.rollback() status = False finally: cursor.close() conn.close() return status
def update_db(self): connection = db_connect() cursor = connection.cursor() select_team = """SELECT team_id FROM team WHERE team_name = %s""" select_location = """SELECT city_id FROM city WHERE city_name = %s""" statement = """UPDATE stadium SET stadium_name=%s, stadium_team=%s, stadium_location=%s, stadium_capacity=%s WHERE stadium_id=%s""" try: cursor.execute(select_team, (self.team,)) connection.commit() new_team = cursor.fetchone() cursor.execute(select_location, (self.location,)) connection.commit() new_location = cursor.fetchone() cursor.execute(statement, (self.name, new_team, new_location, self.capacity, self.id)) connection.commit() status = True except connection.Error: connection.rollback() status = False cursor.close() connection.close() return status
def update_db(self): conn = db_connect() cursor = conn.cursor() status = False query_city = """SELECT city_id FROM city WHERE city_name=%s""" query_type = """SELECT id FROM person_types WHERE person_type_name=%s""" query = """UPDATE person SET person_name=%s, person_birth_date=%s, person_birth_location=%s, person_type=%s WHERE person_id=%s""" try: cursor.execute(query_city, (self.birth_place, )) conn.commit() city_id = cursor.fetchone() cursor.execute(query_type, (self.type, )) conn.commit() type_id = cursor.fetchone() cursor.execute(query, (self.name, self.birth_date, city_id, type_id, self.id)) conn.commit() status = True except conn.Error as error: print(error) conn.rollback() status = False finally: cursor.close() conn.close() return status
def add_to_db(self): connection = db_connect() cursor = connection.cursor() query_country = """SELECT country_id FROM country WHERE country_name = %s""" query = """INSERT INTO league (league_name, league_country, league_start_date) VALUES (%s, %s, %s)""" try: cursor.execute(query_country, (self.country,)) connection.commit() country_id = cursor.fetchone() cursor.execute(query, (self.name, country_id, self.start_date,)) connection.commit() status = True except connection.Error as error: print(error) connection.rollback() status = False cursor.close() connection.close() return status
def add_to_db(self): connection = db_connect() cursor = connection.cursor() query_capital = """SELECT city_id FROM city WHERE city_name = %s""" query = """INSERT INTO country (country_name, country_population, capital) VALUES (%s, %s, %s)""" try: cursor.execute(query_capital,(self.capital,)) connection.commit() capital_id = cursor.fetchone() cursor.execute(query,(self.name, self.population, capital_id)) connection.commit() status = True except connection.Error as error: print(error) connection.rollback() status = False cursor.close() connection.close() return status
def update_db(self): connection = db_connect() cursor = connection.cursor() query_country = """SELECT country_id FROM country WHERE country_name=%s""" query = """UPDATE league SET league_name=%s, league_country=%s, league_start_date=%s WHERE league_id=%s""" try: cursor.execute(query_country, (self.country, )) connection.commit() country_id = cursor.fetchone() cursor.execute(query, (self.name, country_id, self.start_date, self.id,)) connection.commit() status = True except connection.Error as error: print(error) connection.rollback() status = False cursor.close() connection.close() return status
def add_to_db(self): connection = db_connect() cursor = connection.cursor() # query to get referenced user by its id query_user = """SELECT user_id FROM users WHERE user_name = %s""" # query to add given log tuple to database query = """INSERT INTO log (log_description, log_author, log_time) VALUES (%s, %s, %s)""" try: cursor.execute(query_user, (self.author, )) connection.commit() user_id = cursor.fetchone() cursor.execute(query, ( self.description, user_id, self.time, )) connection.commit() status = True except connection.Error as error: print(error) connection.rollback() status = False cursor.close() connection.close() return status
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
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
def add_to_db(self): connection = db_connect() cursor = connection.cursor() new_team = None new_location = None select_team = """SELECT team_id FROM team WHERE team_name = %s""" select_location = """SELECT city_id FROM city WHERE city_name = %s""" statement = """INSERT INTO stadium (stadium_name, stadium_team, stadium_location, stadium_capacity ) VALUES (%s, %s, %s, %s)""" try: cursor.execute(select_team, (self.team, )) connection.commit() new_team = cursor.fetchone() cursor.execute(select_location, (self.location, )) connection.commit() new_location = cursor.fetchone() cursor.execute(statement, (self.name, new_team, new_location, self.capacity)) connection.commit() status = True except connection.Error: connection.rollback() status = False cursor.close() connection.close() return status
def update_db(self): connection = db_connect() cursor = connection.cursor() query_capital = """ SELECT city_id FROM city WHERE city_name = %s""" query = """UPDATE country SET country_name=%s, country_population=%s, capital=%s WHERE country_id=%s""" try: cursor.execute(query_capital, (self.capital, )) connection.commit() capital_id = cursor.fetchone() cursor.execute(query, ( self.name, self.population, capital_id, self.id, )) connection.commit() status = True except connection.Error as error: print(error) connection.rollback() status = False cursor.close() connection.close() return status
def update_db(self): conn = db_connect() cursor = conn.cursor() status = False query_type = """SELECT id FROM penalty_type WHERE penalty_type_name=%s""" query = """UPDATE penalty SET penalty_given_date=%s, penalty_given_person=%s, penalty_type=%s WHERE penalty_id=%s""" try: cursor.execute(query_type, (self.type, )) conn.commit() type_id = cursor.fetchone() cursor.execute(query, (self.given_date, self.person, type_id, self.id)) conn.commit() print(cursor.mogrify(query, (self.given_date, self.person, type_id, self.id))) status = True except conn.Error as error: print(error) conn.rollback() status = False finally: cursor.close() conn.close() return status
def add_to_db(self): conn = db_connect() cursor = conn.cursor() query_type = """SELECT id FROM penalty_type WHERE penalty_type_name = %s""" query = """INSERT INTO penalty(penalty_type, penalty_given_person, penalty_given_date) VALUES (%s, %s, %s)""" try: cursor.execute(query_type, (self.type,)) conn.commit() type_id = cursor.fetchone() print(self.person, type_id, self.given_date) cursor.execute(query, (type_id, self.person, self.given_date)) conn.commit() status = True except conn.Error as error: print(error) conn.rollback() status = False cursor.close() conn.close() return status
def update_db(self): connection = db_connect() cursor = connection.cursor() query_capital = """ SELECT city_id FROM city WHERE city_name = %s""" query = """UPDATE country SET country_name=%s, country_population=%s, capital=%s WHERE country_id=%s""" try: cursor.execute(query_capital, (self.capital,)) connection.commit() capital_id = cursor.fetchone() cursor.execute(query, (self.name, self.population, capital_id, self.id,)) connection.commit() status = True except connection.Error as error: print(error) connection.rollback() status = False cursor.close() connection.close() return status
def add_to_db(self): connection = db_connect() cursor = connection.cursor() query_capital = """SELECT city_id FROM city WHERE city_name = %s""" query = """INSERT INTO country (country_name, country_population, capital) VALUES (%s, %s, %s)""" try: cursor.execute(query_capital, (self.capital, )) connection.commit() capital_id = cursor.fetchone() cursor.execute(query, (self.name, self.population, capital_id)) connection.commit() status = True except connection.Error as error: print(error) connection.rollback() status = False cursor.close() connection.close() return status
def add_to_db(self): connection = db_connect() cursor = connection.cursor() # query to get referenced user by its id query_user = """SELECT user_id FROM users WHERE user_name = %s""" # query to add given log tuple to database query = """INSERT INTO log (log_description, log_author, log_time) VALUES (%s, %s, %s)""" try: cursor.execute(query_user, (self.author,)) connection.commit() user_id = cursor.fetchone() cursor.execute(query, (self.description, user_id, self.time,)) connection.commit() status = True except connection.Error as error: print(error) connection.rollback() status = False cursor.close() connection.close() return status
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
def add_to_db(self): conn = db_connect() cursor = conn.cursor() query_type = """SELECT id FROM person_types WHERE person_type_name = %s""" query_city = """SELECT city_id FROM city WHERE city_name = %s""" query = """INSERT INTO person(person_name, person_birth_date, person_birth_location, person_type) VALUES (%s, %s, %s, %s)""" try: cursor.execute(query_type, (self.type, )) conn.commit() type_id = cursor.fetchone() cursor.execute(query_city, (self.birth_place, )) conn.commit() city_id = cursor.fetchone() cursor.execute(query, (self.name, self.birth_date, city_id, type_id)) conn.commit() status = True except conn.Error as error: print(error) conn.rollback() status = False cursor.close() conn.close() return status
def add_to_db(self): connection = db_connect() cursor = connection.cursor() new_team = None new_location = None select_team = """SELECT team_id FROM team WHERE team_name = %s""" select_location = """SELECT city_id FROM city WHERE city_name = %s""" statement = """INSERT INTO stadium (stadium_name, stadium_team, stadium_location, stadium_capacity ) VALUES (%s, %s, %s, %s)""" try: cursor.execute(select_team, (self.team,)) connection.commit() new_team = cursor.fetchone() cursor.execute(select_location, (self.location,)) connection.commit() new_location = cursor.fetchone() cursor.execute(statement, (self.name, new_team, new_location, self.capacity)) connection.commit() status = True except connection.Error: connection.rollback() status = False cursor.close() connection.close() return status
def update_db(self): connection = db_connect() cursor = connection.cursor() select_team = """SELECT team_id FROM team WHERE team_name = %s""" select_location = """SELECT city_id FROM city WHERE city_name = %s""" statement = """UPDATE stadium SET stadium_name=%s, stadium_team=%s, stadium_location=%s, stadium_capacity=%s WHERE stadium_id=%s""" try: cursor.execute(select_team, (self.team, )) connection.commit() new_team = cursor.fetchone() cursor.execute(select_location, (self.location, )) connection.commit() new_location = cursor.fetchone() cursor.execute( statement, (self.name, new_team, new_location, self.capacity, self.id)) connection.commit() status = True except connection.Error: connection.rollback() status = False cursor.close() connection.close() return status
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
def get_log_by_id(self, get_id=None): connection = db_connect() cursor = connection.cursor() if get_id is not None: query = """SELECT * FROM log JOIN users ON log.log_author = users.user_id WHERE log_id = %s""" try: cursor.execute(query, (get_id,)) connection.commit() data = cursor.fetchone() if data is not None: self.id = data[0] self.description = data[1] self.time = data[3] self.author = 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 log JOIN users ON log.log_author = users.user_id ORDER BY log_id DESC""" try: cursor.execute(query) connection.commit() array = [] data = cursor.fetchall() for log in data: array.append( { 'id': log[0], 'description': log[1], 'time': log[3].strftime('%d/%m/%Y'), 'author': log[5] } ) cursor.close() connection.close() return array except connection.Error as error: print(error) connection.rollback()
def update_db(self): connection = db_connect() cursor = connection.cursor() query_team = """SELECT team_id FROM team WHERE team_name = %s""" query_league = """SELECT league_id FROM league WHERE league_name = %s""" query_stadium = """SELECT stadium_id FROM stadium WHERE stadium_name = %s""" query_referee = """SELECT person_id FROM person WHERE person_name = %s""" query = """UPDATE matches SET match_team_1=%s, match_team_2=%s, match_league=%s, match_stadium=%s, match_referee=%s, match_date=%s, match_team1_score=%s, match_team2_score=%s WHERE match_id=%s""" try: cursor.execute(query_team, (self.name1,)) connection.commit() team1_id = cursor.fetchone() cursor.execute(query_team, (self.name2,)) connection.commit() team2_id = cursor.fetchone() if team1_id == team2_id: return False cursor.execute(query_league, (self.league,)) connection.commit() league_id = cursor.fetchone() cursor.execute(query_stadium, (self.stadium,)) connection.commit() stadium_id = cursor.fetchone() cursor.execute(query_referee, (self.referee,)) connection.commit() referee_id = cursor.fetchone() cursor.execute(query, (team1_id, team2_id, league_id, stadium_id, referee_id, self.date, self.score1, self.score2, self.id,)) connection.commit() status = True except connection.Error as error: print(error) connection.rollback() status = False cursor.close() connection.close() return status
def get_league_by_id(self, get_id=None): connection = db_connect() cursor = connection.cursor() if get_id is not None: query = """SELECT * FROM league JOIN country ON country.country_id = league.league_country WHERE league_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.start_date = data[3] self.country = 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 league JOIN country ON country.country_id = league.league_country""" try: cursor.execute(query) connection.commit() array = [] data = cursor.fetchall() for league in data: array.append( { 'id': league[0], 'name': league[1], 'start_date': league[3].strftime('%d/%m/%Y'), 'country': league[5] } ) cursor.close() connection.close() return array except connection.Error as error: print(error) connection.rollback()
def get_country_by_id(self, get_id=None): connection = db_connect() cursor = connection.cursor() if get_id is not None: query = """SELECT * FROM country JOIN city ON country.capital=city.city_id WHERE country_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.population = data[2] self.capital = 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 country JOIN city ON country.capital = city.city_id""" try: cursor.execute(query) connection.commit() array = [] data = cursor.fetchall() for country in data: array.append( { 'id': country[0], 'name': country[1], 'population': country[2], 'capital': country[5] } ) cursor.close() connection.close() return array except connection.Error as error: print(error) connection.rollback()
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
def get_log_by_id(self, get_id=None): connection = db_connect() cursor = connection.cursor() if get_id is not None: query = """SELECT * FROM log JOIN users ON log.log_author = users.user_id WHERE log_id = %s""" try: cursor.execute(query, (get_id, )) connection.commit() data = cursor.fetchone() if data is not None: self.id = data[0] self.description = data[1] self.time = data[3] self.author = 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 log JOIN users ON log.log_author = users.user_id ORDER BY log_id DESC""" try: cursor.execute(query) connection.commit() array = [] data = cursor.fetchall() for log in data: array.append({ 'id': log[0], 'description': log[1], 'time': log[3].strftime('%d/%m/%Y'), 'author': log[5] }) cursor.close() connection.close() return array except connection.Error as error: print(error) connection.rollback()
def get_country_by_id(self, get_id=None): connection = db_connect() cursor = connection.cursor() if get_id is not None: query = """SELECT * FROM country JOIN city ON country.capital=city.city_id WHERE country_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.population = data[2] self.capital = 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 country JOIN city ON country.capital = city.city_id""" try: cursor.execute(query) connection.commit() array = [] data = cursor.fetchall() for country in data: array.append({ 'id': country[0], 'name': country[1], 'population': country[2], 'capital': country[5] }) cursor.close() connection.close() return array except connection.Error as error: print(error) connection.rollback()
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()
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()
def recent_trade_points(market_id, limit): db = config.db_connect() dbc = db.cursor() x = [] y = [] first_trade = dbc.execute( """ SELECT trade_id FROM trade WHERE market_id = :market_id ORDER BY trade_id DESC LIMIT :limit OFFSET :limit - 1 """, { 'market_id': market_id, 'limit': limit }).fetchone() c = 0 for trade in dbc.execute( """ SELECT trade_id, price FROM trade WHERE market_id = :market_id AND trade_id >= :trade_id ORDER BY trade_id """, { 'market_id': market_id, 'trade_id': first_trade['trade_id'] }).fetchall(): x.append(c) c = c + 1 y.append(trade[1]) return x, y
def recent_trade_points(market_id, limit): db = config.db_connect() dbc = db.cursor() x = [] y = [] first_trade = dbc.execute(""" SELECT trade_id FROM trade WHERE market_id = :market_id ORDER BY trade_id DESC LIMIT :limit OFFSET :limit - 1 """, { 'market_id': market_id, 'limit': limit }).fetchone() c = 0 for trade in dbc.execute(""" SELECT trade_id, price FROM trade WHERE market_id = :market_id AND trade_id >= :trade_id ORDER BY trade_id """, { 'market_id': market_id, 'trade_id': first_trade['trade_id'] }).fetchall(): x.append(c) c = c + 1 y.append(trade[1]) return x, y
def delete_from_db(self): connection = db_connect() cursor = connection.cursor() statement = """DELETE FROM stadium WHERE stadium_id = %s""" try: cursor.execute(statement, (self.id,)) connection.commit() status = True except connection.Error: connection.rollback() status = False cursor.close() connection.close() return status
def delete_from_db(self): connection = db_connect() cursor = connection.cursor() statement = """DELETE FROM team_stat WHERE team_stat_id = %s""" try: cursor.execute(statement, (self.id, )) connection.commit() status = True except connection.Error: connection.rollback() status = False cursor.close() connection.close() return status
def get_city_by_id(self, city_id=None): conn = db_connect() cursor = conn.cursor() if city_id is None: query = """SELECT * FROM city""" try: cursor.execute(query) conn.commit() except conn.Error as error: print(error) data_array = [] data = cursor.fetchall() for city in data: data_array.append( { 'id': city[0], 'name': city[1], 'coordinates': city[2], 'population': city[3] } ) # print(data) cursor.close() conn.close() return data_array else: query = """SELECT * FROM city WHERE city_id = %s""" try: cursor.execute(query, (city_id, )) conn.commit() except conn.Error as error: print(error) data = cursor.fetchone() if data is not None: self.id = data[0] self.name = data[1] return self
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
def away_stats(): connection = db_connect() cursor = connection.cursor() query_away = """SELECT team_name AS Team, COUNT(match_id) AS away_played, COUNT(case when(match_team2_score > match_team1_score) then 1 else null end) AS away_wins, COUNT(case when(match_team2_score < match_team1_score) then 1 else null end) AS away_loses, COUNT(case when(match_team1_score = match_team2_score) then 1 else null end) AS away_draw, (3*COUNT(case when(match_team2_score > match_team1_score) then 1 else null end)+ COUNT(case when(match_team1_score = match_team2_score) then 1 else null end)) AS away_points, league_name FROM team JOIN matches ON team_id = match_team_2 JOIN league ON match_league = league_id GROUP BY team_id, league_name ORDER BY away_points DESC""" try: cursor.execute(query_away) connection.commit() array = [] data = cursor.fetchall() for x in data: array.append( { 'team': x[0], 'away_played': x[1], 'away_wins': x[2], 'away_loses': x[3], 'away_draw': x[4], 'away_points': x[5], 'league': x[6] } ) cursor.close() connection.close() return array except connection.Error as error: print(error) connection.rollback()
def delete_from_db(self): connection = db_connect() cursor = connection.cursor() query = """DELETE FROM matches WHERE match_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
def add_to_db(self): conn = db_connect() cursor = conn.cursor() status = False query = """INSERT INTO penalty_type(penalty_type_name) VALUES (%s)""" try: cursor.execute(query, (self.type, )) conn.commit() status = True except conn.Error as error: print(error) conn.rollback() status = False finally: cursor.close() conn.close() return status
def update_db(self): connection = db_connect() cursor = connection.cursor() status = False new_league = None new_team = None new_person = None select_league = """SELECT league_id FROM league WHERE league_name = %s""" select_team = """SELECT team_id FROM team WHERE team_name = %s""" select_person = """SELECT person_id FROM person WHERE person_name = %s""" statement = """UPDATE sponsorship SET sponsorship_name=%s, sponsorship_start_date=%s, sponsorship_league=%s, sponsorship_team=%s, sponsorship_person=%s WHERE sponsorship_id=%s""" try: cursor.execute(select_league, (self.league, )) connection.commit() new_league = cursor.fetchone() cursor.execute(select_team, (self.team, )) connection.commit() new_team = cursor.fetchone() cursor.execute(select_person, (self.person, )) connection.commit() new_person = cursor.fetchone() cursor.execute(statement, (self.name, self.start_date, new_league, new_team, new_person, self.id)) connection.commit() status = True except connection.Error: connection.rollback() status = False cursor.close() connection.close() return status
def get_penalty_type(self, type_id=None): conn = db_connect() cursor = conn.cursor() types = None if type_id is None: query_type = """SELECT penalty_type.penalty_type_name FROM penalty_type""" try: cursor.execute(query_type) conn.commit() types = cursor.fetchall() except conn.Error as error: print(error) conn.rollback() cursor.close() conn.close() return types else: query_type = """SELECT penalty_type.penalty_type_name FROM penalty_type WHERE id=%s""" try: cursor.execute(query_type, (type_id, )) conn.commit() types = cursor.fetchone() if types is not None: self.id = type_id self.type = types[0] except conn.Error as error: print(error) conn.rollback() cursor.close() conn.close() return self
def add_to_db(self): conn = db_connect() cursor = conn.cursor() query = """INSERT INTO popularity(team_name, most_popular_match, most_popular_player, supporters) VALUES (%s, %s, %s, %s)""" try: cursor.execute(query, (self.team, self.match, self.player, self.supporters)) conn.commit() status = True except conn.Error as error: print(error) conn.rollback() status = False cursor.close() conn.close() return status
def away_stats(): connection = db_connect() cursor = connection.cursor() query_away = """SELECT team_name AS Team, COUNT(match_id) AS away_played, COUNT(case when(match_team2_score > match_team1_score) then 1 else null end) AS away_wins, COUNT(case when(match_team2_score < match_team1_score) then 1 else null end) AS away_loses, COUNT(case when(match_team1_score = match_team2_score) then 1 else null end) AS away_draw, (3*COUNT(case when(match_team2_score > match_team1_score) then 1 else null end)+ COUNT(case when(match_team1_score = match_team2_score) then 1 else null end)) AS away_points, league_name FROM team JOIN matches ON team_id = match_team_2 JOIN league ON match_league = league_id GROUP BY team_id, league_name ORDER BY away_points DESC""" try: cursor.execute(query_away) connection.commit() array = [] data = cursor.fetchall() for x in data: array.append({ 'team': x[0], 'away_played': x[1], 'away_wins': x[2], 'away_loses': x[3], 'away_draw': x[4], 'away_points': x[5], 'league': x[6] }) cursor.close() connection.close() return array except connection.Error as error: print(error) connection.rollback()
def get_user(self, email=None): conn = db_connect() cursor = conn.cursor() if email is not None: query = """SELECT * FROM users WHERE user_email=%s;""" try: cursor.execute(query, (email,)) conn.commit() except conn.Error as error: print(error) conn.rollback() user_data = cursor.fetchone() if user_data is not None: self.id = user_data[0] self.alias = user_data[1] self.email = user_data[3] self.password_hash = user_data[2] self.user_type = user_data[4] cursor.close() conn.close() return self else: query = """SELECT * FROM users""" try: cursor.execute(query) conn.commit() except conn.Error as error: print(error) conn.rollback() user_data = cursor.fetchall() cursor.close() conn.close() return user_data