Insert data from Gadget Bridge into Home Assistant database

gadgetbridge_homeassistant.py

import sqlite3

# Get this from the DB after creating the sensor/helper
METADATA_ID = 500  # input_number.heart_rate

connection_gadget = sqlite3.connect("samba/public/Gadgetbridge")
cursor_gadget = connection_gadget.cursor()

connection_hass = sqlite3.connect("homeassistant/config/home-assistant_v2.db")
cursor_hass = connection_hass.cursor()

heart_rates = cursor_gadget.execute("SELECT * FROM COLMI_HEART_RATE_SAMPLE").fetchall()

for heart_rate in heart_rates:
    (old_state_id, attributes_id, origin_idx) = cursor_hass.execute("SELECT max(state_id), attributes_id, origin_idx FROM states WHERE metadata_id = ?", [METADATA_ID]).fetchone()

    data = (
        heart_rate[3],  # HEAR_RATE
        int(heart_rate[0]) / 1000,  # TIMESTAMP  (it's in milliseconds and we need it in seconds)
        old_state_id,
        attributes_id,
        origin_idx,
        METADATA_ID,
    )

    print("Inserting", data)
    cursor_hass.execute("INSERT INTO states (state, last_updated_ts, old_state_id, attributes_id, origin_idx, metadata_id) values (?, ?, ?, ?, ?, ?)", data)
    connection_hass.commit()

connection_gadget.close()
connection_hass.close()