def __call__(self, parser, namespace, value, option_string=None):
config = read_config_file()
format = namespace.format if namespace.format else config.get('DEFAULT','format')
oauth = config.getboolean('DEFAULT','oauth')
# Checking ig OAuth params are defined
if oauth :
oauth = YOAuth(None, None, from_file=config.get('auth','from_file'))
attr = {
'community': True,
'format': format,
#'jsonCompact': namespace.jsonCompact if namespace.jsonCompact else config.getboolean(format, 'jsonCompact'),
'debug': namespace.debug if namespace.debug else config.getboolean(format, 'debug'),
'oauth': oauth
}
yql = MYQL(**attr)
yql.diagnostics = namespace.diagnostics if namespace.diagnostics else config.getboolean(format, 'diagnostics')
for v in value:
response = yql.rawQuery(v)
if not response.status_code == 200:
print(response.content)
sys.exit(1)
if format == 'json':
print(pretty_json(response.content))
else:
print(pretty_xml(response.content))
sys.exit(0)
def test_yahoo_fantasy_sport(self,):
oauth = OAuth1(None, None, from_file='credentials.json')
yql = MYQL(format='json', oauth=oauth)
teams = ('mlb.l.1328.t.1','mlb.l.1328.t.2')
year = '2015-05-05'
for team in teams:
response = yql.select('fantasysports.teams.roster').where(['team_key','=',team],['date','=',year])
self.assertEqual(response.status_code, 200)
if not response.status_code == 200:
return False
data = response.json()
try:
current_team = data['query']['results']['team']
print((current_team['team_id'],current_team['name'],current_team['number_of_trades'],current_team['number_of_moves']))
except (Exception,) as e:
print(e)
def test_yahoo_fantasy_sport(self,):
oauth = OAuth1(None, None, from_file='credentials.json')
yql = MYQL(format='json', oauth=oauth)
teams = ('mlb.l.1328.t.1','mlb.l.1328.t.2')
year = '2015-05-05'
for team in teams:
response = yql.select('fantasysports.teams.roster').where(['team_key','=',team],['date','=',year])
self.assertEqual(response.status_code, 200)
if not response.status_code == 200:
return False
data = response.json()
try:
current_team = data['query']['results']['team']
print(current_team['team_id'],current_team['name'],current_team['number_of_trades'],current_team['number_of_moves'])
except (Exception,) as e:
print(e)
def __init__(self, league_key, credentials='credentials.json'):
oauth = OAuth1(None, None, from_file=credentials)
self.yql = MYQL(format='json', oauth=oauth)
self.last_pick = 0
self.league_key = league_key
self.teams = self._query(QUERY_TEAMS.format(self.league_key))['team']
self.teams = { t['team_key']: t['name'] for t in self.teams }
print(json.dumps(self.teams, indent=2))
self.rosters = { name: [] for name in self.teams.values() }
class DraftState(object):
def __init__(self, league_key, credentials='credentials.json'):
oauth = OAuth1(None, None, from_file=credentials)
self.yql = MYQL(format='json', oauth=oauth)
self.last_pick = 0
self.league_key = league_key
self.teams = self._query(QUERY_TEAMS.format(self.league_key))['team']
self.teams = { t['team_key']: t['name'] for t in self.teams }
print(json.dumps(self.teams, indent=2))
self.rosters = { name: [] for name in self.teams.values() }
def _query(self, query):
response = self.yql.raw_query(query)
data = json.loads(response.content)
return data['query']['results']
def refresh(self):
# TODO exclude known picks from query
picks = self._query(QUERY_DRAFT_RESULTS.format(self.league_key))['league']['draft_results']['draft_result'][self.last_pick:]
player_keys = []
teams = []
for p in picks:
if 'player_key' in p and p['player_key']:
player_keys.append("'{}'".format(p['player_key']))
teams.append(self.teams[p['team_key']])
else:
self.last_pick = int(p['pick']) - 1
break
players = []
if player_keys:
players = self._query(QUERY_PLAYER_KEYS.format(', '.join(player_keys)))['player']
if isinstance(players, dict):
players = [players]
players = [p['name']['full'] for p in players]
return jsonify(picks=players, teams=teams)
from sqlalchemy import create_engine
import myql
from yahoo_oauth import OAuth1
from myql import MYQL
import xml.etree.ElementTree as ET
app = Flask(__name__)
# only consumer_key and consumer_secret are required.
oauth = OAuth1(None, None, from_file='../oauth1.json')
guid = oauth.guid
consumer_key = oauth.consumer_key
consumer_secret = oauth.consumer_secret
session_handle = oauth.session_handle
access_token_secret = oauth.access_token_secret
yql = MYQL(format='xml', oauth=oauth)
league_key = 'nhl.l.22070'
NSMAP = {'yh': 'http://fantasysports.yahooapis.com/fantasy/v2/base.rng'}
def get_current_week():
current_week = yql.raw_query(
'select current_week from fantasysports.leagues.standings where league_key in ("' + league_key + '")')
week_tree = ET.fromstring(current_week.text)
week = None
for data in week_tree.findall('.//yh:league', namespaces=NSMAP):
week = int(data.find('.//yh:current_week', namespaces=NSMAP).text)
break
if week:
return week
def test_show_tables(self,):
yql = MYQL(format='xml', community=False)
response = yql.show_tables(format='xml')
logging.debug(prettyfy(response, 'xml'))
self.assertEqual(response.status_code, 200)
def setUp(self,):
self.yql = MYQL(format='json',community=True)
self.insert_result = None
class TestMYQL(unittest.TestCase):
def setUp(self,):
self.yql = MYQL(format='json',community=True)
self.insert_result = None
def tearDown(self):
pass
def test_desc(self,):
response = self.yql.desc('weather.forecast')
logging.debug(prettyfy(response, 'json'))
self.assertEqual(response.status_code, 200)
def test_show_tables(self,):
yql = MYQL(format='xml', community=False)
response = yql.show_tables(format='xml')
logging.debug(prettyfy(response, 'xml'))
self.assertEqual(response.status_code, 200)
def test_use(self):
self.yql.use('http://www.josuebrunel.org/users.xml',name='users')
response = self.yql.raw_query('select * from users', format='xml')
self.yql.yql_table_url = None
logging.debug(pretty_xml(response.content))
self.assertEqual(response.status_code, 200)
def test_raw_query(self,):
response = self.yql.raw_query('select name, woeid from geo.states where place="Congo"')
logging.debug(pretty_json(response.content))
self.assertEqual(response.status_code, 200)
def test_get(self,):
self.yql.format = 'xml'
response = self.yql.get('geo.countries', ['name', 'woeid'], 1)
self.yql.format = 'json'
logging.debug(pretty_xml(response.content))
self.assertEqual(response.status_code, 200)
def test_select(self,):
response = self.yql.select('geo.countries', ['name', 'code', 'woeid']).where(['name', '=', 'Canada'])
logging.debug(pretty_json(response.content))
self.assertEqual(response.status_code, 200)
def test_select_in(self,):
response = self.yql.select('yahoo.finance.quotes').where(['symbol','in',("YHOO","AAPL","GOOG")])
logging.debug(pretty_json(response.content))
self.assertEqual(response.status_code, 200)
def test_select_in_2(self,):
response = self.yql.select('weather.forecast',['units','atmosphere']).where(['woeid','IN',('select woeid from geo.places(1) where text="Paris"',)])
logging.debug(pretty_json(response.content))
self.assertEqual(response.status_code, 200)
def test_raise_exception_select_where_in(self,):
with self.assertRaises(TypeError):
response = self.yql.select('weather.forecast',['units','atmosphere']).where(['woeid','IN',('select woeid from geo.places(1) where text="Paris"')])
def test_1_insert(self,):
response = self.yql.insert('yql.storage.admin',('value',),('http://josuebrunel.org',))
try:
logging.debug(pretty_json(response.content))
data = response.json()['query']['results']['inserted']
logging.debug(data)
json_write_data(data,'yql_storage.json')
except (Exception,) as e:
logging.error(response.content)
logging.error(e)
self.assertEqual(response.status_code, 200)
def test_2_check_insert(self,):
json_data = json_get_data('yql_storage.json')
response = self.yql.select('yql.storage').where(['name','=',json_data['select']])
logging.debug(pretty_json(response.content))
self.assertEqual(response.status_code, 200)
def test_3_update(self,):
json_data = json_get_data('yql_storage.json')
response = self.yql.update('yql.storage',('value',),('https://josuebrunel.org',)).where(['name','=',json_data['update']])
logging.debug(pretty_json(response.content))
self.assertEqual(response.status_code, 200)
def test_4_delete(self,):
json_data = json_get_data('yql_storage.json')
response = self.yql.delete('yql.storage').where(['name','=',json_data['update']])
logging.debug(pretty_json(response.content))
self.assertEqual(response.status_code, 200)
def test_cross_product(self):
yql = YQL(format='xml', crossProduct=True)
response = yql.select('weather.forecast').where(['location', '=', '90210'])
logging.debug("{0} {1}".format(response.status_code, response.reason))
self.assertEqual(response.status_code, 200)
def test_variable_substitution(self,):
yql = YQL()
var = {'home': 'Congo'}
yql.set(var)
response = yql.select('geo.states', remote_filter=(5,)).where(['place', '=', '@home'])
logging.debug(pretty_json(response.content))
self.assertEqual(response.status_code, 200)
def test_raise_exception_no_table_selected(self):
with self.assertRaises(NoTableSelectedError):
response = self.yql.select(None).where([])
def test_get_guid(self,):
oauth = OAuth1(None, None, from_file='credentials.json')
yql = MYQL(format='json', oauth=oauth)
response = yql.get_guid('josue_brunel')
logging.debug(pretty_json(response.content))
self.assertEqual(response.status_code, 200)
class NHL_Info:
# only consumer_key and consumer_secret are required.
oauth = OAuth1(None, None, from_file='../oauth1.json')
guid = oauth.guid
consumer_key = oauth.consumer_key
consumer_secret = oauth.consumer_secret
session_handle = oauth.session_handle
access_token_secret = oauth.access_token_secret
yql = MYQL(format='xml', oauth=oauth)
league_key = 'nhl.l.22070'
my_team_key = '363.l.22070.t.9'
NSMAP = {'yh': 'http://fantasysports.yahooapis.com/fantasy/v2/base.rng'}
pd.set_option('precision', 2)
# relative location of fantasy league relevant statistical categories in
# player statistics array
skater_stat_ids = [1, 2, 3, 4, 6, 12, 13, 14, 15, 17]
no_NHL_teams = 30
team_abbr = [
'ANA', 'ARI', 'BOS', 'BUF', 'CGY', 'CAR', 'CHI', 'COL', 'COB', 'DAL',
'DET', 'EDM', 'FLA', 'LOS', 'MIN', 'MON', 'NAS', 'NJD', 'NYI', 'NYR',
'OTT', 'PHI', 'PIT', 'SAN', 'STL', 'TAM', 'TOR', 'VAN', 'WAS', 'WPG'
]
def __init__(self, week, team=None):
self.week = week
self.team = team
self.week_corrected = ""
if self.week == [x for x in range(0, 10)]:
self.week_corrected = "0{}".format(str(self.week))
else:
self.week_corrected = str(self.week)
self.engine = create_engine(
'mysql+pymysql://root:jamesonrogers@localhost:3306/db_fhlo',
echo=False,
poolclass=sqlalchemy.pool.NullPool)
self.df_league = self.get_fantasy_league_info()
if team != None:
self.team_key = str(self.df_league[self.df_league['Name'] ==
self.team].values[0][7])
self.df_my_player_data = self.get_my_player_data()
self.df_all_player_data = self.get_all_player_data()
self.df_past = self.get_past()
self.total_points = self.get_projected_points(self.team_key)
def get_my_player_data(self):
# connection = self.connect()
try:
with self.engine.connect() as conn:
sql = "SELECT * FROM `df_my_player_data_{}_{}`".format(
self.team_key, self.week_corrected)
# cursor.execute(sql)
columns = [
'Name', 'Age', 'Team', 'Position', 'GP', 'Points',
'Average_PPG_Past', 'Average_PPG', 'Games_This_Week',
'Projected_Points'
]
df_my_player_data = pd.read_sql(sql=sql, con=conn)
except Exception as e:
raise
else:
print "success getting df_my_player_data from db"
return df_my_player_data
def get_all_player_data(self):
connection = self.connect()
try:
with connection.cursor() as cursor:
sql = "SELECT * FROM `df_all_player_data`"
cursor.execute(sql)
columns = [
'Name', 'Team', 'GP', 'G', 'A', 'PIM', 'PPP', 'SHP',
'Points', 'Average_PPG', 'Games_This_Week',
'Projected_Points'
]
df_all_player_data = pd.DataFrame(cursor.fetchall(),
columns=columns)
finally:
connection.close()
return df_all_player_data
def get_past(self):
connection = self.connect()
try:
with connection.cursor() as cursor:
sql = "SELECT * FROM `df_past`"
cursor.execute(sql)
columns = [
'name', 'Age', 'Position', 'GP_1314', 'GP_1415', 'GP_1516',
'AverageAverage_GP_Past', 'Points_1314', 'Points_1415',
'Points_1516', 'AverAverage_Points_Past',
'Average_PPG_Past'
]
df_past = pd.DataFrame(cursor.fetchall())
df_past.rename(columns={'name': 'Name'}, inplace=True)
df_past = df_past[[
'Name', 'Position', 'Age', 'GP_1314', 'GP_1415', 'GP_1516',
'Average_GP_Past', 'Points_1314', 'Points_1415',
'Points_1516', 'Average_Points_Past', 'Average_PPG_Past'
]]
finally:
connection.close()
return df_past
def get_fantasy_league_info(self):
print "*get_fantasy_league_info(self)"
connection = self.connect()
try:
with connection.cursor() as cursor:
sql = "SELECT * FROM `df_league_{}`".format(
self.week_corrected)
cursor.execute(sql)
df = pd.DataFrame(cursor.fetchall(),
columns=[
'name', 'rank', 'w-l-t', 'points',
'trades', 'moves', 'draft_pos',
'team_key'
])
# rename the columns for aesthetics
df = df.rename(
columns={
'name': 'Name',
'rank': 'Rank',
'w-l-t': 'Wins-Losses-Ties',
'points': 'Points',
'trades': 'Trades',
'moves': 'Moves',
'draft_pos': 'Draft Position',
'team_key': 'Key'
})
proj_points = {}
try:
for key in df['Key']:
print " Key to get projected points for:", key
proj_points[key] = [self.get_projected_points(key)]
df_points = pd.DataFrame.from_dict(proj_points,
orient='index')
df_points = df_points.rename(
columns={0: 'Projected_Points'})
print df_points
df = df.merge(df_points,
left_on=df.Key,
right_on=df_points.index.values)
print df
except Exception as e:
print e
else:
print " success appending Projected_Points to league dataframe"
# return df
except:
print " don't have current week info so get it"
update = Update(self.week, self)
return update.main()
else:
print " successfully got current week fantasy league info from the database"
return df
finally:
connection.close()
'''
Queries database to get past 3 seasons of player data
@params [self]
@returns [df] pandas dataframe of player stats with player name as the index
'''
def get_past_3_seasons_player_stats(self):
print "get_past_3_seasons_player_stats(self)"
connection = self.connect()
try:
with connection.cursor() as cursor:
# Read a single record
sql = "SELECT * FROM `seasons_past`"
cursor.execute(sql)
df = pd.DataFrame(cursor.fetchall(),
columns=[
'fname', 'lname', 'age', 'pos',
'gp_1314', 'gp_1415', 'gp_1516',
'pts_1314', 'pts_1415', 'pts_1516'
])
df["name"] = df["fname"] + ' ' + df["lname"]
df.set_index(['name'], inplace=True)
del df['fname']
del df['lname']
df["avg_gp"] = \
df[['gp_1314', 'gp_1415', 'gp_1516']].mean(axis=1)
df["avg_pts"] = \
df[['pts_1314', 'pts_1415', 'pts_1516']].mean(axis=1)
df["avg_ppg"] = df["avg_pts"] / df["avg_gp"]
# rename the columns for aesthetics
df = df.rename(
columns={
'name': 'Name',
'pos': 'Position',
'age': 'Age',
'gp_1314': 'GP_1314',
'gp_1415': 'GP_1415',
'gp_1516': 'GP_1516',
'avg_gp': 'Average_GP_Past',
'pts_1314': 'Points_1314',
'pts_1415': 'Points_1415',
'pts_1516': 'Points_1516',
'avg_pts': 'Average_Points_Past',
'avg_ppg': 'Average_PPG_Past'
})
finally:
connection.close()
return df
def get_projected_points(self, team_key=None):
print "get_projected_points(self, team_key=None)"
if team_key == None:
team_key = self.team_key
connection = self.connect()
try:
with connection.cursor() as cursor:
sql = "SELECT SUM(Projected_Points) as sum_points FROM `df_my_player_data_{}_{}` WHERE Roster_Position != 'BN' AND Roster_Position != 'IR'".format(
team_key, self.week_corrected)
cursor.execute(sql)
sum_points = cursor.fetchall()
points = sum_points[0]['sum_points']
print points
except Exception as e:
raise
else:
print " success getting summed points from db"
return points
finally:
connection.close()
'''
Establishes a connection to the mySQL db
@params [self]
@return [connection]
'''
def connect(self):
print "connect(self)"
# Connect to the database
try:
print " Attempting connection to database..."
connection = pymysql.connect(
host='localhost',
port=3306,
user='******',
password='******',
db='db_fhlo',
cursorclass=pymysql.cursors.DictCursor)
except Exception as e:
raise
else:
print " Database successfully connected."
return connection
class Update:
# only consumer_key and consumer_secret are required.
oauth = OAuth1(None, None, from_file='../oauth1.json')
guid = oauth.guid
consumer_key = oauth.consumer_key
consumer_secret = oauth.consumer_secret
session_handle = oauth.session_handle
access_token_secret = oauth.access_token_secret
yql = MYQL(format='xml', oauth=oauth)
league_key = 'nhl.l.22070'
my_team_key = '363.l.22070.t.9'
NSMAP = {'yh': 'http://fantasysports.yahooapis.com/fantasy/v2/base.rng'}
pd.set_option('precision', 2)
# relative location of fantasy league relevant statistical categories in
# player statistics array
skater_stat_ids = [1, 2, 3, 4, 6, 12, 13, 14, 15, 17]
no_NHL_teams = 30
team_abbr = [
'ANA', 'ARI', 'BOS', 'BUF', 'CGY', 'CAR', 'CHI', 'COL', 'COB', 'DAL',
'DET', 'EDM', 'FLA', 'LOS', 'MIN', 'MON', 'NAS', 'NJD', 'NYI', 'NYR',
'OTT', 'PHI', 'PIT', 'SAN', 'STL', 'TAM', 'TOR', 'VAN', 'WAS', 'WPG'
]
# TODO: Make the week work dynamically, i.e. so that the tables update based on the current week of play
# TODO: Also make a link the user can click to update the data
def __init__(self, week=23, app=None):
self.week = week
self.week_corrected = None
self.app = app
if self.week == [x for x in range(0, 10)]:
self.week_corrected = "0{}".format(str(self.week))
else:
self.week_corrected = self.week
self.engine = create_engine(
'mysql+pymysql://root:jamesonrogers@localhost:3306/db_fhlo',
echo=False,
poolclass=sqlalchemy.pool.NullPool)
def update_league_info(self):
if not self.oauth.token_is_valid():
self.oauth.refresh_access_token()
response = self.yql.raw_query(
'select * from fantasysports.leagues.standings where league_key in ("'
+ self.league_key + '")')
if response == None:
response = self.yql.raw_query(
'select * from fantasysports.leagues.standings where league_key in ("'
+ self.league_key + '")')
league_tree = ET.fromstring(response.text)
# get the league info
league = []
self.team_keys = {}
for team in league_tree.findall('.//yh:team', namespaces=self.NSMAP):
name = team.find('.//yh:name', namespaces=self.NSMAP).text
rank = int(team.find('.//yh:rank', namespaces=self.NSMAP).text)
wins = int(team.find('.//yh:wins', namespaces=self.NSMAP).text)
losses = int(team.find('.//yh:losses', namespaces=self.NSMAP).text)
ties = int(team.find('.//yh:ties', namespaces=self.NSMAP).text)
wlt = '{}-{}-{}'.format(wins, losses, ties)
points = \
float(team.find('.//yh:points_for', namespaces=self.NSMAP).text)
self.team_key = team.find('.//yh:team_key',
namespaces=self.NSMAP).text
self.team_keys[self.team_key] = self.team_key
email = team.find('.//yh:email', namespaces=self.NSMAP).text
draft_position = team.find('.//yh:draft_position',
namespaces=self.NSMAP).text
moves = team.find('.//yh:number_of_moves',
namespaces=self.NSMAP).text
trades = team.find('.//yh:number_of_trades',
namespaces=self.NSMAP).text
league.append({
'name': name,
'rank': rank,
'w-l-t': wlt,
'points': points,
'team_key': self.team_key,
'draft_pos': draft_position,
'moves': moves,
'trades': trades
})
df_league = pd.DataFrame(league,
columns=[
'name', 'rank', 'w-l-t', 'points',
'moves', 'trades', 'draft_pos', 'team_key'
])
# df_league.set_index(['name'], inplace=True)
df_league.to_sql(con=self.engine,
name='df_league_{}'.format(self.week_corrected),
if_exists='replace',
index=False)
return df_league
def update_roster_info(self):
# TODO: build this out
return
'''
Scrapes Yahoo to find the games per week per NHL team
@params [self]
@returns [df_games_per_team] a dataframe of games per week per team
'''
def get_games_per_team(self):
# make connection to Yahoo games per week page
page = requests.get(
"https://hockey.fantasysports.yahoo.com/hockey/team_games?week=" +
str(self.week) + "")
soup = BeautifulSoup(page.content, 'html.parser')
teams = []
unparsed_teams = soup.select("td div a")
# get the teams
for i in range(len(unparsed_teams)):
teams.append(unparsed_teams[i].get_text())
no_games = []
unparsed_games = soup.find_all("td", class_="stat Tst-games")
# get the number of games
for i in range(len(unparsed_games)):
no_games.append(unparsed_games[i].get_text())
assert len(teams) == len(no_games), \
"Length of teams: %r is not equal to length of no_games: %r." % (
len(teams), len(no_games))
# put them in a dictionary
team_games = dict(zip(teams, no_games))
# parse the results to get rid of unwanted teams
team_games = {
k: v
for k, v in team_games.items() if "All-Stars" not in k
}
assert len(team_games) == self.no_NHL_teams, \
"There are: %r teams in the NHL but team_games is of size: %r." % (
self.no_NHL_teams, len(team_games))
# replace the long team names with abbreviations
self.set_NHL_teams(team_games.keys())
team_games = {self.teams_dict[k]: v for k, v in team_games.items()}
df_games_per_team = pd.DataFrame.from_dict(data=team_games,
orient='index')
df_games_per_team.columns = ['Games_This_Week']
df_games_per_team.index.name = 'Team'
df_games_per_team = df_games_per_team.convert_objects(
convert_numeric=True)
return df_games_per_team
def get_fantasy_roster_info(self, team_key, df_all_player_data):
if not self.oauth.token_is_valid():
self.oauth.refresh_access_token()
response = self.yql.raw_query(
'select * from fantasysports.teams.roster where team_key in ("' +
team_key + '")')
roster_tree = ET.fromstring(response.text)
players = {}
for player in roster_tree.findall('.//yh:player',
namespaces=self.NSMAP):
name = player.find('.//yh:full', namespaces=self.NSMAP).text
# img_url = name = player.find('.//yh:url', namespaces=self.NSMAP).text
selected_position = player.find('.//yh:selected_position',
namespaces=self.NSMAP)
position = selected_position.find('.//yh:position',
namespaces=self.NSMAP).text
players[name] = position
df_my_player_data = df_all_player_data.loc[players.keys()]
df_my_player_data = df_my_player_data[[
'Team', 'GP', 'Points', 'Average_PPG'
]]
df_my_player_data['Roster_Position'] = players.values()
return df_my_player_data
'''
Setter for NHL teams in the league, including mapping the full names to the respective abbreviations
@params [self]
@returns [team_games] a dictionary of {team,no_games} key value pairs
'''
def set_NHL_teams(self, teams):
self.teams = sorted(teams)
self.teams_dict = dict(zip(self.teams, self.team_abbr))
'''
Queries database to get past 3 seasons of player data
@params [self]
@returns [df] pandas dataframe of player stats with player name as the index
'''
def get_past_3_seasons_player_stats(self):
connection = self.connect()
try:
with connection.cursor() as cursor:
# Read a single record
sql = "SELECT * FROM `seasons_past`"
cursor.execute(sql)
df = pd.DataFrame(cursor.fetchall(),
columns=[
'fname', 'lname', 'age', 'pos',
'gp_1314', 'gp_1415', 'gp_1516',
'pts_1314', 'pts_1415', 'pts_1516'
])
df["name"] = df["fname"] + ' ' + df["lname"]
df.set_index(['name'], inplace=True)
del df['fname']
del df['lname']
df["avg_gp"] = \
df[['gp_1314', 'gp_1415', 'gp_1516']].mean(axis=1)
df["avg_pts"] = \
df[['pts_1314', 'pts_1415', 'pts_1516']].mean(axis=1)
df["avg_ppg"] = df["avg_pts"] / df["avg_gp"]
# rename the columns for aesthetics
df = df.rename(
columns={
'name': 'Name',
'pos': 'Position',
'age': 'Age',
'gp_1314': 'GP_1314',
'gp_1415': 'GP_1415',
'gp_1516': 'GP_1516',
'avg_gp': 'Average_GP_Past',
'pts_1314': 'Points_1314',
'pts_1415': 'Points_1415',
'pts_1516': 'Points_1516',
'avg_pts': 'Average_Points_Past',
'avg_ppg': 'Average_PPG_Past'
})
return df
finally:
connection.close()
'''
Scrapes Yahoo to find the YTD NHL player statistics and prints a table of the results
@params [self]
'''
def ytd_player_details(self):
page = requests.get(
"https://ca.sports.yahoo.com/nhl/stats/byposition?pos=C,RW,LW,D")
soup = BeautifulSoup(page.content, 'html.parser')
unparsed_headers = soup.find_all("a", class_="ysptblhdrsts")
headers = []
# get the headers
for i in range(len(unparsed_headers)):
headers.append(unparsed_headers[i].get_text())
headers = [
headers[i] for i in range(len(headers))
if i in self.skater_stat_ids
]
headers.remove('PPG')
headers.remove('SHG')
headers = [s.replace('PPA', 'PPP') for s in headers]
headers = [s.replace('SHA', 'SHP') for s in headers]
headers.append('Points')
headers.append('Average_PPG')
# get the player data
unparsed_data = soup.find_all("td", class_="yspscores")
data = []
for i in range(len(unparsed_data)):
data.append(unparsed_data[i].get_text())
# clear unwanted spaces
data = [s.strip() for s in data]
data = list(filter(None, data))
# split each player into their own list, all of which are appended to a
# new list
player_data = [data[x:x + 19] for x in range(0, len(data), 19)]
player_names = [data[i][:] for i in range(0, len(data), 19)]
relevant_player_data = []
points = None
average_ppg = None
for player in player_data:
for i in range(len(player)):
name = player[0]
team = player[1]
gp = player[2]
g = player[3]
a = player[4]
pim = player[6]
ppp = int(player[12]) + int(player[13])
shp = int(player[14]) + int(player[15])
sog = player[17]
if i in self.skater_stat_ids:
if i == 12:
relevant_player_data.append(ppp)
elif i == 14:
relevant_player_data.append(shp)
elif i == 13 or i == 15:
pass
else:
relevant_player_data.append(player[i])
points = (int(g) * (3) + int(a) * (2) + int(pim) * (0.25) +
int(ppp) * (0.5) + int(shp) * (1) + int(sog) * (0.5))
average_ppg = points / int(gp)
relevant_player_data.append(points)
relevant_player_data.append(average_ppg)
final_relevant_player_data = \
[relevant_player_data[x:x + 10]
for x in range(0, len(relevant_player_data), 10)]
df_player_data = pd.DataFrame(data=final_relevant_player_data,
columns=headers,
index=player_names)
# TODO: get all player data here
# df_player_data_all.to_sql(con=self.engine, name='player_data_all', if_exists='replace', index=False)
df_player_data.index.name = 'Name'
return df_player_data
def get_projected_points(self, team_key=None):
print "get_projected_points(self, team_key=None)"
if team_key == None:
team_key = self.team_key
connection = self.connect()
try:
with connection.cursor() as cursor:
sql = "SELECT SUM(Projected_Points) as sum_points FROM `df_my_player_data_{}_{}` WHERE Roster_Position != 'BN' AND Roster_Position != 'IR'".format(
team_key, self.week_corrected)
cursor.execute(sql)
sum_points = cursor.fetchall()
points = sum_points[0]['sum_points']
print points
except Exception as e:
raise
else:
print " success getting summed points from db"
return points
finally:
connection.close()
'''
Establishes a connection to the mySQL db
@params [self]
@return [connection]
'''
def connect(self):
# Connect to the database
try:
print "Attempting connection to database..."
connection = pymysql.connect(
host='localhost',
port=3306,
user='******',
password='******',
db='db_fhlo',
cursorclass=pymysql.cursors.DictCursor)
except Exception as e:
raise
else:
print "Database successfully connected."
return connection
def main(self):
try:
df = pd.read_sql_table('df_league_{}'.format(self.week_corrected),
con=self.engine)
except:
print "no tables for given week"
self.app.df_league = self.update_league_info()
print "UPDATING ALL WEEK INFO"
df_all_player_data = self.ytd_player_details()
df_games_per_team = self.get_games_per_team()
df_past = self.get_past_3_seasons_player_stats()
self.df_past_relevant = df_past[[
'Age', 'Position', 'Average_PPG_Past'
]]
df_all_player_data = pd.merge(
df_all_player_data,
df_games_per_team,
right_on=df_games_per_team.index.values,
left_on=df_all_player_data.Team,
right_index=True)
projected_points = df_all_player_data.Average_PPG * \
df_all_player_data.Games_This_Week
df_all_player_data['Projected_Points'] = projected_points
for k, v in self.team_keys.iteritems():
print 'TEAM KEY', v
df_my_player_data = self.get_fantasy_roster_info(
v, df_all_player_data)
df_my_player_data = pd.merge(
df_my_player_data,
df_games_per_team,
right_on=df_games_per_team.index.values,
left_on=df_my_player_data.Team,
right_index=True)
df_my_player_data = df_my_player_data.join(
self.df_past_relevant)
projected_points = df_my_player_data.Average_PPG * \
df_my_player_data.Games_This_Week
df_my_player_data['Projected_Points'] = projected_points
# df_my_player_data = df_my_player_data.reset_index(inplace=False)
df_my_player_data['Name'] = df_my_player_data.index
df_my_player_data = df_my_player_data[[
'Name', 'Team', 'Position', 'Roster_Position', 'Age', 'GP',
'Points', 'Average_PPG', 'Games_This_Week',
'Average_PPG_Past', 'Projected_Points'
]]
# create or replace sql tables with updated information
df_my_player_data.to_sql(con=self.engine,
name="df_my_player_data_{}_{}".format(
v, self.week_corrected),
if_exists='replace',
index=False)
print 'TEAM KEY', v
df_all_player_data.reset_index(inplace=True)
# df_past.reset_index(inplace=True)
df_past['Name'] = df_past.index
df_past = df_past[[
'Name', 'Position', 'Age', 'GP_1314', 'GP_1415', 'GP_1516',
'Average_GP_Past', 'Points_1314', 'Points_1415', 'Points_1516',
'Average_Points_Past', 'Average_PPG_Past'
]]
df_past.to_sql(con=self.engine,
name="df_past",
if_exists='replace',
index=False)
df_all_player_data.to_sql(con=self.engine,
name="df_all_player_data",
if_exists='replace',
index=False)
self.app.df_league = self.app.df_league.rename(
columns={
'name': 'Name',
'rank': 'Rank',
'w-l-t': 'Wins-Losses-Ties',
'points': 'Points',
'trades': 'Trades',
'moves': 'Moves',
'draft_pos': 'Draft Position',
'team_key': 'Key'
})
proj_points = {}
try:
for key in self.app.df_league['Key']:
print " Key to get projected points for:", key
proj_points[key] = [self.get_projected_points(key)]
df_points = pd.DataFrame.from_dict(proj_points, orient='index')
df_points = df_points.rename(columns={0: 'Projected_Points'})
print df_points
self.app.df_league = self.app.df_league.merge(
df_points,
left_on=self.app.df_league.Key,
right_on=df_points.index.values)
print self.app.df_league
except Exception as e:
print e
else:
print " success appending Projected_Points to league dataframe"
return self.app.df_league
else:
print "ALREADY UPDATED THIS WEEK"
df = df.rename(
columns={
'name': 'Name',
'rank': 'Rank',
'w-l-t': 'Wins-Losses-Ties',
'points': 'Points',
'trades': 'Trades',
'moves': 'Moves',
'draft_pos': 'Draft Position',
'team_key': 'Key'
})
proj_points = {}
try:
for key in df['Key']:
print " Key to get projected points for:", key
proj_points[key] = [self.get_projected_points(key)]
df_points = pd.DataFrame.from_dict(proj_points, orient='index')
df_points = df_points.rename(columns={0: 'Projected_Points'})
print df_points
df = df.merge(df_points,
left_on=df.Key,
right_on=df_points.index.values)
print df
except Exception as e:
print e
else:
print " success appending Projected_Points to league dataframe"
return df
def test_get_guid(self, ):
oauth = OAuth1(None, None, from_file='credentials.json')
yql = MYQL(format='json', oauth=oauth)
response = yql.get_guid('josue_brunel')
logging.debug(pretty_json(response.content))
self.assertEqual(response.status_code, 200)
from yahoo_oauth import OAuth1
from myql.utils import pretty_json
from myql import MYQL
from stat_ids import stat_ids
import csv
import os
# read in consumer secret and key
# only consumer_key and consumer_secret are required.
oauth = OAuth1(None, None, from_file='credentials.json')
if not oauth.token_is_valid():
oauth.refresh_access_token()
yql = MYQL(format='json', oauth=oauth)
# get league key info from yahoo
response = yql.raw_query('select * from fantasysports.leagues where use_login=1 and game_key in ("mlb")')
r = response.json()
# if in multiple leagues will need to adjust these lines to make sure you're grabbing the right data.
league_key = r['query']['results']['league'][0]['league_key']
num_teams = int(r['query']['results']['league'][0]['num_teams'])
print league_key
teamid = range(1,(num_teams + 1))
rotostats = {}
first = 0
for i in teamid:
team_key = league_key + '.t.' + str(i)
stats_query = "select * from fantasysports.teams.stats where team_key='" + team_key +"'"
response = yql.raw_query(stats_query)
def test_show_tables(self, ):
yql = MYQL(format='xml', community=False)
response = yql.show_tables(format='xml')
logging.debug(prettyfy(response, 'xml'))
self.assertEqual(response.status_code, 200)
def setUp(self, ):
self.yql = MYQL(format='json', community=True)
self.insert_result = None
class TestMYQL(unittest.TestCase):
def setUp(self, ):
self.yql = MYQL(format='json', community=True)
self.insert_result = None
def tearDown(self):
pass
def test_desc(self, ):
response = self.yql.desc('weather.forecast')
logging.debug(prettyfy(response, 'json'))
self.assertEqual(response.status_code, 200)
def test_show_tables(self, ):
yql = MYQL(format='xml', community=False)
response = yql.show_tables(format='xml')
logging.debug(prettyfy(response, 'xml'))
self.assertEqual(response.status_code, 200)
def test_use(self):
self.yql.use('http://www.josuebrunel.org/users.xml', name='users')
response = self.yql.raw_query('select * from users', format='xml')
self.yql.yql_table_url = None
logging.debug(pretty_xml(response.content))
self.assertEqual(response.status_code, 200)
def test_raw_query(self, ):
response = self.yql.raw_query(
'select name, woeid from geo.states where place="Congo"')
logging.debug(pretty_json(response.content))
self.assertEqual(response.status_code, 200)
def test_get(self, ):
self.yql.format = 'xml'
response = self.yql.get('geo.countries', ['name', 'woeid'], 1)
self.yql.format = 'json'
logging.debug(pretty_xml(response.content))
self.assertEqual(response.status_code, 200)
def test_select(self, ):
response = self.yql.select('geo.countries',
['name', 'code', 'woeid']).where(
['name', '=', 'Canada'])
logging.debug(pretty_json(response.content))
self.assertEqual(response.status_code, 200)
def test_select_in(self, ):
response = self.yql.select('yahoo.finance.quotes').where(
['symbol', 'in', ("YHOO", "AAPL", "GOOG")])
logging.debug(pretty_json(response.content))
self.assertEqual(response.status_code, 200)
def test_select_in_2(self, ):
response = self.yql.select(
'weather.forecast', ['units', 'atmosphere']).where([
'woeid', 'IN',
('select woeid from geo.places(1) where text="Paris"', )
])
logging.debug(pretty_json(response.content))
self.assertEqual(response.status_code, 200)
def test_raise_exception_select_where_in(self, ):
with self.assertRaises(TypeError):
response = self.yql.select(
'weather.forecast', ['units', 'atmosphere']).where([
'woeid', 'IN',
('select woeid from geo.places(1) where text="Paris"')
])
def test_1_insert(self, ):
response = self.yql.insert('yql.storage.admin', ('value', ),
('http://josuebrunel.org', ))
try:
logging.debug(pretty_json(response.content))
data = response.json()['query']['results']['inserted']
logging.debug(data)
json_write_data(data, 'yql_storage.json')
except (Exception, ) as e:
logging.error(response.content)
logging.error(e)
self.assertEqual(response.status_code, 200)
def test_2_check_insert(self, ):
json_data = json_get_data('yql_storage.json')
response = self.yql.select('yql.storage').where(
['name', '=', json_data['select']])
logging.debug(pretty_json(response.content))
self.assertEqual(response.status_code, 200)
def test_3_update(self, ):
json_data = json_get_data('yql_storage.json')
response = self.yql.update('yql.storage', ('value', ),
('https://josuebrunel.org', )).where(
['name', '=', json_data['update']])
logging.debug(pretty_json(response.content))
self.assertEqual(response.status_code, 200)
def test_4_delete(self, ):
json_data = json_get_data('yql_storage.json')
response = self.yql.delete('yql.storage').where(
['name', '=', json_data['update']])
logging.debug(pretty_json(response.content))
self.assertEqual(response.status_code, 200)
def test_cross_product(self):
yql = YQL(format='xml', crossProduct=True)
response = yql.select('weather.forecast').where(
['location', '=', '90210'])
logging.debug("{0} {1}".format(response.status_code, response.reason))
self.assertEqual(response.status_code, 200)
def test_variable_substitution(self, ):
yql = YQL()
var = {'home': 'Congo'}
yql.set(var)
response = yql.select('geo.states', remote_filter=(5, )).where(
['place', '=', '@home'])
logging.debug(pretty_json(response.content))
self.assertEqual(response.status_code, 200)
def test_raise_exception_no_table_selected(self):
with self.assertRaises(NoTableSelectedError):
response = self.yql.select(None).where([])
