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 _insert_into_db(actor, task, claim, auto, teleop, finish,
optionString):
select = text(
"INSERT INTO games (actor, task, claim, auto, teleop, finish) "
"VALUES (:actor,:task,:claim,:auto,:teleop,:finish) "
"ON CONFLICT (task) "
"DO UPDATE "
"SET actor=:actor, task=:task, claim=:claim, auto=:auto, "
"teleop=:teleop, finish=:finish;")
conn = server.model.connection.engine.connect()
conn.execute(select,
actor=actor,
task=task,
claim=claim,
auto=auto,
teleop=teleop,
finish=finish)
conn.close()
server.model.upsert.upsert("tasks", "name", task)
if optionString.strip():
optionNames = optionString.split('|')
for optionName in optionNames:
upsert_cols(
"task_options", {
'task_name': task,
'type': 'capability',
'option_name': optionName.strip()
})
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_cols(testdb_empty_tables):
assert testdb_empty_tables
util.verify_testdb()
conn = smc.engine.connect()
smu.upsert_cols("task_options", {
"task_name": "na",
"type": "capability",
"option_name": "na"
})
sql = "SELECT * FROM task_options;"
tasks = pandas.read_sql_query(sql, conn)
assert tasks.task_name[0] == "na"
assert tasks.type[0] == "capability"
assert tasks.option_name[0] == "na"
assert tasks.shape == (1, 4)
# Teardown
delete_all_rows("task_options", conn)
conn.close()
def test_upsert_cols():
smu.upsert_cols("events", {"name": "upsert_test", "season": "test"})
conn = smc.pool.getconn()
curr = conn.cursor()
sql = '''
SELECT name, season FROM events
WHERE name = 'upsert_test';
'''
curr.execute(sql)
assert curr.fetchone()[1] == 'test'
# Delete test data
sql = '''
DELETE FROM events
WHERE name = 'upsert_test';
'''
curr.execute(sql)
conn.commit()
sql = '''
SELECT COUNT(*) FROM events
WHERE name = 'upsert_test';
'''
curr.execute(sql)
assert curr.fetchone()[0] == 0
# Teardown
smc.pool.putconn(conn)
#
# def delete_all_rows(table, conn):
# print("Deleting table " + table)
# sql_delete = "DELETE FROM " + table + ";"
# conn.execute(sql_delete)