def fill_devices_table(**kwargs):
ti = kwargs['ti']
devices = ti.xcom_pull(task_ids='get_devices_dates')
conn_host = SqliteHook(sqlite_conn_id='sqlite_devices').get_conn()
for device in devices:
sql_insert = f"""INSERT OR REPLACE INTO {DEVICES_TABLE}
(device, last_reading, latest_postprocessing)
VALUES ({device},
'{devices[device]["last_reading"]}',
'{devices[device]["latest_postprocessing"]}'
)
;"""
conn_host.execute(sql_insert)
conn_host.commit()
conn_host.close()
def save_model_accuracy(**kwargs):
# Tasks can pass parameters to downstream tasks through the XCom space.
# XCom (cross-communication) allows communication between task instances.
# In this example the current task `save_model_accuracy` takes the output
# of the previous task `measure_accuracy` "pulling" it from the XCom space
ti = kwargs['ti']
accuracy = ti.xcom_pull(task_ids='measure_accuracy')
sql_insert = f"""INSERT INTO {TRAINING_TABLE}
(mape_test, rmse_test, days_in_test)
VALUES({accuracy['mape_test']},
{accuracy['rmse_test']},
{accuracy['days_in_test']})
;
"""
# Hooks are interface to external platforms (e.g. Amazon S3)
# and DBs (e.g. SQLite DB, PostgreSQL)
conn_host = SqliteHook(sqlite_conn_id='sqlite_ml').get_conn()
conn_host.execute(sql_insert)
conn_host.commit()
def save_prediction(**kwargs):
# Tasks can pass parameters to downstream tasks through the XCom space.
# In this example the current task `save_prediction` takes the output of
# the previous task `run_prediction` "pulling" it from the XCom space
ti = kwargs['ti']
prediction_dict = ti.xcom_pull(task_ids='run_prediction')
# INSERT OR REPLACE guarantees to have unique row for each date_to_predict:
# it inserts a new prediction if it doesn't exists or replace the existing
# one due to the index idx_date_to_predict on date_to_predict
sql_insert = f"""INSERT OR REPLACE INTO {PREDICTION_TABLE}
(date_to_predict, run_date, yhat, yhat_upper, yhat_lower)
VALUES ('{prediction_dict["date_to_predict"]}',
'{prediction_dict["run_date"]}',
{prediction_dict["yhat"]},
{prediction_dict["yhat_upper"]},
{prediction_dict["yhat_lower"]}
)
;"""
# Hooks are interface to external platforms (e.g. Amazon S3)
# and DBs (e.g. SQLite DB, PostgreSQL)
conn_host = SqliteHook(sqlite_conn_id='sqlite_ml').get_conn()
conn_host.execute(sql_insert)
conn_host.commit()
conn_host.close()