def add_oddshark_ncaa(): browser.get('https://www.oddsshark.com/ncaab/computer-picks') block_system_main = browser.find_element_by_id("block-system-main") computer_tables = block_system_main.find_elements_by_css_selector( '.table.table--striped.table--fixed-column') total_sheet_data = [] for computer_table in computer_tables: if computer_table.get_attribute( 'class') == 'table table--striped table--fixed-column': name_wraps = computer_table.find_elements_by_class_name( 'name-wrap') first_team_name_short = name_wraps[0].find_element_by_class_name( 'table__name-short').get_attribute('innerHTML') first_team_name_long = name_wraps[0].find_element_by_class_name( 'table__name-long').text second_team_name_short = name_wraps[1].find_element_by_class_name( 'table__name-short').get_attribute('innerHTML') second_team_name_long = name_wraps[1].find_element_by_class_name( 'table__name-long').text thead = computer_table.find_element_by_tag_name('thead') date_time_ary = thead.find_element_by_tag_name('th').text.split( " @ ") date_time = date_time_ary[0] + " " + date_time_ary[1] insert_game_date = date_time_ary[0] + ' ' + str( datetime.datetime.now().year) insert_game_date = datetime.datetime.strptime( insert_game_date, '%b %d %Y').date() insert_game_date = str(insert_game_date) tbody = computer_table.find_element_by_tag_name('tbody') attr_tr_list = tbody.find_elements_by_tag_name('tr') predicted_score_tds = attr_tr_list[0].find_elements_by_tag_name( 'td') computer_pick_tds = attr_tr_list[1].find_elements_by_tag_name('td') public_consensus_tds = attr_tr_list[2].find_elements_by_tag_name( 'td') consensus_bet_tds = attr_tr_list[3].find_elements_by_tag_name('td') if action_type <= 1: # store into db team1 = session.query(NCAATeam).filter( or_(NCAATeam.ShortTeamName == first_team_name_short, NCAATeam.FullTeamName == first_team_name_long)).first() team2 = session.query(NCAATeam).filter( or_(NCAATeam.ShortTeamName == second_team_name_short, NCAATeam.FullTeamName == second_team_name_long)).first() if not team1: team1 = NCAATeam(first_team_name_short, first_team_name_long) session.add(team1) session.flush() if not team2: team2 = NCAATeam(second_team_name_short, second_team_name_long) session.add(team2) session.flush() db_record = session.query(OddSharkNCAA).filter( OddSharkNCAA.Team1ID == team1.ID, OddSharkNCAA.Team2ID == team2.ID, OddSharkNCAA.Date == insert_game_date, OddSharkNCAA.Time == date_time_ary[1]).first() new_oddshark = OddSharkNCAA( team1.ID, team2.ID, insert_game_date, date_time_ary[1], predicted_score_tds[1].text, predicted_score_tds[2].text, computer_pick_tds[1].text, computer_pick_tds[2].text, public_consensus_tds[1].text, public_consensus_tds[2].text, consensus_bet_tds[1].text, consensus_bet_tds[2].text) if not db_record: session.add(new_oddshark) if action_type == 0: total_sheet_data.append( (team1.ShortTeamName, team1.FullTeamName, team2.ShortTeamName, team2.FullTeamName, insert_game_date, date_time_ary[1], predicted_score_tds[1].text, predicted_score_tds[2].text, computer_pick_tds[1].text, computer_pick_tds[2].text, public_consensus_tds[1].text, public_consensus_tds[2].text, consensus_bet_tds[1].text, consensus_bet_tds[2].text)) else: new_oddshark.ID = db_record.ID session.merge(new_oddshark) if action_type == 0 or action_type == 3: # store into airtable ncaa_first_team = airtable_ncaa_team.match( ncaa_team_info[0], first_team_name_short) ncaa_second_team = airtable_ncaa_team.match( ncaa_team_info[0], second_team_name_short) if not ncaa_first_team: first_team = airtable_ncaa_team.insert({ ncaa_team_info[0]: first_team_name_short, ncaa_team_info[1]: first_team_name_long }) else: first_team = airtable_ncaa_team.update( ncaa_first_team['id'], { ncaa_team_info[0]: first_team_name_short, ncaa_team_info[1]: first_team_name_long }) if not ncaa_second_team: second_team = airtable_ncaa_team.insert({ ncaa_team_info[0]: second_team_name_short, ncaa_team_info[1]: second_team_name_long }) else: second_team = airtable_ncaa_team.update( ncaa_second_team['id'], { ncaa_team_info[0]: second_team_name_short, ncaa_team_info[1]: second_team_name_long }) game_date = airtable_game_date.match(game_date_info[0], insert_game_date) if not game_date: game_date = airtable_game_date.insert( {game_date_info[0]: insert_game_date}) game_time = airtable_game_time.match(game_time_info[0], date_time_ary[1]) if not game_time: game_time = airtable_game_time.insert( {game_time_info[0]: date_time_ary[1]}) formula_str = "AND(Team1='" + first_team_name_short + "', Team2='" + second_team_name_short + \ "', Date='" + insert_game_date + "', Time='" + date_time_ary[1] + "')" fields = { oddshark_ncaa_info[0]: [first_team['id']], oddshark_ncaa_info[1]: [second_team['id']], oddshark_ncaa_info[2]: [game_date['id']], oddshark_ncaa_info[3]: [game_time['id']], oddshark_ncaa_info[4]: predicted_score_tds[1].text, oddshark_ncaa_info[5]: predicted_score_tds[2].text, oddshark_ncaa_info[6]: computer_pick_tds[1].text, oddshark_ncaa_info[7]: computer_pick_tds[2].text, oddshark_ncaa_info[8]: public_consensus_tds[1].text, oddshark_ncaa_info[9]: public_consensus_tds[2].text, oddshark_ncaa_info[10]: consensus_bet_tds[1].text, oddshark_ncaa_info[11]: consensus_bet_tds[2].text } record = airtable_ncaab.get_all(formula=formula_str) if record: airtable_ncaab.replace(record[0]['id'], fields) else: airtable_ncaab.insert(fields) if action_type == 0 and len(total_sheet_data) > 0: # store into gsheet df = pd.DataFrame(total_sheet_data, columns=OddSharkNCAA.table_columns) original_df = wks_ncaab.get_as_df() original_df = original_df.append(df) wks_ncaab.set_dataframe(original_df, (1, 1))
def add_curated_picks_wise_ncaab(): total_sheet_data = [] browser.get('https://www.pickswise.com/sports/college-basketball/') div_list = browser.find_elements_by_css_selector( '.ContentPanel.ContentPanel--block.ContentPanel--no-padding') for div in div_list: datetime_info = div.find_element_by_tag_name('h2').get_attribute( 'innerHTML').strip() if '<br>' not in datetime_info: continue datetime_info = datetime_info.split('<br>')[0] datetime_info = datetime_info.strip() datetime_info = datetime_info.split(', ')[1] date = datetime_info.split(' - ')[0][:-2] time = datetime_info.split(' - ')[1] time = time.split(' ')[0] date = date + "," + str(datetime.datetime.now().year) date = datetime.datetime.strptime(date, '%b %d,%Y').date() date = str(date) try: team_list = div.find_element_by_class_name( 'PreviewCard__teams-container').find_elements_by_class_name( 'Pick') first_team_name = team_list[0].find_element_by_class_name( 'Pick__team-name').text second_team_name = team_list[1].find_element_by_class_name( 'Pick__team-name').text first_team_name = first_team_name.strip() second_team_name = second_team_name.strip() pick_list = div.find_element_by_css_selector('.TabbedContent__tab.TabbedContent__tab--active') \ .find_elements_by_class_name('Pick') first_team_pick_outcome = pick_list[0].find_element_by_class_name( 'Pick__outcome').get_attribute('innerHTML') first_team_pick_market = pick_list[0].find_element_by_class_name( 'Pick__market').get_attribute('innerHTML') first_team_prediction = pick_list[0].find_element_by_css_selector( '.Button.Button--border-red').get_attribute('innerHTML') second_team_pick_outcome = pick_list[1].find_element_by_class_name( 'Pick__outcome').get_attribute('innerHTML') second_team_pick_market = pick_list[1].find_element_by_class_name( 'Pick__market').get_attribute('innerHTML') second_team_prediction = pick_list[1].find_element_by_css_selector( '.Button.Button--border-red').get_attribute('innerHTML') if action_type <= 1: team1 = session.query(NCAATeam).filter( or_(NCAATeam.ShortTeamName == first_team_name, NCAATeam.FullTeamName == first_team_name)).first() team2 = session.query(NCAATeam).filter( or_(NCAATeam.ShortTeamName == second_team_name, NCAATeam.FullTeamName == second_team_name)).first() if not team1: team1 = NCAATeam('', first_team_name) session.add(team1) session.flush() if not team2: team2 = NCAATeam('', second_team_name) session.add(team2) session.flush() db_record = session.query(PicksWiseNCAA).filter( PicksWiseNCAA.Team1ID == team1.ID, PicksWiseNCAA.Team2ID == team2.ID, PicksWiseNCAA.Date == date, PicksWiseNCAA.Time == time).first() new_pickwise = PicksWiseNCAA( team1.ID, team2.ID, first_team_prediction, first_team_pick_outcome, first_team_pick_market, second_team_prediction, second_team_pick_outcome, second_team_pick_market, date, time) if not db_record: session.add(new_pickwise) if action_type == 0: total_sheet_data.append( (team1.ShortTeamName, team1.FullTeamName, team2.ShortTeamName, team2.FullTeamName, date, time, first_team_prediction, first_team_pick_outcome, first_team_pick_market, second_team_prediction, second_team_pick_outcome, second_team_pick_market)) else: new_pickwise.ID = db_record.ID session.merge(new_pickwise) if action_type == 2: team1_formula_str = 'OR(SUBSTITUTE({' + ncaa_team_info[0] + '}, "\'", " ")="' + \ first_team_name.replace("'", " ") + '", SUBSTITUTE({' + \ ncaa_team_info[1] + '}, "\'", " ")="' + \ first_team_name.replace("'", " ") + '")' team2_formula_str = 'OR(SUBSTITUTE({' + ncaa_team_info[0] + '}, "\'", " ")="' + \ second_team_name.replace("'", " ") + '", SUBSTITUTE({' + \ ncaa_team_info[1] + '}, "\'", " ")="' + \ second_team_name.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]: first_team_name}) else: ncaa_first_team = ncaa_first_team[0] if not ncaa_second_team: ncaa_second_team = airtable_ncaa_team.insert( {ncaa_team_info[0]: second_team_name}) 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}) game_time = airtable_game_time.match(game_time_info[0], time) if not game_time: game_time = airtable_game_time.insert( {game_time_info[0]: time}) 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 = { pickwise_ncaa_info[0]: [ncaa_first_team['id']], pickwise_ncaa_info[1]: [ncaa_second_team['id']], pickwise_ncaa_info[2]: [game_date['id']], pickwise_ncaa_info[3]: [game_time['id']], pickwise_ncaa_info[4]: first_team_prediction, pickwise_ncaa_info[5]: second_team_prediction, pickwise_ncaa_info[6]: first_team_pick_outcome, pickwise_ncaa_info[7]: second_team_pick_outcome, pickwise_ncaa_info[8]: first_team_pick_market, pickwise_ncaa_info[9]: second_team_pick_market } record = airtable_pickwise_ncaa.get_all(formula=formula_str) if record: airtable_pickwise_ncaa.replace(record[0]['id'], fields) else: airtable_pickwise_ncaa.insert(fields) except NoSuchElementException: print("No Pickwise NCAAB data") continue if action_type == 0 and len(total_sheet_data) > 0: # store into gsheet df = pd.DataFrame(total_sheet_data, columns=PicksWiseNCAA.gsheet_table_columns) original_df = wks_pickwise_ncaa.get_as_df() original_df = original_df.append(df) wks_pickwise_ncaa.set_dataframe(original_df, (1, 1))
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))
def add_team_score(): time.sleep(5) total_sheet_data = [] date_selections = browser.find_element_by_id('cboUpcomingDates') date_info = date_selections.find_elements_by_tag_name( 'option')[0].get_attribute('innerHTML').split(', ') date = date_info[1] + ", " + date_info[2] date = datetime.datetime.strptime(date, '%B %d, %Y').date() date = str(date) table = browser.find_element_by_id('myTable4') td_list = table.find_elements_by_xpath( '//*[starts-with(@id, "tdUpcoming_") and not(contains(@class, "filler"))]' ) new_team_scores = [ HaslaMetrics(), HaslaMetrics(), HaslaMetrics(), HaslaMetrics() ] new_team_scores[0].Date = date new_team_scores[1].Date = date new_team_scores[2].Date = date new_team_scores[3].Date = date add_index = 0 for td in td_list: td_text = td.get_attribute('innerHTML') td_id = td.get_attribute('id') # if len(td_text) == 0 and '_sc' not in td_id: if len(td_text) == 0: continue td_infos = td_id.split('_') td_infos[1] = int(td_infos[1]) td_infos[1] -= 1 td_infos[1] %= 4 if td_infos[2] == '1': if '_sc' not in td_id: first_team_name = td.find_element_by_tag_name( 'a').get_attribute('innerHTML') new_team_scores[td_infos[1]].Team1Name = first_team_name team1 = session.query(NCAATeam).filter( or_(NCAATeam.ShortTeamName == first_team_name, NCAATeam.FullTeamName == first_team_name)).first() if action_type <= 1: if not team1: team1 = NCAATeam(first_team_name) session.add(team1) session.flush() new_team_scores[td_infos[1]].Team1ID = team1.ID new_team_scores[td_infos[1]].Team1Rank = int( td.find_element_by_tag_name('sub').get_attribute( 'innerHTML')) else: new_team_scores[td_infos[1]].Team1Score = float( td.get_attribute('innerHTML')) else: if '_sc' not in td_id: second_team_name = td.find_element_by_tag_name( 'a').get_attribute('innerHTML') new_team_scores[td_infos[1]].Team2Name = second_team_name team2 = session.query(NCAATeam).filter( or_(NCAATeam.ShortTeamName == second_team_name, NCAATeam.FullTeamName == second_team_name)).first() if action_type <= 1: if not team2: team2 = NCAATeam(second_team_name) session.add(team2) session.flush() new_team_scores[td_infos[1]].Team2ID = team2.ID new_team_scores[td_infos[1]].Team2Rank = int( td.find_element_by_tag_name('sub').get_attribute( 'innerHTML')) else: new_team_scores[td_infos[1]].Team2Score = float( td.get_attribute('innerHTML')) add_index += 1 if add_index % 16 == 0: for idx in range(4): if action_type <= 1: db_record = session.query(HaslaMetrics).filter( HaslaMetrics.Team1ID == new_team_scores[idx].Team1ID, HaslaMetrics.Team2ID == new_team_scores[idx].Team2ID, HaslaMetrics.Date == date).first() if not db_record: session.add(new_team_scores[idx]) if action_type == 0: first_team = session.query(NCAATeam).filter( NCAATeam.ID == new_team_scores[idx].Team1ID).first() second_team = session.query(NCAATeam).filter( NCAATeam.ID == new_team_scores[idx].Team2ID).first() total_sheet_data.append( (first_team.ShortTeamName, first_team.FullTeamName, second_team.ShortTeamName, second_team.FullTeamName, new_team_scores[idx].Date, new_team_scores[idx].Team1Rank, new_team_scores[idx].Team2Rank, new_team_scores[idx].Team1Score, new_team_scores[idx].Team2Score)) else: new_team_scores[idx].ID = db_record.ID session.merge(new_team_scores[idx]) elif action_type == 0 or action_type == 2: team1_formula_str = 'OR(SUBSTITUTE({' + ncaa_team_info[0] + '}, "\'", " ")="' + \ new_team_scores[idx].Team1Name.replace("'", " ") + '", SUBSTITUTE({' + \ ncaa_team_info[1] + '}, "\'", " ")="' + \ new_team_scores[idx].Team1Name.replace("'", " ") + '")' team2_formula_str = 'OR(SUBSTITUTE({' + ncaa_team_info[0] + '}, "\'", " ")="' + \ new_team_scores[idx].Team2Name.replace("'", " ") + '", SUBSTITUTE({' + \ ncaa_team_info[1] + '}, "\'", " ")="' + \ new_team_scores[idx].Team2Name.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]: new_team_scores[idx].Team1Name }) else: ncaa_first_team = ncaa_first_team[0] if not ncaa_second_team: ncaa_second_team = airtable_ncaa_team.insert({ ncaa_team_info[0]: new_team_scores[idx].Team2Name }) 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 = { hasla_metrics_info[0]: [ncaa_first_team['id']], hasla_metrics_info[1]: [ncaa_second_team['id']], hasla_metrics_info[2]: [game_date['id']], hasla_metrics_info[3]: new_team_scores[idx].Team1Rank, hasla_metrics_info[4]: new_team_scores[idx].Team2Rank, hasla_metrics_info[5]: float(new_team_scores[idx].Team1Score), hasla_metrics_info[6]: float(new_team_scores[idx].Team2Score), } record = airtable_haslametrics.get_all(formula=formula_str) if record: airtable_haslametrics.replace(record[0]['id'], fields) else: airtable_haslametrics.insert(fields) new_team_scores = [ HaslaMetrics(), HaslaMetrics(), HaslaMetrics(), HaslaMetrics() ] new_team_scores[0].Date = date new_team_scores[1].Date = date new_team_scores[2].Date = date new_team_scores[3].Date = date if action_type == 0 and len(total_sheet_data) > 0: # store into gsheet df = pd.DataFrame(total_sheet_data, columns=HaslaMetrics.gsheet_table_columns) original_df = wks.get_as_df() original_df = original_df.append(df) wks.set_dataframe(original_df, (1, 1))