This notebooks shows the two ways to access objects stored in your Home Assistant database.
This access method is very simple and perfect if you want to have a quick look at your data.
import sqlite3
Create a connection to the database.
conn = sqlite3.connect('/path/to/.homeassistant/home-assistant_v2.db')
With a standard SQL query you will get access to the data.
data = conn.execute("SELECT state, last_changed FROM states WHERE entity_id = 'sensor.random_sensor' AND state != 'unknown'")
Now loop over the returned data.
for row in data:
print(row)
('5', '2018-12-29 22:35:23.002962') ('10', '2018-12-29 22:35:54.004050') ('11', '2018-12-29 22:36:25.003492') ('9', '2018-12-29 22:36:56.002128') ('10', '2018-12-29 22:37:26.003112') ('3', '2018-12-29 22:37:57.003212') ('7', '2018-12-29 22:38:28.003530') ('0', '2018-12-29 22:40:24.706704') ('13', '2018-12-29 22:40:55.003484') ('2', '2018-12-29 22:41:26.002753') ('16', '2018-12-29 22:41:57.002935') ('0', '2018-12-29 22:42:28.002090') ('13', '2018-12-29 22:42:58.003726') ('15', '2018-12-29 22:43:29.003645') ('10', '2018-12-29 22:44:00.003698') ('16', '2018-12-29 22:44:31.005287') ('9', '2018-12-29 22:45:02.003470') ('4', '2018-12-29 22:46:46.708584') ('18', '2018-12-29 22:47:17.004101') ('13', '2018-12-29 22:47:48.002298') ('6', '2018-12-29 22:48:18.002954') ('2', '2018-12-29 22:48:38.645046') ('0', '2018-12-29 22:49:09.002633') ('15', '2018-12-29 22:49:39.004924') ('4', '2018-12-29 22:50:10.004746') ('1', '2018-12-29 22:50:27.419122')
sqlalchemy
¶SQLAlchemy is a SQL toolkit and object-relational mapper.
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
from homeassistant.components.recorder.models import Base, Events, States, RecorderRuns
DB_URL = "sqlite:////path/to/.homeassistant/home-assistant_v2.db"
# Database setup
engine = create_engine(DB_URL)
Base.metadata.create_all(engine)
session_factory = sessionmaker(bind=engine)
Session = scoped_session(session_factory)
list(engine.execute("SELECT state, last_changed FROM states WHERE entity_id = 'sensor.random_sensor' AND state != 'unknown'"))
[('5', '2018-12-29 22:35:23.002962'), ('10', '2018-12-29 22:35:54.004050'), ('11', '2018-12-29 22:36:25.003492'), ('9', '2018-12-29 22:36:56.002128'), ('10', '2018-12-29 22:37:26.003112'), ('3', '2018-12-29 22:37:57.003212'), ('7', '2018-12-29 22:38:28.003530'), ('0', '2018-12-29 22:40:24.706704'), ('13', '2018-12-29 22:40:55.003484'), ('2', '2018-12-29 22:41:26.002753'), ('16', '2018-12-29 22:41:57.002935'), ('0', '2018-12-29 22:42:28.002090'), ('13', '2018-12-29 22:42:58.003726'), ('15', '2018-12-29 22:43:29.003645'), ('10', '2018-12-29 22:44:00.003698'), ('16', '2018-12-29 22:44:31.005287'), ('9', '2018-12-29 22:45:02.003470'), ('4', '2018-12-29 22:46:46.708584'), ('18', '2018-12-29 22:47:17.004101'), ('13', '2018-12-29 22:47:48.002298'), ('6', '2018-12-29 22:48:18.002954'), ('2', '2018-12-29 22:48:38.645046'), ('0', '2018-12-29 22:49:09.002633'), ('15', '2018-12-29 22:49:39.004924'), ('4', '2018-12-29 22:50:10.004746'), ('1', '2018-12-29 22:50:27.419122')]
[str(row.start) for row in Session().query(RecorderRuns)]
['2018-12-29 22:35:21.556017', '2018-12-29 22:40:23.336551', '2018-12-29 22:46:45.356893', '2018-12-29 22:48:37.321686', '2018-12-29 22:50:26.139649']
# Last finished run
last_run = (Session().query(RecorderRuns)
.filter(RecorderRuns.end != None)
.order_by(RecorderRuns.end.desc())
.first().to_native())
print(last_run.end)
2018-12-29 22:50:56.243402
from datetime import timedelta, datetime
from sqlalchemy import and_, func
point_in_time = datetime(2018, 12, 29, 23, 40, 0)
# Taken from homeassistant.components.history#get_states
most_recent_state_ids = (
Session().query(func.max(States.state_id).label('max_state_id'))
.filter(States.created < point_in_time).group_by(States.entity_id).subquery()
)
query = Session().query(States).join(
most_recent_state_ids, and_(
States.state_id == most_recent_state_ids.c.max_state_id))
for state in query:
# Convert to a Home Assistant object
state = state.to_native()
print(state.name, ":", state.state, state.attributes.get('unit_of_measurement', ''))
Demo Air Pollutants Home : 14 Demo Air Pollutants Office : 4 Heizung Wohnzimmer AUS : on Heizung Wohnzimmer EIN : on Random Binary Sensor : on Bärenplatz : idle Camera Proxy - camera.barenplatz : idle all automations : on Random Sensor : 1 Sun : below_horizon