async def handle_subscribe(bot, ctx, sub_arg):
session.flush()
if len(sub_arg) == 0:
return await bot.send(ctx, '缺少参数')
if is_number(sub_arg[0]) == False and sub_arg[0] != 'all':
return await bot.send(ctx, message='参数只能是编号或者all', at_sender=True)
vid_list = list(map(lambda x: x[0], session.query(Vtb.vid).all()))
user_id = ctx['user_id']
try:
if sub_arg[0] == 'all':
session.bulk_save_objects(
[UserSubscribe(user_id=user_id, vid=vid) for vid in vid_list])
else:
if int(sub_arg[0]) not in vid_list:
return await bot.send(ctx,
message='你订阅了不存在的vtb',
at_sender=True)
model = UserSubscribe(user_id=user_id, vid=sub_arg[0])
session.merge(model)
except Exception:
session.rollback()
return await bot.send(ctx, message='成功订阅（如已订阅请忽略）', at_sender=True)
def edit_post_post(id):
post = Post(
id=id,
title=request.form["title"],
content=mistune.markdown(request.form["content"])
)
session.merge(post)
session.commit()
return redirect(url_for("posts"))
def saveChatSession(self, number, chatsession):
# sessionId = self.getSessionId(number)
# if sessionId:
# session.query(ChatSession).filter(ChatSession.id==sessionId).delete()
session.merge(ChatSession(phoneNumber=number, session=chatsession))
session.commit()
def saveChatSession(self, number, chatsession):
#sessionId = self.getSessionId(number)
#if sessionId:
# session.query(ChatSession).filter(ChatSession.id==sessionId).delete()
session.merge(ChatSession(
phoneNumber=number,
session=chatsession)
)
session.commit()
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_bet_signals():
total_sheet_data = []
browser.get('https://sportsinsights.actionnetwork.com/login/')
browser.find_element_by_tag_name('form').find_element_by_name('email').send_keys(email)
browser.find_element_by_tag_name('form').find_element_by_name('password').send_keys(password)
browser.find_element_by_tag_name('form').submit()
try:
WebDriverWait(browser, 20).until(EC.title_contains('Dashboard'))
print("Sportsinsights page is ready!")
except TimeoutException:
print("Sportsinsights page Loading took too much time!")
# cookie_list = browser.get_cookies()
# for cook in cookie_list:
# print(cook['name']+" = ", cook['value'])
# return
browser.get('https://sportsinsights.actionnetwork.com/bet-signals/')
time.sleep(8)
main_tab = browser.find_element_by_id('s1').find_element_by_id(
'borderLayout_eRootPanel').find_element_by_class_name('ag-body-container')
div_list = main_tab.find_elements_by_css_selector('.ag-row.ag-row-no-focus')
for div in div_list:
cell_list = div.find_elements_by_tag_name('div')
game_time = cell_list[1].get_attribute('innerHTML')
play_on = cell_list[3].get_attribute('innerHTML')
if action_type <= 1:
db_record = session.query(SportsInsightsBETSIGNALS).filter(
SportsInsightsBETSIGNALS.GameTime == cell_list[1].get_attribute('innerHTML'),
SportsInsightsBETSIGNALS.PlayOn == cell_list[3].get_attribute('innerHTML')).first()
new_bet_signals = SportsInsightsBETSIGNALS(cell_list[0].get_attribute('innerHTML'),
cell_list[1].get_attribute('innerHTML'),
cell_list[2].get_attribute('innerHTML'),
cell_list[3].get_attribute('innerHTML'),
cell_list[4].get_attribute('innerHTML'),
cell_list[5].get_attribute('innerHTML'),
cell_list[6].get_attribute('innerHTML'),
cell_list[7].get_attribute('innerHTML').replace(
'<b>', '').replace('</b>', ''),
cell_list[8].get_attribute('innerHTML'))
if not db_record:
session.add(new_bet_signals)
if action_type == 0:
total_sheet_data.append((cell_list[0].get_attribute('innerHTML'),
cell_list[1].get_attribute('innerHTML'),
cell_list[2].get_attribute('innerHTML'),
cell_list[3].get_attribute('innerHTML'),
cell_list[4].get_attribute('innerHTML'),
cell_list[5].get_attribute('innerHTML'),
cell_list[6].get_attribute('innerHTML'),
cell_list[7].get_attribute('innerHTML').replace(
'<b>', '').replace('</b>', ''),
cell_list[8].get_attribute('innerHTML')))
else:
new_bet_signals.ID = db_record.ID
session.merge(new_bet_signals)
print(new_bet_signals)
if action_type == 0 or action_type == 2:
formula_str = 'AND(SUBSTITUTE({PlayOn}, "\'", " ")="' + \
play_on.replace("'", " ") + \
'", {GameTime}="' + game_time + '")'
fields = {
"TriggerTime": cell_list[0].get_attribute('innerHTML'),
"GameTime": cell_list[1].get_attribute('innerHTML'),
"Signal": cell_list[2].get_attribute('innerHTML'),
"PlayOn": cell_list[3].get_attribute('innerHTML'),
"BetType": cell_list[4].get_attribute('innerHTML'),
"TriggerBook": cell_list[5].get_attribute('innerHTML'),
"TriggerUnits": cell_list[6].get_attribute('innerHTML'),
"Score": cell_list[7].get_attribute('innerHTML').replace(
'<b>', '').replace('</b>', ''),
"Result": cell_list[8].get_attribute('innerHTML'),
}
record = airtable_sportsinsights_betsignals.get_all(formula=formula_str)
if record:
airtable_sportsinsights_betsignals.replace(record[0]['id'], fields)
else:
airtable_sportsinsights_betsignals.insert(fields)
if action_type == 0 and len(total_sheet_data) > 0: # store into gsheet
df = pd.DataFrame(total_sheet_data, columns=SportsInsightsBETSIGNALS.gsheet_table_columns)
original_df = wks_bet_signals.get_as_df()
original_df = original_df.append(df)
wks_bet_signals.set_dataframe(original_df, (1, 1))
def add_vegas_insider():
total_sheet_data = []
browser.get('https://www.vegasinsider.com/college-basketball/stats/iskoe-spreadsheet/')
season_name = browser.find_element_by_class_name('viHeaderNorm').text
season_name = season_name.split(' ')[0]
tr_list = browser.find_element_by_class_name('viBodyBorderNorm').find_elements_by_tag_name('tr')
for index, tr in enumerate(tr_list):
if index <= 3:
continue
td_list = tr.find_elements_by_tag_name('td')
try:
team_name = td_list[1].find_element_by_class_name('tableText').get_attribute('innerHTML').strip()
except NoSuchElementException:
team_name = td_list[1].get_attribute('innerHTML').strip()
# print(team_name)
if action_type <= 1:
team1 = session.query(NCAATeam).filter(or_(NCAATeam.ShortTeamName == team_name,
NCAATeam.FullTeamName == team_name)).first()
if not team1:
team1 = NCAATeam(team_name)
session.add(team1)
session.flush()
db_record = session.query(VegasInsider).filter(VegasInsider.Season == season_name,
VegasInsider.TeamID == team1.ID).first()
new_vegasinsider = VegasInsider(season_name, team1.ID, td_list[0].text,
td_list[2].text, td_list[3].text, td_list[4].text, td_list[5].text,
td_list[6].text, td_list[7].text, td_list[8].text, td_list[9].text,
td_list[10].text, td_list[11].text, td_list[12].text,
td_list[13].text,
td_list[14].text, td_list[15].text, td_list[16].text,
td_list[17].text,
td_list[18].text, td_list[19].text, td_list[20].text,
td_list[21].text,
td_list[22].text, td_list[23].text)
if not db_record:
session.add(new_vegasinsider)
if action_type == 0:
total_sheet_data.append((season_name, team1.ShortTeamName, team1.FullTeamName, td_list[0].text,
td_list[2].text, td_list[3].text, td_list[4].text, td_list[5].text,
td_list[6].text, td_list[7].text, td_list[8].text, td_list[9].text,
td_list[10].text, td_list[11].text, td_list[12].text,
td_list[13].text,
td_list[14].text, td_list[15].text, td_list[16].text,
td_list[17].text,
td_list[18].text, td_list[19].text, td_list[20].text,
td_list[21].text,
td_list[22].text, td_list[23].text))
else:
new_vegasinsider.ID = db_record.ID
session.merge(new_vegasinsider)
if action_type == 2:
team_formula_str = 'OR(SUBSTITUTE({' + ncaa_team_info[0] + '}, "\'", " ")="' + \
team_name.replace("'", " ") + '", SUBSTITUTE({' + \
ncaa_team_info[1] + '}, "\'", " ")="' + \
team_name.replace("'", " ") + '")'
ncaa_team = airtable_ncaa_team.get_all(formula=team_formula_str)
if not ncaa_team:
ncaa_team = airtable_ncaa_team.insert(
{ncaa_team_info[0]: team_name})
else:
ncaa_team = ncaa_team[0]
season = airtable_season.match(season_info[0], season_name)
if not season:
season = airtable_season.insert({season_info[0]: season_name})
formula_str = 'AND(SUBSTITUTE({Team}, "\'", " ")="' + \
ncaa_team['fields']['NCAA Team Name'].replace("'", " ") + \
'", {Season}="' + season_name + '")'
fields = {
vegasinsider_ncaa_info[0]: [season['id']],
vegasinsider_ncaa_info[1]: [ncaa_team['id']],
vegasinsider_ncaa_info[2]: int(td_list[0].text),
vegasinsider_ncaa_info[3]: float(td_list[2].text),
vegasinsider_ncaa_info[4]: float(td_list[3].text),
vegasinsider_ncaa_info[5]: float(td_list[4].text),
vegasinsider_ncaa_info[6]: float(td_list[5].text),
vegasinsider_ncaa_info[7]: float(td_list[6].text),
vegasinsider_ncaa_info[8]: int(td_list[7].text),
vegasinsider_ncaa_info[9]: int(td_list[8].text),
vegasinsider_ncaa_info[10]: float(td_list[9].text),
vegasinsider_ncaa_info[11]: float(td_list[10].text),
vegasinsider_ncaa_info[12]: float(td_list[11].text),
vegasinsider_ncaa_info[13]: int(td_list[12].text),
vegasinsider_ncaa_info[14]: int(td_list[13].text),
vegasinsider_ncaa_info[15]: int(td_list[14].text),
vegasinsider_ncaa_info[16]: float(td_list[15].text),
vegasinsider_ncaa_info[17]: float(td_list[16].text),
vegasinsider_ncaa_info[18]: float(td_list[17].text),
vegasinsider_ncaa_info[19]: float(td_list[18].text),
vegasinsider_ncaa_info[20]: float(td_list[19].text),
vegasinsider_ncaa_info[21]: float(td_list[20].text),
vegasinsider_ncaa_info[22]: float(td_list[21].text),
vegasinsider_ncaa_info[23]: int(td_list[22].text),
vegasinsider_ncaa_info[24]: int(td_list[23].text)
}
record = airtable_vegasinsider_ncaa.get_all(formula=formula_str)
if record:
airtable_vegasinsider_ncaa.replace(record[0]['id'], fields)
else:
airtable_vegasinsider_ncaa.insert(fields)
if action_type == 0 and len(total_sheet_data) > 0: # store into gsheet
df = pd.DataFrame(total_sheet_data, columns=VegasInsider.gsheet_table_columns)
original_df = wks.get_as_df()
original_df = original_df.append(df)
wks.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_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_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))
