######### 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)
for new_entry in new_entries:
new_row = Bp_book_list(**new_entry)
session_write.add(new_row)
session_write.commit()
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:
new_status_entry['current_team'] = None
else:
new_status_entry['current_team'] = None
new_status_entry['last_played_date'] = row.last_played_date
new_status_entry['mlb_debut_date'] = row.mlb_debut_date
new_status_entry['updated_timestamp'] = datetime.now()
new_status_entry_row = Bp_people_roster_status(**new_status_entry)
session_write.add(new_status_entry_row)
session_write.commit()
Bp_levels.level_id)).scalar() #this should change to be max() not count()
level_entries = []
for level_row in mlb_levels:
new_level_entry = {}
level_count += 1
new_level_entry['level_id'] = level_count
new_level_entry['level_name'] = level_row.code
new_level_entry['gov_bod_id'] = bp_governing_bodies.filter(
Bp_governing_bodies.gov_bod_name ==
'Major League Baseball').first().gov_bod_id
new_level_entry['updated_timestamp'] = datetime.now()
level_entries.append(new_level_entry)
for level_entry in level_entries:
new_level_row = Bp_levels(**level_entry)
session_write.add(new_level_row)
league_count = session_write.query(func.count(Bp_leagues.league_id)).scalar(
) #this should change to be max() not count()
league_entries = []
for league_row in mlb_leagues:
new_league_entry = {}
league_count += 1
new_league_entry['league_id'] = league_count
new_league_entry['league_name'] = league_row.abbreviation
new_league_entry['gov_bod_id'] = bp_governing_bodies.filter(
Bp_governing_bodies.gov_bod_name ==
'Major League Baseball').first().gov_bod_id
new_league_entry['updated_timestamp'] = datetime.now()
league_entries.append(new_league_entry)
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
new_roster_entry['position'] = row.position
new_roster_entry['status'] = row.status
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['is_active'] = row.is_active
new_roster_entry['start_date'] = row.start_date
new_roster_entry['end_date'] = row.end_date
new_roster_entry['status_date'] = row.status_date
new_roster_entry['is_active_forty_man'] = row.is_active_forty_man
new_roster_entry['updated_timestamp'] = datetime.now()
new_roster_entries.append(new_roster_entry)
for roster_entry in new_roster_entries:
new_roster_entry_row = Bp_people_roster_entries(**roster_entry)
session_write.add(new_roster_entry_row)
# session_write.commit()
new_team_entry['org_id'] = bp_organizations.filter(Bp_organizations.org_name==parent_abbrev.abbreviation).first().org_id
new_team_entry['league_id'] = bp_leagues.filter(
Bp_leagues.league_name == mlb_leagues.filter(Mlb_leagues.id==team_row.league).first().abbreviation
).first().league_id
team_level_lookup = mlb_levels.filter(Mlb_levels.id==team_row.level).first()
if not team_level_lookup:
new_team_entry['level_id'] = None
else:
new_team_entry['level_id'] = bp_levels.filter(
Bp_levels.level_name == team_level_lookup.code
).first().level_id
team_division_lookup = mlb_divisions.filter(Mlb_divisions.id==team_row.division).first()
if not team_division_lookup:
new_team_entry['division_id'] = None
else:
new_team_entry['division_id'] = bp_divisions.filter(
Bp_divisions.division_name == team_division_lookup.abbreviation
).first().division_id
new_team_entry['updated_timestamp'] = datetime.now()
team_entries.append(new_team_entry)
for team_entry in team_entries:
new_team_row = Bp_teams(**team_entry)
session_write.add(new_team_row)
#session_write.commit()
