from dugout_manager.connectors.read import session_read
from dugout_manager.connectors.write import session_write
from dugout_manager.cage_models import Mlb_people_search
from dugout_manager.dugout_models import Bp_people_search
from datetime import datetime
bp_persons = session_write.query(Bp_people_search)
mlb_persons = session_read.query(Mlb_people_search).all()
bp_persons.delete()
new_entries = []
for row in mlb_persons:
new_entry = {}
new_entry['updated_timestamp'] = datetime.now()
new_entry['bpid'] = row.bpid #PK
new_entry['full_name'] = row.full_name
new_entry['active'] = row.active
new_entry['on_40'] = row.on_40
new_entry['team_id'] = row.team_id
new_entry['org_id'] = row.org_id
new_entry['birth_date'] = row.birth_date
new_entry['death_date'] = row.death_date
new_entry['throws'] = row.throws
new_entry['bats'] = row.bats
new_entry['height'] = row.height
new_entry['weight'] = row.weight
new_entry['boxscore_name'] = row.boxscore_name
new_entry['first_name_proper'] = row.first_name_proper
new_entry['first_name'] = row.first_name
new_entry['middle_name'] = row.middle_name
new_entry['last_name'] = row.last_name
from dugout_manager.connectors.read import session_read
from dugout_manager.connectors.write import session_write
from dugout_manager.cage_models import Euston_contracts
from dugout_manager.dugout_models import Bp_organizations, Xref_org, People_contracts
from sqlalchemy import func
from datetime import datetime
euston_contracts = session_read.query(Euston_contracts)
bp_organizations = session_write.query(Bp_organizations).join(
Xref_org, Bp_organizations.org_id == Xref_org.org_id)
for row in euston_contracts:
new_contract_entry = {}
new_contract_entry['contract_id'] = row.contract_id
new_contract_entry['bpid'] = row.bpid
new_contract_entry['signed_date'] = row.signed_date
new_contract_entry['terminated_date'] = row.terminated_date
new_contract_entry['duration_years_max'] = row.duration_years_max
new_contract_entry['duration_years_base'] = row.duration_years_base
new_contract_entry['duration_years_actual'] = row.duration_years_actual
new_contract_entry_org = bp_organizations.filter(
Xref_org.xref_id == row.signing_org).first()
if new_contract_entry_org:
new_contract_entry['signing_org_id'] = new_contract_entry_org.org_id
else:
continue
new_contract_entry['first_season'] = row.first_season
new_contract_entry['updated_timestamp'] = datetime.now()
new_row = People_contracts(**new_contract_entry)
session_write.add(new_row)
session_write.commit()
from dugout_manager.connectors.read import session_read
from dugout_manager.connectors.write import session_write
from dugout_manager.cage_models import Bp_xref, Mlb_people_roster_status, Mlb_teams,Mlb_people
from dugout_manager.dugout_models import Bp_teams, Bp_people_roster_status
from datetime import datetime
## copy is fairly direct, need to replace MLB team and player IDs with BP values
all_mlb_teams = session_read.query(Mlb_teams)
bp_teams = session_write.query(Bp_teams)
mlb_people_roster_status = session_read.query(Mlb_people_roster_status).join(Mlb_people,Bp_xref).all()
new_status_entries = []
# this is a light load relatively speaking, so just flush and load
session_write.query(Bp_people_roster_status).delete()
for row in mlb_people_roster_status:
new_status_entry = {}
for xref in row.people.bpxref:
new_status_entry['bpid'] = xref.bpid
new_status_entry['active'] = row.active
new_status_entry_team = all_mlb_teams.filter(Mlb_teams.id == row.current_team).first()
if new_status_entry_team:
new_status_entry_team_bp = bp_teams.filter(Bp_teams.team_name == new_status_entry_team.name ).first()
if new_status_entry_team_bp:
new_status_entry['current_team'] = new_status_entry_team_bp.team_id
else:
from dugout_manager.connectors.read import session_read
from dugout_manager.connectors.write import session_write
from dugout_manager.cage_models import Pecota_fraa_cda
from dugout_manager.dugout_models import Bp_pecota_fraa_cda
from sqlalchemy import func
from datetime import datetime
vintage_last = session_write.query(
func.max(
Bp_pecota_fraa_cda.created_datetime)).scalar() or datetime.strptime(
'01/01/01 01:01:01', '%m/%d/%y %H:%M:%S')
vintage_last_judge = session_read.query(func.max(
Pecota_fraa_cda.vintage)).scalar()
print(vintage_last_judge, vintage_last)
if vintage_last_judge > vintage_last:
pecota_raw = session_read.query(Pecota_fraa_cda).filter(
Pecota_fraa_cda.vintage == vintage_last_judge,
Pecota_fraa_cda.decile == 5).all()
session_write.query(Bp_pecota_fraa_cda).delete()
for row in pecota_raw:
new_entry = {}
new_entry['bpid'] = int(row.playerid)
new_entry['season'] = int(row.proj_year)
new_entry['created_datetime'] = row.vintage
new_entry['csaa_proj'] = row.csaa_proj
new_entry['epaa_proj'] = row.epaa_proj
new_entry['sraa_proj'] = row.sraa_proj
new_entry['traa_proj'] = row.traa_proj
new_row = Bp_pecota_fraa_cda(**new_entry)
from dugout_manager.connectors.read import session_read
from dugout_manager.connectors.write import session_write
from dugout_manager.cage_models import *
from dugout_manager.dugout_models import *
from sqlalchemy import func
from datetime import datetime
vintage_last = session_write.query(func.max(Bp_pecota_hitting_raw.created_datetime)).scalar() or datetime.strptime('01/01/01 01:01:01', '%m/%d/%y %H:%M:%S')
vintage_last_judge = session_read.query(func.max(Pecota_raw_batters.vintage)).scalar()
print(vintage_last_judge ,vintage_last)
if vintage_last_judge > vintage_last:
pecota_raw = session_read.query(Pecota_raw_batters).filter(Pecota_raw_batters.vintage == vintage_last_judge ).all()
session_write.query(Bp_pecota_hitting_raw).delete()
for row in pecota_raw:
new_entry = {}
new_entry['bpid'] = int(row.batter)
new_entry['season'] = int(row.proj_year)
new_entry['created_datetime'] = row.vintage
new_entry['drc_plus'] = row.DRC
new_entry['draa_pa'] = row.dRAA_PA
new_entry['decile'] = int(row.decile)
new_entry['hr_pa'] = row.HR_proj_pneu
new_entry['b3_pa'] = row.B3_proj_pneu
new_entry['b2_pa'] = row.B2_proj_pneu
new_entry['b1_pa'] = row.B1_proj_pneu
new_entry['roe_pa'] = row.ROE_proj_pneu
new_entry['hbp_pa'] = row.HBP_proj_pneu
new_entry['bb_pa'] = row.BB_proj_pneu
new_entry['so_pa'] = row.SO_proj_pneu
from dugout_manager.connectors.read import session_read
from dugout_manager.connectors.write import session_write
from dugout_manager.cage_models import Mlb_stats_catching, Mlb_people, Bp_xref, Mlb_teams
from dugout_manager.dugout_models import Bp_teams, Bp_stats_catching
from sqlalchemy import func
from datetime import datetime
all_mlb_teams = session_read.query(Mlb_teams)
bp_teams = session_write.query(Bp_teams)
######### TO DO dupe management
mlb_stats_catching = session_read.query(Mlb_stats_catching).join(
Mlb_people, Bp_xref).all()
for row in mlb_stats_catching:
new_entry = {}
for xref in row.people.bpxref:
new_entry['bpid'] = xref.bpid
new_entry['season'] = row.season
new_roster_entry_team = all_mlb_teams.filter(
Mlb_teams.id == row.team).first()
new_roster_entry_team_bp = bp_teams.filter(
Bp_teams.team_name == new_roster_entry_team.name).first()
if new_roster_entry_team_bp:
new_entry['team_id'] = new_roster_entry_team_bp.team_id
else:
continue
new_entry['timestamp'] = row.timestamp
new_entry['games_played'] = row.games_played
new_entry['runs'] = row.runs
from dugout_manager.connectors.read import session_read
from dugout_manager.connectors.write import session_write
from dugout_manager.cage_models import Bp_xref, Mlb_people_roster_entries, Mlb_teams, Mlb_people
from dugout_manager.dugout_models import Bp_teams, Bp_people_roster_entries
from sqlalchemy.sql.expression import func
from datetime import datetime
## copy is fairly direct, need to replace MLB team and player IDs with BP values
all_mlb_teams = session_read.query(Mlb_teams)
bp_teams = session_write.query(Bp_teams)
last_bp_roster_entry_status_date = session_write.query(
func.max(Bp_people_roster_entries.status_date)).scalar()
#
mlb_people_roster_entries = session_read.query(
Mlb_people_roster_entries).filter(Mlb_people_roster_entries.status_date >
last_bp_roster_entry_status_date).all()
print(
session_read.query(Mlb_people_roster_entries).filter(
Mlb_people_roster_entries.status_date >
last_bp_roster_entry_status_date))
new_roster_entries = []
for row in mlb_people_roster_entries:
new_roster_entry = {}
for xref in row.people.bpxref:
new_roster_entry['bpid'] = xref.bpid
new_roster_entry['jersey_number'] = row.jersey_number
from dugout_manager.connectors.read import session_read
from dugout_manager.connectors.write import session_write
from dugout_manager.cage_models import Pecota_fielder_binomials, Pecota_of_assists
from dugout_manager.dugout_models import Bp_pecota_fielder_binomials, Bp_pecota_of_assists
from sqlalchemy import func
pecota_raw = session_read.query(Pecota_fielder_binomials).all()
session_write.query(Bp_pecota_fielder_binomials).delete()
for row in pecota_raw:
new_entry = {}
new_entry['bpid'] = int(row.fld_id)
new_entry['season'] = int(row.year_proj)
new_entry['years'] = row.years
new_entry['position'] = row.pos
new_entry['ch'] = row.ch
new_entry['ch_weighted'] = row.ch_weighted
new_entry['pm_rt_var'] = row.pm_rt_var
new_entry['pm_rt'] = row.pm_rt
new_entry['pm_rt_lg'] = row.pm_rt_lg
new_entry['pm_rt_raw'] = row.pm_rt_raw
new_row = Bp_pecota_fielder_binomials(**new_entry)
session_write.add(new_row)
session_write.commit()
pecota_raw = session_read.query(Pecota_of_assists).all()
session_write.query(Bp_pecota_of_assists).delete()
for row in pecota_raw:
from dugout_manager.connectors.read import session_read
from dugout_manager.connectors.write import session_write
from dugout_manager.cage_models import Mlb_leagues, Mlb_levels, Mlb_divisions, Mlb_teams
from dugout_manager.dugout_models import Bp_leagues, Bp_divisions, Bp_levels, Bp_organizations, Bp_teams, Bp_governing_bodies
from sqlalchemy import func
from datetime import datetime
######### TO DO dupe management
bp_governing_bodies = session_write.query(Bp_governing_bodies)
bp_leagues = session_write.query(Bp_leagues)
######### fetch data FROM CAGE
mlb_levels = session_read.query(Mlb_levels).filter(
Mlb_levels.code.in_(
['win', 'aaa', 'aax', 'afa', 'afx', 'asx', 'rok', 'roa',
'mlb'])).all()
mlb_leagues = session_read.query(Mlb_leagues).join(Mlb_levels).filter(
Mlb_levels.code.in_(
['win', 'aaa', 'aax', 'afa', 'afx', 'asx', 'rok', 'roa',
'mlb'])).all()
mlb_divisions = session_read.query(Mlb_divisions).join(
Mlb_leagues, Mlb_levels).filter(
Mlb_levels.code.in_(
['win', 'aaa', 'aax', 'afa', 'afx', 'asx', 'rok', 'roa',
'mlb'])).all()
level_count = session_write.query(func.count(
Bp_levels.level_id)).scalar() #this should change to be max() not count()
level_entries = []
for level_row in mlb_levels:
new_level_entry = {}
from dugout_manager.connectors.read import session_read
from dugout_manager.connectors.write import session_write
from dugout_manager.cage_models import Pecota_runner_binomials
from dugout_manager.dugout_models import Bp_pecota_runner_binomials
from sqlalchemy import func
from datetime import datetime
pecota_raw = session_read.query(Pecota_runner_binomials).all()
session_write.query(Bp_pecota_runner_binomials).delete()
for row in pecota_raw:
new_entry = {}
new_entry['bpid'] = int(row.bat_id)
new_entry['season'] = int(row.year_proj)
new_entry['sba_var'] = row.sba_var
new_entry['sba'] = row.sba
new_entry['sb_var'] = row.sb_var
new_entry['sb'] = row.sb
new_row = Bp_pecota_runner_binomials(**new_entry)
session_write.add(new_row)
session_write.commit()
from dugout_manager.connectors.read import session_read
from dugout_manager.connectors.write import session_write
from dugout_manager.cage_models import Bp_xref, Mlb_team_rosters, Mlb_teams,Mlb_people
from dugout_manager.dugout_models import Bp_teams, Bp_team_rosters
from sqlalchemy.sql.expression import func
from datetime import datetime
mlb_team_rosters = session_read.query(Mlb_team_rosters).join(Mlb_people, Bp_xref).all()
all_mlb_teams = session_read.query(Mlb_teams)
bp_teams = session_write.query(Bp_teams)
for row in mlb_team_rosters:
new_roster_entry = {}
new_roster_entry['timestamp'] = row.timestamp
for xref in row.people.bpxref:
new_roster_entry['player'] = xref.bpid
new_roster_entry_team = all_mlb_teams.filter(Mlb_teams.id == row.team).first()
new_roster_entry_team_bp = bp_teams.filter(Bp_teams.team_name == new_roster_entry_team.name ).first()
if new_roster_entry_team_bp:
new_roster_entry['team'] = new_roster_entry_team_bp.team_id
else:
new_roster_entry['team'] = None
new_roster_entry['jersey_number'] = row.jersey_number
new_roster_entry['position'] = row.position
new_roster_entry['status'] = row.status
new_roster_entry['updated_timestamp'] = datetime.now()
new_roster_entry_row = Bp_team_rosters(**new_roster_entry)
# new_entry['version'] = row.version_date
# new_entry['pa'] = row.pa
# new_entry['drc_plus'] = row.drc_plus
# new_entry['drc_raa'] = row.drc_raa
# new_entry['fraa'] = row.fraa
# new_entry['brr'] = row.brr
# new_entry['pos_adj'] = row.pos_adj
# new_entry['rep_level'] = row.rep_level
# new_entry['drc_warp'] = row.drc_warp
# new_row = Stats_legacy_batting_daily(**new_entry)
# session_write.add(new_row)
# session_write.commit()
pitching = session_read.query(Legacy_pitching_daily).filter(
Legacy_pitching_daily.version_date > '1999-01-01 00:00:00').all()
## session_write.query(Stats_legacy_pitching_daily).delete()
for row in pitching:
new_entry = {}
new_entry['bpid'] = row.pitcher
new_entry['season'] = row.year
new_entry['level_name'] = row.lvl
new_entry['team_name'] = row.team
new_entry['version'] = row.version_date
new_entry['pa'] = row.pa
new_entry['dra'] = row.dra
new_entry['dra_minus'] = row.dra_minus
new_entry['cfip'] = row.cfip
new_entry['dra_pwarp'] = row.dra_pwarp
from dugout_manager.connectors.read import session_read
from dugout_manager.connectors.write import session_write
from dugout_manager.cage_models import Mlb_leagues, Mlb_levels, Mlb_divisions, Mlb_teams
from dugout_manager.dugout_models import Bp_leagues, Bp_divisions, Bp_levels, Bp_organizations, Bp_teams, Bp_governing_bodies
from sqlalchemy import func, exists, or_, and_
from datetime import datetime
bp_governing_bodies = session_write.query(Bp_governing_bodies)
bp_organizations = session_write.query(Bp_organizations)
bp_leagues = session_write.query(Bp_leagues)
bp_levels = session_write.query(Bp_levels)
bp_divisions = session_write.query(Bp_divisions)
mlb_leagues = session_read.query(Mlb_leagues)
mlb_levels = session_read.query(Mlb_levels)
mlb_divisions = session_read.query(Mlb_divisions)
######### TO DO dupe management
######### fetch data FROM CAGE
all_mlb_teams = session_read.query(Mlb_teams)
mlb_teams = all_mlb_teams.join(Mlb_leagues, Mlb_levels, Mlb_divisions).filter(
Mlb_teams.active == 'True',
Mlb_levels.code.in_(['win','aaa', 'aax','afa','afx','asx','rok','roa', 'mlb'])
).order_by(Mlb_levels.code).all()
team_count = session_write.query(func.count(Bp_teams.team_id)).scalar() #this should change to be max() not count()
team_entries = []
for team_row in mlb_teams:
new_team_entry = {}
team_count += 1
from dugout_manager.connectors.read import session_read
from dugout_manager.connectors.write import session_write
from dugout_manager.cage_models import Pecota_ref_bat_events_by_lineup, Pecota_ref_dyna_lg_pos_batting_stats, Pecota_ref_pitcher_league_pos
from dugout_manager.dugout_models import Bp_leagues, Bp_levels, Ref_batter_events_league_lineup, Ref_batting_stats_league_position, Ref_pitching_stats_league_position
from sqlalchemy import func
from datetime import datetime
bp_leagues = session_write.query(Bp_leagues)
bp_levels = session_write.query(Bp_levels)
### requires tabulation
batting_events_by_lineup_cage = session_read.query(Pecota_ref_bat_events_by_lineup).all()
new_entries = []
for row in batting_events_by_lineup_cage:
new_entry = {}
new_entry['season']=row.year_id
new_entry['league_id']= bp_leagues.filter(Bp_leagues.league_name==row.lg).first().league_id
new_entry['lineup_slot']=row.bat_lineup_id
new_entry['pa']=row.pa
new_entry['outs']=row.outs
new_entry['ab']=row.ab
new_entry['h']=row.h
new_entry['b1']=row.b1
new_entry['b2']=row.b2
new_entry['b3']=row.b3
new_entry['hr']=row.hr
new_entry['tb']=row.tb
new_entry['bb']=row.bb
new_entry['ubb']=row.ubb
new_entry['ibb']=row.ibb
from dugout_manager.connectors.read import session_read
from dugout_manager.connectors.write import session_write
from dugout_manager.cage_models import Judge_pecota_bpf
from dugout_manager.dugout_models import Pecota_bpf, Bp_organizations, Xref_org
from sqlalchemy import func
from datetime import datetime
bp_organizations = session_write.query(Bp_organizations).join(
Xref_org, Bp_organizations.org_id == Xref_org.org_id)
## pick org_id, cast year to season integer
judge_bpf = session_read.query(Judge_pecota_bpf).all()
pecota_bpf = session_write.query(Pecota_bpf)
pecota_bpf.delete()
new_entries = []
for row in judge_bpf:
new_entry = {}
new_entry_org = bp_organizations.filter(
Xref_org.xref_id == row.bat_team).first()
if new_entry_org:
new_entry['org_id'] = new_entry_org.org_id
else:
continue
new_entry['season'] = row.year
new_entry['bats'] = row.bats
new_entry['hr_bpf'] = row.hr_bpf
new_entry['hr_bpf_sd'] = row.hr_bpf_sd
new_entry['b3_bpf'] = row.b3_bpf
from dugout_manager.connectors.read import session_read
from dugout_manager.connectors.write import session_write
from dugout_manager.cage_models import Pecota_book_list
from dugout_manager.dugout_models import Bp_organizations, Xref_org, Bp_book_list
from sqlalchemy import func
from datetime import datetime
######### TO DO dupe management
#load
pecota_book_list = session_read.query(Pecota_book_list).all()
#xref prep
bp_organizations = session_write.query(Bp_organizations).join(
Xref_org, Bp_organizations.org_id == Xref_org.org_id)
#clear the way
bp_book_list = session_write.query(Bp_book_list)
bp_book_list.delete(
) #nothing happens until commit, which is followed by a bulk write
new_entries = []
for row in pecota_book_list:
new_entry = {}
new_entry['bpid'] = row.playerid
new_entry['position'] = row.position
new_entry['oneline'] = row.oneline
new_entry_org = bp_organizations.filter(
Xref_org.xref_id == row.team).first()
if new_entry_org:
new_entry['org_id'] = new_entry_org.org_id
else:
continue
new_entries.append(new_entry)
from dugout_manager.dugout_models import schedule, schedule_team
from sqlalchemy import func
from datetime import datetime
import pandas as pd
from numpy import nan
### read in home games
schedule_read_home = session_read.query(
Mlb_schedule.game_pk, Mlb_schedule.game_type, Mlb_schedule.season,
Mlb_schedule.home_team.label("team_id"),
Mlb_schedule.away_team.label("opp_team_id"), Mlb_schedule.status_code,
func.min(Mlb_schedule.scheduled_innings).label("scheduled_innings"),
func.max(Mlb_schedule.home_score).label("score"),
func.max(Mlb_schedule.away_score).label("opp_score"),
func.max(Mlb_schedule.game_number).label("game_number"),
func.min(Mlb_schedule.game_date).label("game_date1"),
func.max(Mlb_schedule.reschedule_date).label("game_date2")
).filter(
Mlb_schedule.season == 2020, Mlb_schedule.game_type == "R"
#, Mlb_schedule.home_team.in_((158,140))
#, Mlb_schedule.game_pk == 631289
).group_by(Mlb_schedule.game_pk, Mlb_schedule.game_type, Mlb_schedule.season,
Mlb_schedule.home_team, Mlb_schedule.away_team,
Mlb_schedule.status_code).all()
# away games
schedule_read_away = session_read.query(
Mlb_schedule.game_pk, Mlb_schedule.game_type, Mlb_schedule.season,
Mlb_schedule.away_team.label("team_id"),
Mlb_schedule.home_team.label("opp_team_id"), Mlb_schedule.status_code,
func.min(Mlb_schedule.scheduled_innings).label("scheduled_innings"),
