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.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 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.write import session_write from dugout_manager.connectors.ondeck import session_ondeck from dugout_manager.ondeck_models import Od_depth_charts_batters, Od_depth_charts_pitchers from dugout_manager.dugout_models import Bp_pecota_depthcharts_pitchers, Bp_pecota_depthcharts_batters from sqlalchemy import func from datetime import datetime ### read in existing work from admins dc_batters = session_ondeck.query(Od_depth_charts_batters) # clear the dugout pecota_depthcharts_batters = session_write.query(Bp_pecota_depthcharts_batters) pecota_depthcharts_batters.delete() #build new entries new_entries = [] for row in dc_batters: new_entry = {} new_entry['bpid'] = row.bpid new_entry['org_id'] = row.org_id new_entry['season'] = row.year new_entry['pt_c'] = row.pt_c new_entry['pt_1b'] = row.pt_1b new_entry['pt_2b'] = row.pt_2b new_entry['pt_3b'] = row.pt_3b new_entry['pt_ss'] = row.pt_ss new_entry['pt_lf'] = row.pt_lf new_entry['pt_cf'] = row.pt_cf new_entry['pt_rf'] = row.pt_rf new_entry['pt_dh'] = row.pt_dh new_entries.append(new_entry)
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 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_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.write import session_write from dugout_manager.connectors.ondeck import session_ondeck from dugout_manager.ondeck_models import Od_depth_charts_batters, Od_depth_charts_pitchers from dugout_manager.dugout_models import Bp_people_search from sqlalchemy import func from datetime import datetime ### Built to prime the system now, not update ### select active 40 bp_persons = session_write.query(Bp_people_search).filter( Bp_people_search.active == True, Bp_people_search.on_40 == True) bp_pitchers = bp_persons.filter(Bp_people_search.position == 'P').all() bp_batters = bp_persons.filter(Bp_people_search.position != 'P').all() ### read in existing work from admins dc_batters = session_ondeck.query(Od_depth_charts_batters) dc_pitchers = session_ondeck.query(Od_depth_charts_pitchers) ######### original code that does full data dump ######### needs to be converted to only (a) insert new records; (b) modify fullname,shortname,primary_pos, and, mainly, org_id for existing records entry_season = 2020 new_pitchers = [] for row in bp_pitchers: new_entry = {} if not dc_pitchers.filter( Od_depth_charts_pitchers.bpid == row.bpid).first(): new_entry['bpid'] = row.bpid new_entry['fullname'] = row.full_name new_entry['shortname'] = row.boxscore_name new_entry['primary_pos'] = 1
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 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 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_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 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 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_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:
df_single.game_date2.notnull(), None) df_single = df_single.replace({nan: None}) ## done manipulating data ## go back from pd to list of dicts to write to pg df_list = df.to_dict('records') df_list_single = df_single.to_dict('records') # re-sort the schedule (not necessary) df_list = sorted(df_list, key=lambda i: (i['game_date'], i['game_pk'], i['home_away'])) df_list_single = sorted(df_list_single, key=lambda i: (i['game_date'])) #clear teams first schedule_team_write = session_write.query(schedule_team) schedule_team_write.delete() # write plain schedule first schedule_write = session_write.query(schedule) schedule_write.delete() for new_entry in df_list_single: new_row = schedule(**new_entry) session_write.add(new_row) session_write.commit() # write schedule per team (has FK to schedule) for new_entry in df_list: