def get_wins_equals_defeats(self, my_team, oponent):
conn = DbConnection().create_connection(DbConnection.database)
sql_wins = ''' SELECT result FROM results WHERE team_id=? AND opponent=? AND result="v" '''
sql_equals = ''' SELECT result FROM results WHERE team_id=? AND opponent=? AND result="e" '''
sql_defeats = ''' SELECT result FROM results WHERE team_id=? AND opponent=? AND result="d" '''
wins_list = []
equals_list = []
defeats_list = []
data = []
with conn:
cursor1 = conn.cursor()
cursor2 = conn.cursor()
cursor3 = conn.cursor()
cursor1.execute(sql_wins,
[self.team_dictionary_pl.get(my_team), oponent])
cursor2.execute(sql_defeats,
[self.team_dictionary_pl.get(my_team), oponent])
cursor3.execute(sql_equals,
[self.team_dictionary_pl.get(my_team), oponent])
for i, row in enumerate(cursor1):
wins_list.append(row)
for i, row in enumerate(cursor2):
defeats_list.append(row)
for i, row in enumerate(cursor3):
equals_list.append(row)
nr_of_wins = len(wins_list)
nr_of_equals = len(equals_list)
nr_of_defeats = len(defeats_list)
data.append(nr_of_wins)
data.append(nr_of_equals)
data.append(nr_of_defeats)
cursor1.close()
cursor2.close()
cursor3.close()
return data
def get_data(self, my_team, oponent):
conn = DbConnection().create_connection(DbConnection.database)
sql = ''' SELECT score FROM results WHERE team_id=? AND opponent=?'''
with conn:
cursor = conn.cursor()
print(self.team_dictionary_pl.get(my_team))
cursor.execute(sql,
[self.team_dictionary_pl.get(my_team), oponent])
score = []
g_g = []
g_t = []
data = []
for i, row in enumerate(cursor):
sc = row[0]
goals_given = sc.split(':')[0]
g_g.append(int(goals_given))
goals_taken = sc.split(':')[1]
g_t.append(int(goals_taken))
score.append(sc)
total_of_goals_given = sum(g_g)
total_of_goals_take = sum(g_t)
data.append(total_of_goals_given)
data.append(total_of_goals_take)
data.extend(self.get_wins_equals_defeats(my_team, oponent))
cursor.close()
# goals_given goals_taken nr_of_wins nr_of_equals nr_of_defeats
return data
def get_info(self, team_id):
conn = DbConnection().create_connection(DbConnection.database)
sql = ''' SELECT * FROM extra_data WHERE team_id=? LIMIT 10; '''
with conn:
cursor = conn.cursor()
result = {}
cursor.execute(sql, [team_id])
counter = 0
for i, row in enumerate(cursor):
year = row[1]
investment = row[2]
age = row[3]
wins = row[4]
draws = row[5]
defeats = row[6]
goals = row[7]
place = row[8]
info = [
year, investment, age, wins, draws, defeats, goals, place
]
season = 'season' + str(counter)
result[season] = info
counter = counter + 1
cursor.close()
return result
def __function_to_return_user_id(self, email):
conn = DbConnection().create_connection(DbConnection.database)
sql = ''' SELECT id FROM user WHERE email=?'''
with conn:
cursor = conn.cursor()
cursor.execute(sql, [email])
cursor_fetch = cursor.fetchall()
cursor.close()
return cursor_fetch[0][0]
def get_team_name(self, team_id):
conn = DbConnection().create_connection(DbConnection.database)
sql = ''' SELECT name FROM team WHERE id=? '''
with conn:
cursor = conn.cursor()
cursor.execute(sql, [team_id])
name = cursor.fetchall()
cursor.close()
return name[0][0]
def get_all_teams(self):
conn = DbConnection().create_connection(DbConnection.database)
sql = ''' SELECT * FROM team '''
with conn:
cursor = conn.cursor()
cursor.execute(sql, [])
all_teams = cursor.fetchall()
cursor.close()
return all_teams
def __get_hashed_password(self, email):
conn = DbConnection().create_connection(DbConnection.database)
sql = ''' SELECT password FROM user WHERE email=? '''
with conn:
cursor = conn.cursor()
cursor.execute(sql, [email])
cursor_fetch = cursor.fetchall()
cursor.close()
return cursor_fetch
def get_team_live(self, team_name, team_id):
conn = DbConnection().create_connection(DbConnection.database)
sql = ''' SELECT DISTINCT opponent FROM results WHERE opponent LIKE ? AND team_id=? LIMIT 1'''.format(
team_name)
with conn:
cursor = conn.cursor()
cursor.execute(sql, ['%' + team_name + '%', team_id])
nr_of_opponents = cursor.fetchall()
cursor.close()
return nr_of_opponents
def get_team_b(self, my_team):
conn = DbConnection().create_connection(DbConnection.database)
sql = ''' SELECT DISTINCT name FROM team WHERE name LIKE ? LIMIT 1'''
with conn:
cursor = conn.cursor()
cursor.execute(sql, ['%' + my_team + '%'])
my_team = cursor.fetchall()
cursor.close()
print(my_team)
return my_team
def register_user(self, username, email, password, admin):
conn = DbConnection().create_connection(DbConnection.database)
sql = ''' INSERT INTO user(username, email, password, admin) VALUES (?,?,?,?)'''
sql1 = ''' '''
with conn:
cursor = conn.cursor()
cursor.execute(sql, [username, email, password, admin])
cursor_last_row = cursor.lastrowid
cursor.close()
return cursor_last_row
def get_team_result(self, team_id):
conn = DbConnection().create_connection(DbConnection.database)
sql = ''' SELECT * FROM results WHERE team_id = ? '''
with conn:
cursor = conn.cursor()
cursor.execute(sql, [team_id])
all_teams_encoded = []
for i, row in enumerate(cursor):
t = [row[0], row[1], row[2], row[3], row[4], row[5]]
all_teams_encoded.append(t)
cursor.close()
return np.asarray(all_teams_encoded)
def get_results_for_a_specific_team(self, team_a_id, team_b):
conn = DbConnection().create_connection(DbConnection.database)
sql = ''' SELECT * FROM results WHERE team_id=? AND opponent=?'''
with conn:
cursor = conn.cursor()
cursor.execute(sql, [team_a_id, team_b])
all_teams = []
for i, row in enumerate(cursor):
t = [row[0], row[1], row[2], row[3], row[4], row[5]]
all_teams.append(t)
cursor.close()
return np.asarray(all_teams)
def check_subscriptions(self, email):
user_id = self.__function_to_return_user_id(email)
sql = ''' SELECT team_id FROM subscriptions WHERE user_id=? '''
conn = DbConnection().create_connection(DbConnection.database)
result = []
with conn:
cursor = conn.cursor()
cursor.execute(sql, [user_id])
team_list = cursor.fetchall()
cursor.close()
for team in team_list:
result.append(team[0])
return result
def add_teams(self, team_list, current_user_email):
print("list in repository : " + str(team_list) + current_user_email)
user_id = self.__function_to_return_user_id(current_user_email)
sql = ''' INSERT INTO subscriptions(user_id, team_id) VALUES(?,?)'''
conn = DbConnection().create_connection(DbConnection.database)
if not team_list:
return False
else:
with conn:
cursor = conn.cursor()
for team in team_list:
team_id = self.team_dictionary_pl.get(team)
cursor.execute(sql, [user_id, team_id])
cursor.close()
return True
def load_extra_data(self):
conn = DbConnection().create_connection(DbConnection.database)
with conn:
for team in self.help_dicty_pl:
for result in self.__read_from_extra_csv(team):
res = tuple(result.values())
self.create_extra_data(conn, res)
def load_results(self):
conn = DbConnection().create_connection(DbConnection.database)
with conn:
for teams in PremierLeague:
for result in self.__read_from_csv(teams.name):
res = tuple(result.values())
self.create_results(conn, res)
def get_goals_result(self, my_team, oponent):
conn = DbConnection().create_connection(DbConnection.database)
sql = ''' SELECT score,result FROM results WHERE team_id=? AND opponent=?'''
with conn:
cursor = conn.cursor()
score_result = []
cursor.execute(sql,
[self.team_dictionary_pl.get(my_team), oponent])
for i, row in enumerate(cursor):
li = []
score = row[0].split(':')
li.append(score[0])
li.append(score[1])
li.append(row[1])
score_result.append(li)
cursor.close()
return score_result
def get_extra_data(self, team_id):
conn = DbConnection().create_connection(DbConnection.database)
sql = ''' SELECT * FROM extra_data WHERE team_id=? '''
with conn:
cursor = conn.cursor()
cursor.execute(sql, [team_id])
all_data = []
for i, row in enumerate(cursor):
investments = int(row[2])
age = int(row[3])
wins = int(row[4])
equals = int(row[5])
defeats = int(row[6])
goals = int(row[7])
place = int(row[8])
t = [investments, age, wins, equals, defeats, goals, place]
all_data.append(t)
cursor.close()
return np.asarray(all_data)
def login_user(self, email, password):
bcrypt = Bcrypt()
user = User.query.filter_by(email=email).first()
if not self.__get_hashed_password(email):
return [user, False]
if bcrypt.check_password_hash(
self.__get_hashed_password(email)[0][0], password) is True:
conn = DbConnection().create_connection(DbConnection.database)
sql = ''' SELECT * FROM user WHERE email=? '''
with conn:
cursor = conn.cursor()
cursor.execute(sql, [email])
cursor_fetch = cursor.fetchall()
cursor.close()
if not cursor_fetch:
return [user, False]
return [user, True]
else:
return [user, False]
def load_teams(self):
conn = DbConnection().create_connection(DbConnection.database)
with conn:
for team in PremierLeague:
team_name = team.name.replace("_info", "")
self.create_teams(conn, team_name)