def create_Schedule(row):
schedule = Schedule()
array = row.split(";")
date = array[0]
team1Name = array[1]
team2Name = array[2]
team1 = Team()
team2 = Team()
datetime_object = datetime.strptime(date, '%d/%m/%Y %H:%M')
team1.name = team1Name
team2.name = team2Name
schedule.scheduled_date = datetime_object
schedule.home_team = team1
schedule.visitor_team = team2
return schedule
def save_teams():
# teams = Team.objects.all().delete()
teams = Team.objects.all()
if not teams:
teamList = team.TeamList()
for x in range(0, 29):
val = teamList.info().__getitem__(x)
team_summary = team.TeamSummary(val['TEAM_ID'])
teamApp = Team()
# print(team.TeamSummary(val['TEAM_ID']).info()[0])
# teamApp.name = val['DISPLAY_FIRST_LAST']
teamApp.name = team_summary.info()[0]['TEAM_NAME']
teamApp.id_nba = val['TEAM_ID']
division = Division.objects.get(
name=team_summary.info()[0]['TEAM_DIVISION'])
teamApp.division = division
teamApp.fundation_date = team_summary.info()[0]['MIN_YEAR']
teamApp.owner = team_summary.info()[0]['MIN_YEAR']
teamApp.city = team_summary.info()[0]['TEAM_CITY']
teamApp.save()
update_team_stats(True)
def import_data_from_test_csv(db_session, db_uri):
import pandas as pd
from sqlalchemy import create_engine
from app.models import City, Venue, Team, Official
# read raw matches csv files
df = pd.read_csv('test_files/matches.csv', parse_dates=['date'])
# save cities and venues
city_venue_df = df[['city', 'venue']].drop_duplicates()
zip_city_venue = zip(city_venue_df['city'], city_venue_df['venue'])
for cv in zip_city_venue:
city = City()
city.name = cv[0]
db_session.add(city)
db_session.commit()
venue = Venue()
venue.city_id = city.id
venue.name = cv[1]
db_session.add(venue)
db_session.commit()
# save teams
team_set1 = set(df['team1'].drop_duplicates())
team_set2 = set(df['team2'].drop_duplicates())
all_teams = team_set1.union(team_set2)
for team in all_teams:
team_model = Team()
team_model.name = team
db_session.add(team_model)
# save umpires
umpire_set1 = set(df['umpire1'].drop_duplicates())
umpire_set2 = set(df['umpire2'].drop_duplicates())
all_umpires = umpire_set1.union(umpire_set2)
for umpire in all_umpires:
umpire_model = Official()
umpire_model.name = umpire
db_session.add(umpire_model)
db_session.commit()
# create a dict with key and name mappings for
# cities, venues, teams and officials
cities = db_session.query(City).all()
cities_dict = {city.name: city.id for city in cities}
venues = db_session.query(Venue).all()
venues_dict = {venue.name: venue.id for venue in venues}
teams = db_session.query(Team).all()
teams_dict = {team.name: team.id for team in teams}
officials = db_session.query(Official).all()
officials_dict = {official.name: official.id for official in officials}
# cities = df_matches['city'].drop_duplicates()
# cities_dict = {x: y for y,x in enumerate(cities)}
db_session.close()
# change city, venue, team1, team2, umpire1, umpire2
# names to their corresponding 'ids' from database
df['city'] = df['city'].apply(lambda x: cities_dict[x])
df['venue'] = df['venue'].apply(lambda x: venues_dict[x])
df['team1'] = df['team1'].apply(lambda x: teams_dict[x])
df['team2'] = df['team2'].apply(lambda x: teams_dict[x])
df['toss_winner'] = df['toss_winner'].apply(lambda x: teams_dict[x])
df['winner'] = df['winner'].apply(lambda x: teams_dict[x])
df['umpire1'] = df['umpire1'].apply(lambda x: officials_dict[x])
df['umpire2'] = df['umpire2'].apply(lambda x: officials_dict[x])
# create sqlalchemy engine
engine = create_engine(db_uri)
# Insert dataframe into 'Matches' table
df.to_sql('Matches', con=engine, if_exists='replace', index=False)
def load_csv_data_into_tables(db_session):
from lib.src.d02_intermediate.clean_data import clean_null_values_in_csv
from lib.src.d03_processing.get_cleaned_data import get_dataframe
# clean the raw 'matches.csv' file
clean_null_values_in_csv()
# get pandas dataframe from the cleaned up csv
df = get_dataframe()
# save cities and venues
city_venue_df = df[['city', 'venue']].drop_duplicates()
zip_city_venue = zip(city_venue_df['city'], city_venue_df['venue'])
for cv in zip_city_venue:
city = City()
city.name = cv[0]
db_session.add(city)
db_session.commit()
venue = Venue()
venue.city_id = city.id
venue.name = cv[1]
db_session.add(venue)
# db_session.commit()
# save teams
teams_df = df['team1'].drop_duplicates()
teams_list = [team for team in teams_df]
for team in teams_list:
team_model = Team()
team_model.name = team
db_session.add(team_model)
# save umpires
umpire_set1 = set(df['umpire1'].drop_duplicates())
umpire_set2 = set(df['umpire2'].drop_duplicates())
all_umpires = umpire_set1.union(umpire_set2)
for umpire in all_umpires:
umpire_model = Official()
umpire_model.name = umpire
db_session.add(umpire_model)
db_session.commit()
# create a dict with key and name mappings for
# cities, venues, teams and officials
cities = db_session.query(City).all()
cities_dict = {city.name: city.id for city in cities}
venues = db_session.query(Venue).all()
venues_dict = {venue.name: venue.id for venue in venues}
teams = db_session.query(Team).all()
teams_dict = {team.name: team.id for team in teams}
officials = db_session.query(Official).all()
officials_dict = {official.name: official.id for official in officials}
# cities = df_matches['city'].drop_duplicates()
# cities_dict = {x: y for y,x in enumerate(cities)}
# db_session.close()
# change city, venue, team1, team2, umpire1, umpire2
# names to their corresponding 'ids' from database
df['city'] = df['city'].apply(lambda x: cities_dict[x])
df['venue'] = df['venue'].apply(lambda x: venues_dict[x])
df['team1'] = df['team1'].apply(lambda x: teams_dict[x])
df['team2'] = df['team2'].apply(lambda x: teams_dict[x])
df['toss_winner'] = df['toss_winner'].apply(lambda x: teams_dict[x])
df['winner'] = df['winner'].apply(lambda x: teams_dict[x])
df['umpire1'] = df['umpire1'].apply(lambda x: officials_dict[x])
df['umpire2'] = df['umpire2'].apply(lambda x: officials_dict[x])
# create sqlalchemy engine
engine = db_session.get_bind()
# Insert dataframe into 'Matches' table
df.to_sql('Matches', con=engine, if_exists='replace', index=False)
def updates(competition):
connection = http.client.HTTPConnection('api.football-data.org')
headers = { 'X-Auth-Token': 'fe4c5aaa344a40a78cef8547f5840478' }
connection.request('GET', '/v2/competitions/' + competition + '/matches', None, headers )
response = json.loads(connection.getresponse().read().decode())
connection.close()
if "matches" not in response:
return "no matches\n"
for match_api in response['matches']:
home_team = Team.query.filter(Team.name == match_api["homeTeam"]["name"]).first()
if home_team is None:
home_team = Team()
home_team.name = match_api["homeTeam"]["name"]
db.session.add(home_team)
db.session.commit()
away_team = Team.query.filter(Team.name == match_api["awayTeam"]["name"]).first()
if away_team is None:
away_team = Team()
away_team.name = match_api["awayTeam"]["name"]
db.session.add(away_team)
db.session.commit()
match = FootballMatch.query.options(joinedload(FootballMatch.home_team), joinedload(FootballMatch.away_team)).filter(FootballMatch.home_team_id == home_team.id, FootballMatch.away_team_id == away_team.id).first()
# The match does not exist. We must add it to the database
if match is None:
match = FootballMatch()
#match.date = match_api["utcDate"]
match_status = MatchStatus()
#match_status.status = match_api["status"]
#match.status = match_status
match.home_team = home_team
match.away_team = away_team
#match.last_updated = match_api["lastUpdated"]
score = MatchScore()
#score.winner = Winner[match_api["score"]["winner"]]
full_time_score = Score()
half_time_score = Score()
extra_score = Score()
penalties_score = Score()
full_time_score.home_goals = match_api["score"]["fullTime"]["homeTeam"]
full_time_score.away_goals = match_api["score"]["fullTime"]["awayTeam"]
half_time_score.home_goals = match_api["score"]["halfTime"]["homeTeam"]
half_time_score.away_goals = match_api["score"]["halfTime"]["awayTeam"]
extra_score.home_goals = match_api["score"]["extraTime"]["homeTeam"]
extra_score.away_goals = match_api["score"]["extraTime"]["awayTeam"]
penalties_score.home_goals = match_api["score"]["penalties"]["homeTeam"]
penalties_score.away_goals = match_api["score"]["penalties"]["awayTeam"]
score.full_time_score = full_time_score
score.half_time_score = half_time_score
score.extra_score = extra_score
score.penalties_score = penalties_score
match.match_score = score
#db.session.add(match_status)
db.session.add(penalties_score)
db.session.add(full_time_score)
db.session.add(half_time_score)
db.session.add(extra_score)
db.session.add(score)
db.session.add(match)
db.session.commit()
#match.set
return "no error ?\n" + str(response)
