def add_city():
"""Добавить области в БД. Запустить только один раз!"""
raions = ('Алексинский район', 'Арсеньевский район', 'Белёвский район',
'Богородицкий район', 'Венёвский район', 'Воловский район',
'Дубенский район', 'Ефремовский район', 'Заокский район',
'Каменский район', 'Кимовский район', 'Киреевский район',
'Куркинский район', 'Ленинский район', 'Новомосковский район',
'Одоевский район', 'Плавский район', 'Суворовский район',
'Тёпло-Огарёвский район', 'Узловский район', 'Чернский район',
'Щёкинский район', 'Ясногорский район')
region = Region()
region.name = 'Тульская область'
region.save()
for raion in raions:
city = City()
city.region = region
city.name = raion
city.save()
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)