This notebooks shows a couple of basic examples about how to create statistics from details stored in your Home Assistant database.
# Instead of SQLAlchemy is the built-in Python support for SQLite used.
import sqlite3
# Your database URL
DB_URL = "/home/PATH/.homeassistant/home-assistant_v2.db"
conn = sqlite3.connect(DB_URL)
Get all entities and their occurrence from your database. Limit to 10.
entities = conn.execute("SELECT entity_id, COUNT(*) as count FROM states "
"GROUP BY entity_id ORDER BY count DESC LIMIT 10")
entities.fetchall()
[('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)]
Most SQL dialects support mathematical functions, especially for statistical analysis. For SQLite those are called Aggregate Functions.
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'")
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.
stats1 = conn.execute("SELECT entity_id, COUNT(state) FROM states "
"WHERE entity_id = 'sensor.random_sensor' AND state = 6")
stats1.fetchall()
[('sensor.random_sensor', 234)]
Show only values which exceeded a threshold.
stats2 = conn.execute("SELECT entity_id, COUNT(state) FROM states "
"WHERE entity_id = 'sensor.random_sensor' AND state > 5")
stats2.fetchall()
[('sensor.random_sensor', 921)]
Determine the states of a binary sensor.
stats3 = conn.execute("SELECT state, COUNT(state) FROM states "
"WHERE entity_id = 'binary_sensor.movement' "
"AND state = 'off' OR state = 'on' GROUP BY state")
stats3.fetchall()
[('on', 2883)]