def refresh_user_data(display_name, league_id, user_id, team_name):
db = open_connection.open_connection()
cursor = db.cursor()
insert_query = """INSERT INTO users_tbl(display_name, league_id, user_id, team_name)
VALUES (%s, %s, %s, %s)
ON CONFLICT (user_id) DO UPDATE SET display_name=users_tbl.display_name, team_name=users_tbl.team_name"""
cursor.execute(insert_query, (display_name, league_id, user_id, team_name))
db.commit()
cursor.close()
db.close()
return
#enter sleeper league ID here
#l_id = ref.league_id()
l_id = references.league_id(year)
usrs = requests.get("https://api.sleeper.app/v1/league/" + l_id + "/users")
usrs_json = usrs.json()
users_data = pd.DataFrame(usrs_json)
# #### #leauge_data = pd.read_json(leauge_data_json)
# roster_data = pd.DataFrame(rosters_json)
# #pd.read_json(_, orient='split')
# print(roster_data)
drop_table("users_tbl")
user_create = """
CREATE TABLE users_tbl
(
display_name character(255),
league_id character(255),
def process_it(year, week):
#if len(str(week))<2:
# wk = '0'+str(week)
#else:
# wk = str(week)
l_id = references.league_id(year)
wk = str(week)
w_transactions = requests.get("https://api.sleeper.app/v1/league/" + l_id +
"/transactions/" + str(week))
w_transactions_json = w_transactions.json()
for i in range(0, len(w_transactions_json)):
status_date = datetime.fromtimestamp(
w_transactions_json[i]['status_updated'] / 1000)
create_date = datetime.fromtimestamp(
w_transactions_json[i]['created'] / 1000)
transaction_id = w_transactions_json[i]['transaction_id']
creater_user_id = w_transactions_json[i]['creator']
transaction_type = w_transactions_json[i]['type']
try:
waiver_bid_ammount = w_transactions_json[i]['settings'][
'waiver_bid']
except:
waiver_bid_ammount = None
transaction_status = w_transactions_json[i]['status']
if w_transactions_json[i]['adds'] != None:
for a in w_transactions_json[i]['adds']:
faab_ammount = None
player_id = a
roster_id = w_transactions_json[i]['adds'][a]
tn_rost_plyr_id = str(transaction_id) + str(roster_id) + str(
player_id)
add_drop = 'add'
asset_type = 'player'
add_transaction_data(tn_rost_plyr_id, transaction_id,
creater_user_id, transaction_type, year,
wk, status_date, create_date,
waiver_bid_ammount, transaction_status,
roster_id, player_id, add_drop,
asset_type, faab_ammount)
if w_transactions_json[i]['drops'] != None:
for d in w_transactions_json[i]['drops']:
faab_ammount = None
player_id = d
roster_id = w_transactions_json[i]['drops'][d]
tn_rost_plyr_id = str(transaction_id) + str(roster_id) + str(
player_id)
add_drop = 'drop'
asset_type = 'player'
add_transaction_data(tn_rost_plyr_id, transaction_id,
creater_user_id, transaction_type, year,
wk, status_date, create_date,
waiver_bid_ammount, transaction_status,
roster_id, player_id, add_drop,
asset_type, faab_ammount)
if w_transactions_json[i]['draft_picks'] != None:
for p in w_transactions_json[i]['draft_picks']:
faab_ammount = None
pick_yr = p['season']
pick_rd = p['round']
original_pk_owner = p['roster_id']
player_id = str(original_pk_owner) + 'pk' + '_' + str(
pick_rd) + '_' + str(pick_yr)
roster_id = p['owner_id']
tn_rost_plyr_id = str(transaction_id) + str(roster_id) + str(
player_id)
asset_type = 'pick'
add_drop = 'add'
add_transaction_data(tn_rost_plyr_id, transaction_id,
creater_user_id, transaction_type, year,
wk, status_date, create_date,
waiver_bid_ammount, transaction_status,
roster_id, player_id, add_drop,
asset_type, faab_ammount)
roster_id = p['previous_owner_id']
tn_rost_plyr_id = str(transaction_id) + str(roster_id) + str(
player_id)
add_drop = 'drop'
add_transaction_data(tn_rost_plyr_id, transaction_id,
creater_user_id, transaction_type, year,
wk, status_date, create_date,
waiver_bid_ammount, transaction_status,
roster_id, player_id, add_drop,
asset_type, faab_ammount)
if w_transactions_json[i]['waiver_budget'] != None:
for w in w_transactions_json[i]['waiver_budget']:
waiver_ammount = w['amount']
tn_rost_plyr_id = str(transaction_id) + str(roster_id) + 'faab'
player_id = str(year) + '_faab'
asset_type = 'faab'
add_drop = 'add'
roster_id = w['sender']
add_drop = 'drop'
roster_id = w['receiver']
add_transaction_data(tn_rost_plyr_id, transaction_id,
creater_user_id, transaction_type, year,
wk, status_date, create_date,
waiver_bid_ammount, transaction_status,
roster_id, player_id, add_drop,
asset_type, faab_ammount)
cursor = db.cursor()
insert_query = """INSERT INTO sleeper_raw.matchups_plr_tbl(matchup_rost_plr_key, matchup_rost_key,year,week,matchup_id,roster_id,Player_id,is_starter)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
ON CONFLICT (matchup_rost_plr_key)
DO NOTHING
"""
cursor.execute(insert_query,
(matchup_rost_plr_key, matchup_rost_key, year, week,
matchup_id, roster_id, Player_id, is_starter))
db.commit()
cursor.close()
db.close()
return
l_id = references.league_id()
'''
drop_table("sleeper_raw.matchups_tbl")
drop_table("sleeper_raw.matchups_plr_tbl")
matchup_create = """
CREATE TABLE sleeper_raw.matchups_tbl
(
matchup_rost_key character(20),
year character(4),
week character(2),
matchup_id character(2),
roster_id character(2),
players character(255) ARRAY,
starters character(255) ARRAY,
points float(1),
matchup_start_date date,