def process_sched(event, season, sched_json, level='qual'):
sched = json.loads(sched_json)['Schedule']
smu.upsert_cols("events", {"name": event, "season": season})
event_id = sm_event.EventDal.get_event_id(event, season)
select = text(
"INSERT INTO schedules (event_id, match, team, level, date, "
"alliance, station) " +
"VALUES (:evt_id,'na','na','na','na','na','na'); "
)
conn = smc.engine.connect()
conn.execute(select, evt_id=event_id)
conn.close()
for mch in sched:
match = "{0:0>3}-q".format(mch['matchNumber'])
date = mch['startTime']
for tm in mch['teams']:
team = tm['teamNumber']
station = tm['station'][-1:]
alliance = tm['station'][0:-1].lower()
select = text(
"INSERT INTO schedules (event_id, match, team, level, "
"date, alliance, station) " +
"VALUES (:evt_id,:match,:team,:level,:date,:alliance,:station);"
)
conn = smc.engine.connect()
conn.execute(select, evt_id=event_id, match=match, team=team,
level=level, date=date, alliance=alliance,
station=station)
conn.close()
# smu.upsert("events", "name", event)
smu.upsert("teams", "name", team)
smu.upsert("dates", "name", date)
set_teams()
def process_sched(event, season, sched_json, level='qual'):
sched = json.loads(sched_json)['Schedule']
smu.upsert_cols("events", {"name": event, "season": season})
event_id = sm_event.EventDal.get_event_id(event, season)
select = text("INSERT INTO schedules (event_id, match, team, level, date, "
"alliance, station) " +
"VALUES (:evt_id,'na','na','na','na','na','na'); ")
conn = smc.engine.connect()
conn.execute(select, evt_id=event_id)
conn.close()
for mch in sched:
match = "{0:0>3}-q".format(mch['matchNumber'])
date = mch['startTime']
for tm in mch['teams']:
team = tm['teamNumber']
station = tm['station'][-1:]
alliance = tm['station'][0:-1].lower()
select = text(
"INSERT INTO schedules (event_id, match, team, level, "
"date, alliance, station) " +
"VALUES (:evt_id,:match,:team,:level,:date,:alliance,:station);"
)
conn = smc.engine.connect()
conn.execute(select,
evt_id=event_id,
match=match,
team=team,
level=level,
date=date,
alliance=alliance,
station=station)
conn.close()
# smu.upsert("events", "name", event)
smu.upsert("teams", "name", team)
smu.upsert("dates", "name", date)
# set_teams()
url = (f"https://frc-api.firstinspires.org/v2.0/{season}"
f"/teams?eventCode={event}")
teams_json = smf._send_http_request(url)
teams = pandas.DataFrame(json.loads(teams_json)['teams'])
conn = smc.engine.connect()
for row in teams.itertuples():
sql = text(
"UPDATE teams SET long_name = :long_name WHERE name = :name;"
).bindparams(long_name=row.nameShort, name=str(row.teamNumber))
try:
conn.execute(sql)
except ValueError:
pass
except:
pass
conn.close()
def test_upsert(testdb_empty_tables):
assert testdb_empty_tables
util.verify_testdb()
conn = smc.engine.connect()
smu.upsert("actors", "name", "upsert_test1")
smu.upsert("actors", "name", "upsert_test2")
sql_count = sqlalchemy.text("SELECT COUNT(*) FROM actors;")
count = conn.execute(sql_count).scalar()
assert count == 2
sql_sel = "SELECT * FROM actors;"
actors = pandas.read_sql_query(sql_sel, conn)
assert actors.shape == (2, 2)
assert actors.name[0] == "upsert_test1"
# Teardown
delete_all_rows("actors", conn)
conn.close()
def test_upsert():
'''Verifies upsert function inserts data into database
Also verifies upsert() does not error when reinserting the same
data.
'''
# Run upsert commands
smu.upsert("actors", "name", "upsert_test1")
smu.upsert("actors", "name", "upsert_test2")
# Verify two records in database
conn = smc.pool.getconn()
curr = conn.cursor()
sql = r'''
SELECT COUNT(*) FROM actors
WHERE name LIKE 'upsert\_test%';
'''
curr.execute(sql)
assert curr.fetchone()[0] == 2
# Run upsert commands again to ensure no errors if inserting
# data that already exists
smu.upsert("actors", "name", "upsert_test1")
smu.upsert("actors", "name", "upsert_test2")
# Delete the test data that was just entered
sql = r'''
DELETE FROM actors
WHERE name LIKE 'upsert\_test%';
'''
curr.execute(sql)
conn.commit()
# Verify test data was deleted
sql = r'''
SELECT COUNT(*) FROM actors
WHERE name LIKE 'upsert\_test%';
'''
curr.execute(sql)
assert curr.fetchone()[0] == 0
# Release connection
curr.close()
smc.pool.putconn(conn)
def initialize_dimension_data():
"""Loads initial dimension data into database.
The dimension data loaded by this function is essential for the
scouting system to operate and must be loaded before recording
any competition data (but after creating the tables with
`create_tables()`.
"""
upsert("levels", "name", "na")
upsert("levels", "name", "qual")
upsert("levels", "name", "playoff")
upsert_rows("matches", "name", 150, "{0:0>3}-q")
upsert("matches", "name", "na")
upsert("matches", "name", "q1.1")
upsert("matches", "name", "q1.2")
upsert("matches", "name", "q1.3")
upsert("matches", "name", "q2.1")
upsert("matches", "name", "q2.2")
upsert("matches", "name", "q2.3")
upsert("matches", "name", "q3.1")
upsert("matches", "name", "q3.2")
upsert("matches", "name", "q3.3")
upsert("matches", "name", "s1.1")
upsert("matches", "name", "s1.2")
upsert("matches", "name", "s1.3")
upsert("matches", "name", "s2.1")
upsert("matches", "name", "s2.2")
upsert("matches", "name", "s2.3")
upsert("matches", "name", "f1")
upsert("matches", "name", "f2")
upsert("matches", "name", "f3")
upsert("alliances", "name", "na")
upsert("alliances", "name", "blue")
upsert("alliances", "name", "red")
upsert("dates", "name", "na")
# teams imported from schedule
upsert("teams", "name", 'na')
upsert("stations", "name", "na")
upsert("stations", "name", "1")
upsert("stations", "name", "2")
upsert("stations", "name", "3")
upsert("actors", "name", "na")
upsert("actors", "name", "drive_team")
upsert("actors", "name", "robot")
upsert("actors", "name", "pilot")
upsert("actors", "name", "human_player")
upsert("actors", "name", "alliance")
upsert("actors", "name", "team")
upsert("actors", "name", "field")
# tasks imported from game
upsert("tasks", "name", 'na')
upsert("measuretypes", "name", "na")
upsert("measuretypes", "name", "count")
upsert("measuretypes", "name", "percentage")
upsert("measuretypes", "name", "boolean")
upsert("measuretypes", "name", "enum")
upsert("measuretypes", "name", "attempt")
upsert("measuretypes", "name", "cycletime")
upsert("measuretypes", "name", "rating")
upsert("phases", "name", "na")
upsert("phases", "name", "claim")
upsert("phases", "name", "auto")
upsert("phases", "name", "teleop")
upsert("phases", "name", "finish")
upsert("attempts", "name", "summary")
upsert_rows("attempts", "name", 31, "{0:0>2}")
upsert("reasons", "name", "na")
upsert("reasons", "name", "dropped")
upsert("reasons", "name", "blocked")
upsert("reasons", "name", "defended")