def upsert_competition(competition_extID, competition_desc, competition_abrv):
conn = dbconnect.connect()
cur = conn.cursor()
if int(competition_extID) == 1097:
competition_desc = "MLS is Back Tournament Final Pres. by Wells Fargo"
competition_abrv = "MLS is Back"
if str(competition_desc) == "MLS Cup":
competition_extID = 99999
competition_desc = "MLS Playoffs"
competition_abrv = "MLS Playoffs"
query = """
INSERT INTO `mls`.`competitions` (
competition_extID,
competition_desc,
competition_abrv
) VALUES (
%(competition_extID)s,
%(competition_desc)s,
%(competition_abrv)s
) ON DUPLICATE KEY UPDATE
competition_extID = %(competition_extID)s,
competition_desc = %(competition_desc)s,
competition_abrv = %(competition_abrv)s;
"""
cur.execute(query, {
"competition_extID": competition_extID,
"competition_desc": competition_desc,
"competition_abrv": competition_abrv
})
conn.commit()
conn.close()
def get_match(match_extID):
conn = connect()
cur = conn.cursor()
query = """
SELECT `id`
FROM `mls`.`matches`
WHERE `match_extID` = %(match_extID)s
"""
cur.execute(query, {"match_extID": match_extID})
match_id = cur.fetchone()
conn.close()
if match_id is None:
return match_id
else:
return match_id[0]
def lookup_season(season_extID):
conn = dbconnect.connect()
cur = conn.cursor()
query = """
SELECT `id`
FROM `mls`.`seasons`
WHERE `season_extID` = %(season_extID)s
"""
cur.execute(query, {"season_extID": season_extID})
season_id = cur.fetchone()
conn.close()
if season_id is None:
return season_id
else:
return season_id[0]
def lookup_club(club_extID):
conn = dbconnect.connect()
cur = conn.cursor()
query = """
SELECT `id`
FROM `mls`.`clubs`
WHERE `club_extID` = %(club_extID)s
"""
cur.execute(query, {"club_extID": club_extID})
club_id = cur.fetchone()
conn.close()
if club_id is None:
return club_id
else:
return club_id[0]
def lookup_competition(competition_extID):
conn = dbconnect.connect()
cur = conn.cursor()
query = """
SELECT `id`
FROM `mls`.`competitions`
WHERE `competition_extID` = %(competition_extID)s;
"""
cur.execute(query, {"competition_extID": competition_extID})
competition_id = cur.fetchone()
conn.close()
if competition_id is None:
return competition_id
else:
return competition_id[0]
def lookup_player_extID(player_extID):
conn = dbconnect.connect()
cur = conn.cursor()
query = """
SELECT `id`
FROM `mls`.`players`
WHERE `player_extID` = %(player_extID)s;
"""
cur.execute(query, {"player_extID": player_extID})
player_id = cur.fetchone()
conn.close
if player_id is None:
return player_id
else:
return player_id[0]
def upsert_club(club_name, club_abrv, club_extID, bg_color, accent_color_1,
accent_color_2, txt_color, logo_url):
conn = dbconnect.connect()
cur = conn.cursor()
query = """
INSERT INTO `mls`.`clubs` (
`club_name`,
`club_abrv`,
`club_extID`,
`bg_color`,
`accent_color_1`,
`accent_color_2`,
`txt_color`,
`logo_url`
) VALUES (
%(club_name)s,
%(club_abrv)s,
%(club_extID)s,
%(bg_color)s,
%(accent_color_1)s,
%(accent_color_2)s,
%(txt_color)s,
%(logo_url)s,
) ON DUPLICATE KEY UPDATE
`club_name` = %(club_name)s,
`club_abrv` = %(club_abrv)s,
`club_extID` = %(club_extID)s,
`bg_color` = %(bg_color)s,
`accent_color_1` = %(accent_color_1)s,
`accent_color_2` = %(accent_color_2)s,
`txt_color` = %(txt_color)s,
`logo_url` = %(logo_url)s;
"""
cur.execute(
query, {
"club_name": club_name,
"club_abrv": club_abrv,
"club_extID": club_extID,
"bg_color": bg_color,
"accent_color_1": accent_color_1,
"accent_color_2": accent_color_2,
"txt_color": txt_color,
"logo_url": logo_url
})
conn.commit()
conn.close()
def upsert_season(season_extID, season_desc):
conn = dbconnect.connect()
cur = conn.cursor()
query = """
INSERT INTO `mls`.`seasons` (
season_extID,
season_desc
) VALUES (
%(season_extID)s,
%(season_desc)s
) ON DUPLICATE KEY UPDATE
season_desc = %(season_desc)s;
"""
cur.execute(query, {
"season_extID": season_extID,
"season_desc": season_desc
})
conn.commit()
conn.close()
def upsert_match(match_extID, away_club_id, home_club_id, match_datetime,
competition_id, season_id, mlssoccer_url):
conn = connect()
cur = conn.cursor()
query = """
INSERT INTO `mls`.`matches` (
`match_extID`,
`away_club_id`,
`home_club_id`,
`match_datetime`,
`competition_id`,
`season_id`,
`mlssoccer_url`
) VALUES (
%(match_extID)s,
%(away_club_id)s,
%(home_club_id)s,
%(match_datetime)s,
%(competition_id)s,
%(season_id)s,
%(mlssoccer_url)s
) ON DUPLICATE KEY UPDATE
away_club_id = %(away_club_id)s,
home_club_id = %(home_club_id)s,
match_datetime = %(match_datetime)s,
competition_id = %(competition_id)s,
season_id = %(season_id)s,
mlssoccer_url = %(mlssoccer_url)s
"""
cur.execute(
query, {
"match_extID": match_extID,
"away_club_id": away_club_id,
"home_club_id": home_club_id,
"match_datetime": match_datetime,
"competition_id": competition_id,
"season_id": season_id,
"mlssoccer_url": mlssoccer_url
})
conn.commit()
conn.close()
def upsert_match_player(player_extID, player_first_name, player_last_name,
player_jersey_num, player_weight_lbs, player_birthdate,
player_home_city, player_home_state,
player_home_country, player_headshot_url, player_url,
player_club_id, player_display_name, player_position):
if math.isnan(player_jersey_num):
player_jersey_num = None
if math.isnan(player_weight_lbs):
player_weight_lbs = None
conn = dbconnect.connect()
cur = conn.cursor()
query = """
INSERT INTO `mls`.`players` (
`display_name`,
`real_name`,
`first_name`,
`last_name`,
`player_url`,
`jersey_num`,
`weight_lbs`,
`birthdate`,
`player_extID`,
`home_city`,
`home_state`,
`home_country`,
`position`,
`headshot_url`,
`club_id`
) VALUES (
%(player_display_name)s,
CONCAT_WS(' ',%(player_first_name)s, %(player_last_name)s),
%(player_first_name)s,
%(player_last_name)s,
%(player_url)s,
%(player_jersey_num)s,
%(player_weight_lbs)s,
%(player_birthdate)s,
%(player_extID)s,
%(player_home_city)s,
%(player_home_state)s,
%(player_home_country)s,
%(player_position)s,
%(player_headshot_url)s,
%(player_club_id)s
) ON DUPLICATE KEY UPDATE
`display_name` = %(player_display_name)s,
`first_name` = %(player_first_name)s,
`last_name` = %(player_last_name)s,
`jersey_num` = %(player_jersey_num)s,
`weight_lbs` = %(player_weight_lbs)s,
`birthdate` = %(player_birthdate)s,
`player_extID` = %(player_extID)s,
`player_url` = %(player_url)s,
`headshot_url` = %(player_headshot_url)s,
`home_city` = %(player_home_city)s,
`home_state` = %(player_home_state)s,
`home_country` = %(player_home_country)s,
`club_id` = %(player_club_id)s,
`position` = %(player_position)s
"""
cur.execute(
query, {
"player_display_name": player_display_name,
"player_first_name": player_first_name,
"player_last_name": player_last_name,
"player_url": player_url,
"player_jersey_num": player_jersey_num,
"player_weight_lbs": player_weight_lbs,
"player_birthdate": player_birthdate,
"player_extID": player_extID,
"player_home_city": player_home_city,
"player_home_state": player_home_state,
"player_home_country": player_home_country,
"player_position": player_position,
"player_headshot_url": player_headshot_url,
"player_club_id": player_club_id
})
conn.commit()
conn.close()