## Datebase Statistics Example¶

This notebooks shows a couple of basic examples about how to create statistics from details stored in your Home Assistant database.

### Setup Database connection¶

In [1]:
# Instead of SQLAlchemy is the built-in Python support for SQLite used.
import sqlite3

In [2]:
# Your database URL
DB_URL = "/home/PATH/.homeassistant/home-assistant_v2.db"

In [3]:
conn = sqlite3.connect(DB_URL)


Get all entities and their occurrence from your database. Limit to 10.

In [4]:
entities = conn.execute("SELECT entity_id, COUNT(*) as count FROM states "
"GROUP BY entity_id ORDER BY count DESC LIMIT 10")

In [5]:
entities.fetchall()

Out[5]:
[('sensor.max_sensor', 7978),
('binary_sensor.random_trend', 5086),
('sensor.random_sensor', 5052),
('sensor.random_sensor_2', 3160),
('binary_sensor.random_binary_sensor', 703),
('binary_sensor.iss', 530),
('sensor.b4d1_uptime', 528),
('sensor.b4d1_signal', 361),
('sun.sun', 171)]

Most SQL dialects support mathematical functions, especially for statistical analysis. For SQLite those are called Aggregate Functions.

In [6]:
stats = conn.execute("SELECT entity_id, COUNT(state) as count, MAX(state) as max, "
"MIN(state) as min, AVG(state) as mean FROM states "
"WHERE entity_id = 'sensor.random_sensor'")

In [7]:
for row in stats:
print("Entitiy ID : ", row[0])
print("count      : ", row[1])
print("max        : ", row[2])
print("min        : ", row[3])
print("mean       : ", row[4])

Entitiy ID :  sensor.random_sensor
count      :  5052
max        :  9
min        :  0
mean       :  9.877672209026128


Count the occurrence of a single values. Could be useful if you want to adjust your climate units.

In [8]:
stats1 = conn.execute("SELECT entity_id, COUNT(state) FROM states "
"WHERE entity_id = 'sensor.random_sensor' AND state = 6")

In [9]:
stats1.fetchall()

Out[9]:
[('sensor.random_sensor', 234)]

Show only values which exceeded a threshold.

In [10]:
stats2 = conn.execute("SELECT entity_id, COUNT(state) FROM states "
"WHERE entity_id = 'sensor.random_sensor' AND state > 5")

In [11]:
stats2.fetchall()

Out[11]:
[('sensor.random_sensor', 921)]

Determine the states of a binary sensor.

In [12]:
stats3 = conn.execute("SELECT state, COUNT(state) FROM states "
"WHERE entity_id = 'binary_sensor.movement' "
"AND state = 'off' OR state = 'on' GROUP BY state")

In [13]:
stats3.fetchall()

Out[13]:
[('on', 2883)]