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)