def main(years: Optional[int] = None):
keys = ["id", "name", "cleanName", "country", "competition_id"]
with open("credentials/footystats.json") as f:
key = json.load(f)["key"]
fs = FootyStats(key)
season_ids = fs.chosen_season_id(years)
sql_create = open("sql/footystats/tables/teams/create.sql").read()
sql_insert = open("sql/footystats/tables/teams/insert.sql").read()
conn = mysql.connector.connect(user="******",
password="******",
host="127.0.0.1",
database="footystats")
conn.cursor().execute(sql_create)
pbar = tqdm(season_ids)
pbar.set_description("Ingesting team data")
for season_id in pbar:
data = pipeline.extract(fs.teams, season_id)
data = pipeline.transform(data, transform, keys)
pipeline.load(data, sql_insert, conn)
conn.commit()
conn.close()
def main(years: Optional[int] = None):
keys = [
"id",
"homeID",
"awayID",
"status",
"homeGoals",
"awayGoals",
"homeGoalCount",
"awayGoalCount",
"date_unix",
"team_a_xg",
"team_b_xg",
"goal_timings_recorded",
"competition_id",
"is_home_away",
"is_xg",
"home_adj",
"away_adj",
"home_avg",
"away_avg",
]
with open("credentials/footystats.json") as f:
key = json.load(f)["key"]
fs = FootyStats(key)
season_ids = fs.chosen_season_id(years)
sql_create = open("sql/footystats/tables/matches/create.sql").read()
sql_insert = open("sql/footystats/tables/matches/insert.sql").read()
conn = mysql.connector.connect(
user="******", password="******", host="127.0.0.1", database="footystats"
)
conn.cursor().execute(sql_create)
pbar = tqdm(season_ids)
pbar.set_description("Ingesting match data")
for season_id in pbar:
data = pipeline.extract(fs.matches, season_id)
data = pipeline.transform(data, transform, keys)
pipeline.load(data, sql_insert, conn)
conn.commit()
conn.close()
def main(years: Optional[int] = None):
keys = [
"id",
"name",
"country",
"status",
"format",
"division",
"starting_year",
"ending_year",
"clubNum",
"season",
"totalMatches",
"matchesCompleted",
"canceledMatchesNum",
"game_week",
"total_game_week",
"league_name",
]
with open("credentials/footystats.json") as f:
key = json.load(f)["key"]
fs = FootyStats(key)
season_ids = fs.chosen_season_id(years)
sql_create = open("sql/footystats/tables/season/create.sql").read()
sql_insert = open("sql/footystats/tables/season/insert.sql").read()
conn = mysql.connector.connect(user="******",
password="******",
host="127.0.0.1",
database="footystats")
conn.cursor().execute(sql_create)
pbar = tqdm(season_ids)
pbar.set_description("Ingesting season data")
for season_id in pbar:
data = pipeline.extract(fs.season, season_id)
data = pipeline.transform(data, transform, keys)
pipeline.load(data, sql_insert, conn)
conn.commit()
conn.close()
def test_etl_list_of_dicts():
data = pipeline.extract(MockAPI().list_of_dicts)
assert data == [
{
"index": 2,
"value": b,
"transformed": False
},
{
"index": 3,
"value": c,
"transformed": False
},
]
data = pipeline.transform(data, transform_mock, keys)
assert data == [
{
"index": 2,
"value": b,
"transformed": True
},
{
"index": 3,
"value": c,
"transformed": True
},
]
con = mysql.connector.connect(user="******",
password="******",
host="127.0.0.1",
database="test")
cursor = con.cursor()
sql_create = open("tests/sql/create.sql").read()
sql_insert = open("tests/sql/insert.sql").read()
cursor.execute(sql_create)
pipeline.load(data, sql_insert, con)
con.commit()
cursor.execute("SELECT num FROM test WHERE idx IN (2, 3)")
assert cursor.fetchall() in [[(b, ), (c, )], [(c, ), (b, )]]
con.close()
def test_etl_list():
data = pipeline.extract(MockAPI().dict)
assert data == {"index": 1, "value": a, "transformed": False}
data = pipeline.transform(data, transform_mock, keys)
assert data == {"index": 1, "value": a, "transformed": True}
con = mysql.connector.connect(user="******",
password="******",
host="127.0.0.1",
database="test")
cursor = con.cursor()
sql_create = open("tests/sql/create.sql").read()
sql_insert = open("tests/sql/insert.sql").read()
cursor.execute(sql_create)
pipeline.load(data, sql_insert, con)
con.commit()
cursor.execute("SELECT num FROM test WHERE idx = 1")
assert cursor.fetchone() == (a, )
con.close()