Datebase query examples

This notebooks shows the two ways to access objects stored in your Home Assistant database.

Python's SQLite3 support

This access method is very simple and perfect if you want to have a quick look at your data.

In [1]:
import sqlite3

Create a connection to the database.

In [2]:
conn = sqlite3.connect('/path/to/.homeassistant/home-assistant_v2.db')

With a standard SQL query you will get access to the data.

In [3]:
data = conn.execute("SELECT state, last_changed FROM states WHERE entity_id = 'sensor.random_sensor' AND state != 'unknown'")

Now loop over the returned data.

In [4]:
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.

In [5]:
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker

from homeassistant.components.recorder.models import Base, Events, States, RecorderRuns
In [6]:
DB_URL = "sqlite:////path/to/.homeassistant/home-assistant_v2.db"
In [7]:
# Database setup
engine = create_engine(DB_URL)
Base.metadata.create_all(engine)
session_factory = sessionmaker(bind=engine)
Session = scoped_session(session_factory)

Available entities and their total number of unique states

In [8]:
list(engine.execute("SELECT state, last_changed FROM states WHERE entity_id = 'sensor.random_sensor' AND state != 'unknown'"))
Out[8]:
[('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')]

Get available runs

In [9]:
[str(row.start) for row in Session().query(RecorderRuns)]
Out[9]:
['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']
In [10]:
# Last finished run
last_run = (Session().query(RecorderRuns)
            .filter(RecorderRuns.end != None)
            .order_by(RecorderRuns.end.desc())
            .first().to_native())
In [11]:
print(last_run.end)
2018-12-29 22:50:56.243402

Show states at point in time

In [12]:
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