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)

Details about your entities

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),
 ('sensor.netdata_load', 658),
 ('binary_sensor.iss', 530),
 ('sensor.b4d1_uptime', 528),
 ('sensor.b4d1_signal', 361),
 ('sun.sun', 171)]

Details about one entities

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)]