def add_curated_picks_ncaab():
total_sheet_data = []
browser.get('https://www.teamrankings.com/ncaa-basketball-betting-picks/')
time.sleep(3)
body = browser.find_element_by_id(
'DataTables_Table_0').find_element_by_tag_name('tbody')
tr_list = body.find_elements_by_tag_name('tr')
first_td_list = tr_list[0].find_elements_by_tag_name('td')
if len(first_td_list) == 1:
print("no TeamRanking-NCAA data")
return
date = Select(browser.find_element_by_class_name(
'redirectOnChange')).first_selected_option.text
date_info = date.split(' ')[1] + "," + date.split(' ')[2] + "," + str(
datetime.datetime.now().year)
date = datetime.datetime.strptime(date_info, '%b,%d,%Y').date()
date = str(date)
for tr in tr_list:
td_list = tr.find_elements_by_tag_name('td')
rot_list = td_list[0].get_attribute('innerHTML').strip().split('<br>')
team_list = td_list[1].get_attribute('innerHTML').strip().split('<br>')
team_list[0] = team_list[0].strip()
team_list[1] = team_list[1].strip()
game_winner = td_list[2].find_element_by_class_name(
'picks-block-in').text.strip()
ATS = td_list[3].find_element_by_class_name(
'picks-block-in').text.strip()
total = td_list[4].find_element_by_class_name(
'picks-block-in').text.strip()
money_line_value = td_list[5].find_element_by_class_name(
'picks-block-in').text.strip()
if action_type <= 1:
team1 = session.query(NCAATeam).filter(
or_(NCAATeam.ShortTeamName == team_list[0],
NCAATeam.FullTeamName == team_list[0])).first()
team2 = session.query(NCAATeam).filter(
or_(NCAATeam.ShortTeamName == team_list[1],
NCAATeam.FullTeamName == team_list[1])).first()
if not team1:
team1 = NCAATeam(team_list[0])
session.add(team1)
session.flush()
if not team2:
team2 = NCAATeam(team_list[1])
session.add(team2)
session.flush()
db_record = session.query(TeamRankingNCAA).filter(
TeamRankingNCAA.Team1ID == team1.ID,
TeamRankingNCAA.Team2ID == team2.ID,
TeamRankingNCAA.Date == date).first()
new_teamranking = TeamRankingNCAA(team1.ID,
int(rot_list[0]), team2.ID,
int(rot_list[1]), game_winner,
ATS, total, money_line_value,
date)
if not db_record:
session.add(new_teamranking)
if action_type == 0:
total_sheet_data.append(
(team1.ShortTeamName, team1.FullTeamName,
team2.ShortTeamName, team2.FullTeamName, rot_list[0],
rot_list[1], game_winner, ATS, total,
money_line_value, date))
else:
new_teamranking.ID = db_record.ID
session.merge(new_teamranking)
if action_type == 0 or action_type == 2:
team1_formula_str = 'OR(SUBSTITUTE({' + ncaa_team_info[0] + '}, "\'", " ")="' + \
team_list[0].replace("'", " ") + '", SUBSTITUTE({' + \
ncaa_team_info[1] + '}, "\'", " ")="' + \
team_list[0].replace("'", " ") + '")'
team2_formula_str = 'OR(SUBSTITUTE({' + ncaa_team_info[0] + '}, "\'", " ")="' + \
team_list[1].replace("'", " ") + '", SUBSTITUTE({' + \
ncaa_team_info[1] + '}, "\'", " ")="' + \
team_list[1].replace("'", " ") + '")'
ncaa_first_team = airtable_ncaa_team.get_all(
formula=team1_formula_str)
ncaa_second_team = airtable_ncaa_team.get_all(
formula=team2_formula_str)
if not ncaa_first_team:
ncaa_first_team = airtable_ncaa_team.insert(
{ncaa_team_info[0]: team_list[0]})
else:
ncaa_first_team = ncaa_first_team[0]
if not ncaa_second_team:
ncaa_second_team = airtable_ncaa_team.insert(
{ncaa_team_info[0]: team_list[1]})
else:
ncaa_second_team = ncaa_second_team[0]
game_date = airtable_game_date.match(game_date_info[0], date)
if not game_date:
game_date = airtable_game_date.insert(
{game_date_info[0]: date})
formula_str = 'AND(SUBSTITUTE({Team1}, "\'", " ")="' + \
ncaa_first_team['fields']['NCAA Team Name'].replace("'", " ") + \
'", SUBSTITUTE({Team2}, "\'", " ")="' + \
ncaa_second_team['fields']['NCAA Team Name'].replace("'", " ") + '", {Date}="' + date + '")'
fields = {
teamranking_ncaa_info[0]: [ncaa_first_team['id']],
teamranking_ncaa_info[1]: [ncaa_second_team['id']],
teamranking_ncaa_info[2]: [game_date['id']],
teamranking_ncaa_info[3]: int(rot_list[0]),
teamranking_ncaa_info[4]: int(rot_list[1]),
teamranking_ncaa_info[5]: game_winner,
teamranking_ncaa_info[6]: ATS,
teamranking_ncaa_info[7]: total,
teamranking_ncaa_info[8]: money_line_value
}
record = airtable_teamranking_ncaa.get_all(formula=formula_str)
if record:
airtable_teamranking_ncaa.replace(record[0]['id'], fields)
else:
airtable_teamranking_ncaa.insert(fields)
if action_type == 0 and len(total_sheet_data) > 0: # store into gsheet
df = pd.DataFrame(total_sheet_data,
columns=TeamRankingNCAA.gsheet_table_columns)
original_df = wks_ncaa.get_as_df()
original_df = original_df.append(df)
wks_ncaa.set_dataframe(original_df, (1, 1))
