This notebooks shows the dafault way to access object stored in your Home Assistant database.
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
from homeassistant.components.recorder.models import Base, Events, States, RecorderRuns
# This requires Home Assistant 0.24+
# Your database URL as specified in configuration.yaml
# If using default settings, it's sqlite:///<path to config dir>/home-assistant_v2.db
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 entity_id, COUNT(*) FROM states GROUP BY entity_id"))
[('binary_sensor.bathroom_door', 179), ('binary_sensor.movement', 38), ('gpsd.gps', 771), ('gpsd.gpsd', 1), ('group.all_lights', 226), ('group.all_locks', 537), ('group.all_switches', 1689), ('group.ha_host', 179), ('group.info', 179), ('group.kitchen', 179), ('group.livingroom', 179), ('group.livingroom1', 179), ('group.office', 179), ('group.time', 179), ('input_boolean.control_switch', 41), ('input_boolean.notify_home', 182), ('input_slider.brightness', 187), ('input_slider.darkness', 179), ('light.1afe34d535e4', 69), ('lock.front_gate', 179), ('sensor.bathroom111_temperature', 2), ('sensor.battery_tablet', 210), ('sensor.control_input', 49), ('sensor.cpu', 7705), ('sensor.cpu_temp', 2119), ('sensor.danotage', 2), ('sensor.date', 186), ('sensor.discharge', 50), ('sensor.glances11', 100), ('sensor.ha_release', 130), ('sensor.internet_time', 3600), ('sensor.kitchen_humidity', 179), ('sensor.kitchen_temperature', 205), ('sensor.last', 112), ('sensor.last_seen_local', 53), ('sensor.last_seen_utc', 53), ('sensor.living_room_humidity', 179), ('sensor.living_room_temperature', 179), ('sensor.micropython', 433), ('sensor.new_york', 5148), ('sensor.process_glances', 51), ('sensor.rest_tester111', 90), ('sensor.slider_input', 47), ('sensor.sun_state', 85), ('sensor.test', 50), ('sensor.thijshs', 2), ('sensor.time', 5148), ('sensor.weather_condition', 604), ('sensor.weather_humidity', 875), ('sensor.weather_temperature', 1242), ('sensor.weather_wind_speed', 870), ('sensor.yr_symbol', 2), ('sun.sun', 5148), ('switch.arest_pin4', 178), ('switch.mystrom_switch', 4803), ('switch.power_office', 178), ('switch.pump_balcony', 178), ('updater.updater', 1), ('zone.home', 7)]
[str(r.start) for r in Session().query(RecorderRuns)]
['2016-07-15 09:12:19.592055', '2016-07-15 09:26:00.942845', '2016-07-15 09:32:06.724139', '2016-07-15 09:33:25.235434', '2016-07-15 09:47:25.311636', '2016-07-15 10:01:51.460298', '2016-07-15 10:05:25.841796', '2016-07-15 10:08:06.893725', '2016-07-15 14:06:26.388940', '2016-07-15 14:31:19.874527', '2016-07-15 14:34:38.110095', '2016-07-15 14:36:04.907501', '2016-07-15 14:37:19.463373', '2016-07-15 14:39:47.683495', '2016-07-15 14:40:42.347384', '2016-07-15 14:49:06.979299', '2016-07-15 14:53:05.294371', '2016-07-15 14:57:24.514303', '2016-07-15 14:59:54.028440', '2016-07-15 15:02:06.632085', '2016-07-15 15:03:01.883560', '2016-07-15 15:05:46.992554', '2016-07-15 16:03:40.601593', '2016-07-16 07:48:47.391476', '2016-07-16 08:17:47.066920', '2016-07-16 08:36:29.846933', '2016-07-16 08:38:47.758929', '2016-07-16 08:41:24.527377', '2016-07-16 08:43:59.797913', '2016-07-16 08:45:19.766744', '2016-07-16 08:46:04.050954', '2016-07-16 08:49:14.389891', '2016-07-16 08:49:48.306489', '2016-07-16 08:51:17.886930', '2016-07-16 08:51:43.822985', '2016-07-16 13:10:07.441796', '2016-07-16 13:19:26.693310', '2016-07-16 13:25:19.006626', '2016-07-16 13:43:50.079915', '2016-07-16 13:44:32.158061', '2016-07-16 13:48:50.639567', '2016-07-16 14:01:24.168251', '2016-07-16 14:03:34.691260', '2016-07-16 19:52:56.692469', '2016-07-16 21:31:18.802434', '2016-07-16 21:32:32.054834', '2016-07-16 21:33:56.894174', '2016-07-17 08:26:35.992366', '2016-07-17 08:27:52.706757', '2016-07-17 08:29:49.703058', '2016-07-17 08:39:44.864845', '2016-07-17 09:03:38.893645', '2016-07-17 10:02:19.533601', '2016-07-17 10:06:56.203657', '2016-07-17 10:25:09.318122', '2016-07-17 10:29:20.929219', '2016-07-17 10:38:04.173924', '2016-07-17 10:44:33.182488', '2016-07-17 11:14:55.113328', '2016-07-17 11:17:10.133880', '2016-07-17 11:17:30.342145', '2016-07-17 11:18:43.166345', '2016-07-17 11:31:12.800384', '2016-07-17 11:35:40.378574', '2016-07-17 11:38:28.902428', '2016-07-17 11:40:29.525891', '2016-07-17 11:44:13.936933', '2016-07-17 11:45:43.415567', '2016-07-17 11:49:21.395742', '2016-07-17 11:51:38.597659', '2016-07-17 11:59:28.353353', '2016-07-17 13:03:54.254045', '2016-07-17 13:38:45.387625', '2016-07-17 13:41:24.736000', '2016-07-17 13:53:58.307882', '2016-07-17 14:18:53.863674', '2016-07-17 14:22:58.805773', '2016-07-17 14:40:14.798286', '2016-07-17 14:43:36.235049', '2016-07-17 14:46:02.511999', '2016-07-17 15:20:19.588012', '2016-07-17 15:21:15.972852', '2016-07-17 15:53:21.886815', '2016-07-17 17:46:33.523057', '2016-07-17 17:48:19.753656', '2016-07-17 17:53:11.073067', '2016-07-17 17:54:06.863027', '2016-07-17 17:54:49.879645', '2016-07-17 18:05:16.310203', '2016-07-17 18:15:30.482549', '2016-07-17 19:08:49.619108', '2016-07-17 19:09:14.151947', '2016-07-17 19:37:46.118793', '2016-07-17 19:50:03.648934', '2016-07-17 19:53:47.259629', '2016-07-17 20:53:45.427076', '2016-07-17 20:54:19.049869', '2016-07-17 20:54:39.046062', '2016-07-17 20:57:31.475201', '2016-07-17 20:57:46.570265', '2016-07-17 20:59:51.518826', '2016-07-17 21:03:13.472660', '2016-07-17 21:04:54.258101', '2016-07-17 21:05:36.382183', '2016-07-17 21:07:29.286547', '2016-07-17 21:10:36.910250', '2016-07-18 06:26:03.797719', '2016-07-18 06:28:19.827089', '2016-07-18 06:33:08.540169', '2016-07-18 06:38:23.186904', '2016-07-18 08:16:05.934485', '2016-07-18 08:17:20.474851', '2016-07-18 08:23:19.435717', '2016-07-18 08:27:14.615281', '2016-07-18 08:36:51.974687', '2016-07-18 08:39:17.531915', '2016-07-18 09:05:53.487540', '2016-07-18 09:09:41.849102', '2016-07-18 19:01:17.501129', '2016-07-18 19:01:44.295859', '2016-07-18 19:03:57.611960', '2016-07-18 19:07:35.077555', '2016-07-18 19:32:03.385832', '2016-07-18 20:16:28.162782', '2016-07-19 10:42:46.338881', '2016-07-20 14:22:07.454265', '2016-07-20 16:11:36.912270', '2016-07-21 14:33:16.276039', '2016-07-21 15:52:52.575817', '2016-07-21 15:56:13.499626', '2016-07-21 15:57:34.487916', '2016-07-21 15:59:55.361077', '2016-07-21 16:20:09.964689', '2016-07-21 16:23:49.454156', '2016-07-21 16:24:53.691256', '2016-07-21 16:27:37.083485', '2016-07-21 16:28:46.897103', '2016-07-21 16:30:00.473435', '2016-07-21 16:33:24.151699', '2016-07-21 16:34:34.745454', '2016-07-21 16:37:33.761637', '2016-07-21 16:44:51.818372', '2016-07-21 16:46:55.239923', '2016-07-21 16:50:01.544369', '2016-07-21 16:57:40.987725', '2016-07-21 17:05:26.945221', '2016-07-21 17:10:27.834649', '2016-07-21 17:11:48.665692', '2016-07-21 19:15:17.809677', '2016-07-21 20:18:28.629622', '2016-07-22 09:12:18.369204', '2016-07-22 09:33:20.848032', '2016-07-22 09:55:03.634299', '2016-07-22 09:56:44.474854', '2016-07-22 11:52:40.369937', '2016-07-22 12:39:06.699739', '2016-07-22 14:23:40.573965', '2016-07-22 14:43:33.261506', '2016-07-22 15:01:06.569349', '2016-07-22 21:12:09.086164', '2016-07-22 21:14:19.160191', '2016-07-22 21:15:50.192180', '2016-07-22 21:17:36.040389', '2016-07-22 21:21:57.886842', '2016-07-22 21:33:21.648959', '2016-07-22 21:33:57.104399', '2016-07-23 07:55:56.462018', '2016-07-23 09:18:55.486046', '2016-07-23 09:20:01.825112', '2016-07-23 09:31:55.651858', '2016-07-23 09:34:14.404590', '2016-07-23 09:51:09.838073', '2016-07-23 11:09:35.738452', '2016-07-23 14:18:40.345240', '2016-07-23 14:19:23.231642', '2016-07-23 14:20:04.680489', '2016-07-23 14:21:34.631639', '2016-07-23 14:24:06.431376', '2016-07-23 21:29:57.559603', '2016-07-23 21:44:09.127505', '2016-07-23 21:54:45.577710', '2016-07-24 12:08:12.816935']
# Last finished run
last_run = (Session().query(RecorderRuns)
.filter(RecorderRuns.end != None)
.order_by(RecorderRuns.end.desc())
.first().to_native())
from datetime import timedelta, datetime
from sqlalchemy import and_, func
point_in_time = datetime(2016, 7, 4, 9, 0, 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 HA object
state = state.to_native()
print(state.name, ":", state.state, state.attributes.get('unit_of_measurement', ''))