Last active
October 5, 2023 10:38
-
-
Save balloob/dd05d872ac5b277c56e31d140b8d4d5c to your computer and use it in GitHub Desktop.
Jupyter Notebook connecting to Home Assistant
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
{ | |
"cells": [ | |
{ | |
"cell_type": "markdown", | |
"metadata": {}, | |
"source": [ | |
"### Setup" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": null, | |
"metadata": { | |
"collapsed": true, | |
"scrolled": true | |
}, | |
"outputs": [], | |
"source": [ | |
"# This requires Home Assistant 0.24+\n", | |
"# Your database url as specified in configuration.yaml\n", | |
"# If using default settings, it's sqlite:///<path to config dir>/home-assistant_v2.db\n", | |
"DB_URL = \"sqlite:///./home-assistant_v2.db\"" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": null, | |
"metadata": { | |
"collapsed": false, | |
"scrolled": true | |
}, | |
"outputs": [], | |
"source": [ | |
"from sqlalchemy import create_engine\n", | |
"from sqlalchemy.orm import scoped_session, sessionmaker\n", | |
"\n", | |
"from homeassistant.components.recorder.models import Base, Events, States, RecorderRuns\n", | |
"\n", | |
"engine = create_engine(DB_URL)\n", | |
"Base.metadata.create_all(engine)\n", | |
"session_factory = sessionmaker(bind=engine)\n", | |
"Session = scoped_session(session_factory)" | |
] | |
}, | |
{ | |
"cell_type": "markdown", | |
"metadata": {}, | |
"source": [ | |
"### Available entities and their total number of unique states" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": null, | |
"metadata": { | |
"collapsed": false, | |
"scrolled": false | |
}, | |
"outputs": [], | |
"source": [ | |
"list(engine.execute(\"SELECT entity_id, COUNT(*) FROM states GROUP BY entity_id\"))" | |
] | |
}, | |
{ | |
"cell_type": "markdown", | |
"metadata": {}, | |
"source": [ | |
"### Get available runs" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": null, | |
"metadata": { | |
"collapsed": false, | |
"scrolled": true | |
}, | |
"outputs": [], | |
"source": [ | |
"[str(r.start) for r in Session().query(RecorderRuns)]" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": null, | |
"metadata": { | |
"collapsed": true, | |
"scrolled": true | |
}, | |
"outputs": [], | |
"source": [ | |
"# Last finished run\n", | |
"last_run = (Session().query(RecorderRuns)\n", | |
" .filter(RecorderRuns.end != None)\n", | |
" .order_by(RecorderRuns.end.desc())\n", | |
" .first().to_native())" | |
] | |
}, | |
{ | |
"cell_type": "markdown", | |
"metadata": {}, | |
"source": [ | |
"### Show states at point in time" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": null, | |
"metadata": { | |
"collapsed": false, | |
"scrolled": false | |
}, | |
"outputs": [], | |
"source": [ | |
"from datetime import timedelta, datetime\n", | |
"from sqlalchemy import and_, func\n", | |
"\n", | |
"point_in_time = datetime(2016, 7, 4, 9, 0, 0)\n", | |
"\n", | |
"# Taken from homeassistant.components.history#get_states\n", | |
"most_recent_state_ids = (\n", | |
" Session().query(func.max(States.state_id).label('max_state_id'))\n", | |
" .filter(States.created < point_in_time).group_by(States.entity_id).subquery()\n", | |
")\n", | |
"\n", | |
"query = Session().query(States).join(\n", | |
" most_recent_state_ids, and_(\n", | |
" States.state_id == most_recent_state_ids.c.max_state_id))\n", | |
"\n", | |
"for state in query:\n", | |
" # Convert to HA object\n", | |
" state = state.to_native()\n", | |
" print(state.name, \":\", state.state, state.attributes.get('unit_of_measurement', ''))" | |
] | |
} | |
], | |
"metadata": { | |
"kernelspec": { | |
"display_name": "Python 3", | |
"language": "python", | |
"name": "python3" | |
}, | |
"language_info": { | |
"codemirror_mode": { | |
"name": "ipython", | |
"version": 3 | |
}, | |
"file_extension": ".py", | |
"mimetype": "text/x-python", | |
"name": "python", | |
"nbconvert_exporter": "python", | |
"pygments_lexer": "ipython3", | |
"version": "3.4.3" | |
} | |
}, | |
"nbformat": 4, | |
"nbformat_minor": 0 | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment