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 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_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
