{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Datebase query example\n",
    "This notebooks shows the dafault way to access object stored in your Home Assistant database."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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:////path/to/.homeassistant/home-assistant_v2.db\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Database setup\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": 25,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('binary_sensor.bathroom_door', 179),\n",
       " ('binary_sensor.movement', 38),\n",
       " ('gpsd.gps', 771),\n",
       " ('gpsd.gpsd', 1),\n",
       " ('group.all_lights', 226),\n",
       " ('group.all_locks', 537),\n",
       " ('group.all_switches', 1689),\n",
       " ('group.ha_host', 179),\n",
       " ('group.info', 179),\n",
       " ('group.kitchen', 179),\n",
       " ('group.livingroom', 179),\n",
       " ('group.livingroom1', 179),\n",
       " ('group.office', 179),\n",
       " ('group.time', 179),\n",
       " ('input_boolean.control_switch', 41),\n",
       " ('input_boolean.notify_home', 182),\n",
       " ('input_slider.brightness', 187),\n",
       " ('input_slider.darkness', 179),\n",
       " ('light.1afe34d535e4', 69),\n",
       " ('lock.front_gate', 179),\n",
       " ('sensor.bathroom111_temperature', 2),\n",
       " ('sensor.battery_tablet', 210),\n",
       " ('sensor.control_input', 49),\n",
       " ('sensor.cpu', 7705),\n",
       " ('sensor.cpu_temp', 2119),\n",
       " ('sensor.danotage', 2),\n",
       " ('sensor.date', 186),\n",
       " ('sensor.discharge', 50),\n",
       " ('sensor.glances11', 100),\n",
       " ('sensor.ha_release', 130),\n",
       " ('sensor.internet_time', 3600),\n",
       " ('sensor.kitchen_humidity', 179),\n",
       " ('sensor.kitchen_temperature', 205),\n",
       " ('sensor.last', 112),\n",
       " ('sensor.last_seen_local', 53),\n",
       " ('sensor.last_seen_utc', 53),\n",
       " ('sensor.living_room_humidity', 179),\n",
       " ('sensor.living_room_temperature', 179),\n",
       " ('sensor.micropython', 433),\n",
       " ('sensor.new_york', 5148),\n",
       " ('sensor.process_glances', 51),\n",
       " ('sensor.rest_tester111', 90),\n",
       " ('sensor.slider_input', 47),\n",
       " ('sensor.sun_state', 85),\n",
       " ('sensor.test', 50),\n",
       " ('sensor.thijshs', 2),\n",
       " ('sensor.time', 5148),\n",
       " ('sensor.weather_condition', 604),\n",
       " ('sensor.weather_humidity', 875),\n",
       " ('sensor.weather_temperature', 1242),\n",
       " ('sensor.weather_wind_speed', 870),\n",
       " ('sensor.yr_symbol', 2),\n",
       " ('sun.sun', 5148),\n",
       " ('switch.arest_pin4', 178),\n",
       " ('switch.mystrom_switch', 4803),\n",
       " ('switch.power_office', 178),\n",
       " ('switch.pump_balcony', 178),\n",
       " ('updater.updater', 1),\n",
       " ('zone.home', 7)]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "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": 26,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['2016-07-15 09:12:19.592055',\n",
       " '2016-07-15 09:26:00.942845',\n",
       " '2016-07-15 09:32:06.724139',\n",
       " '2016-07-15 09:33:25.235434',\n",
       " '2016-07-15 09:47:25.311636',\n",
       " '2016-07-15 10:01:51.460298',\n",
       " '2016-07-15 10:05:25.841796',\n",
       " '2016-07-15 10:08:06.893725',\n",
       " '2016-07-15 14:06:26.388940',\n",
       " '2016-07-15 14:31:19.874527',\n",
       " '2016-07-15 14:34:38.110095',\n",
       " '2016-07-15 14:36:04.907501',\n",
       " '2016-07-15 14:37:19.463373',\n",
       " '2016-07-15 14:39:47.683495',\n",
       " '2016-07-15 14:40:42.347384',\n",
       " '2016-07-15 14:49:06.979299',\n",
       " '2016-07-15 14:53:05.294371',\n",
       " '2016-07-15 14:57:24.514303',\n",
       " '2016-07-15 14:59:54.028440',\n",
       " '2016-07-15 15:02:06.632085',\n",
       " '2016-07-15 15:03:01.883560',\n",
       " '2016-07-15 15:05:46.992554',\n",
       " '2016-07-15 16:03:40.601593',\n",
       " '2016-07-16 07:48:47.391476',\n",
       " '2016-07-16 08:17:47.066920',\n",
       " '2016-07-16 08:36:29.846933',\n",
       " '2016-07-16 08:38:47.758929',\n",
       " '2016-07-16 08:41:24.527377',\n",
       " '2016-07-16 08:43:59.797913',\n",
       " '2016-07-16 08:45:19.766744',\n",
       " '2016-07-16 08:46:04.050954',\n",
       " '2016-07-16 08:49:14.389891',\n",
       " '2016-07-16 08:49:48.306489',\n",
       " '2016-07-16 08:51:17.886930',\n",
       " '2016-07-16 08:51:43.822985',\n",
       " '2016-07-16 13:10:07.441796',\n",
       " '2016-07-16 13:19:26.693310',\n",
       " '2016-07-16 13:25:19.006626',\n",
       " '2016-07-16 13:43:50.079915',\n",
       " '2016-07-16 13:44:32.158061',\n",
       " '2016-07-16 13:48:50.639567',\n",
       " '2016-07-16 14:01:24.168251',\n",
       " '2016-07-16 14:03:34.691260',\n",
       " '2016-07-16 19:52:56.692469',\n",
       " '2016-07-16 21:31:18.802434',\n",
       " '2016-07-16 21:32:32.054834',\n",
       " '2016-07-16 21:33:56.894174',\n",
       " '2016-07-17 08:26:35.992366',\n",
       " '2016-07-17 08:27:52.706757',\n",
       " '2016-07-17 08:29:49.703058',\n",
       " '2016-07-17 08:39:44.864845',\n",
       " '2016-07-17 09:03:38.893645',\n",
       " '2016-07-17 10:02:19.533601',\n",
       " '2016-07-17 10:06:56.203657',\n",
       " '2016-07-17 10:25:09.318122',\n",
       " '2016-07-17 10:29:20.929219',\n",
       " '2016-07-17 10:38:04.173924',\n",
       " '2016-07-17 10:44:33.182488',\n",
       " '2016-07-17 11:14:55.113328',\n",
       " '2016-07-17 11:17:10.133880',\n",
       " '2016-07-17 11:17:30.342145',\n",
       " '2016-07-17 11:18:43.166345',\n",
       " '2016-07-17 11:31:12.800384',\n",
       " '2016-07-17 11:35:40.378574',\n",
       " '2016-07-17 11:38:28.902428',\n",
       " '2016-07-17 11:40:29.525891',\n",
       " '2016-07-17 11:44:13.936933',\n",
       " '2016-07-17 11:45:43.415567',\n",
       " '2016-07-17 11:49:21.395742',\n",
       " '2016-07-17 11:51:38.597659',\n",
       " '2016-07-17 11:59:28.353353',\n",
       " '2016-07-17 13:03:54.254045',\n",
       " '2016-07-17 13:38:45.387625',\n",
       " '2016-07-17 13:41:24.736000',\n",
       " '2016-07-17 13:53:58.307882',\n",
       " '2016-07-17 14:18:53.863674',\n",
       " '2016-07-17 14:22:58.805773',\n",
       " '2016-07-17 14:40:14.798286',\n",
       " '2016-07-17 14:43:36.235049',\n",
       " '2016-07-17 14:46:02.511999',\n",
       " '2016-07-17 15:20:19.588012',\n",
       " '2016-07-17 15:21:15.972852',\n",
       " '2016-07-17 15:53:21.886815',\n",
       " '2016-07-17 17:46:33.523057',\n",
       " '2016-07-17 17:48:19.753656',\n",
       " '2016-07-17 17:53:11.073067',\n",
       " '2016-07-17 17:54:06.863027',\n",
       " '2016-07-17 17:54:49.879645',\n",
       " '2016-07-17 18:05:16.310203',\n",
       " '2016-07-17 18:15:30.482549',\n",
       " '2016-07-17 19:08:49.619108',\n",
       " '2016-07-17 19:09:14.151947',\n",
       " '2016-07-17 19:37:46.118793',\n",
       " '2016-07-17 19:50:03.648934',\n",
       " '2016-07-17 19:53:47.259629',\n",
       " '2016-07-17 20:53:45.427076',\n",
       " '2016-07-17 20:54:19.049869',\n",
       " '2016-07-17 20:54:39.046062',\n",
       " '2016-07-17 20:57:31.475201',\n",
       " '2016-07-17 20:57:46.570265',\n",
       " '2016-07-17 20:59:51.518826',\n",
       " '2016-07-17 21:03:13.472660',\n",
       " '2016-07-17 21:04:54.258101',\n",
       " '2016-07-17 21:05:36.382183',\n",
       " '2016-07-17 21:07:29.286547',\n",
       " '2016-07-17 21:10:36.910250',\n",
       " '2016-07-18 06:26:03.797719',\n",
       " '2016-07-18 06:28:19.827089',\n",
       " '2016-07-18 06:33:08.540169',\n",
       " '2016-07-18 06:38:23.186904',\n",
       " '2016-07-18 08:16:05.934485',\n",
       " '2016-07-18 08:17:20.474851',\n",
       " '2016-07-18 08:23:19.435717',\n",
       " '2016-07-18 08:27:14.615281',\n",
       " '2016-07-18 08:36:51.974687',\n",
       " '2016-07-18 08:39:17.531915',\n",
       " '2016-07-18 09:05:53.487540',\n",
       " '2016-07-18 09:09:41.849102',\n",
       " '2016-07-18 19:01:17.501129',\n",
       " '2016-07-18 19:01:44.295859',\n",
       " '2016-07-18 19:03:57.611960',\n",
       " '2016-07-18 19:07:35.077555',\n",
       " '2016-07-18 19:32:03.385832',\n",
       " '2016-07-18 20:16:28.162782',\n",
       " '2016-07-19 10:42:46.338881',\n",
       " '2016-07-20 14:22:07.454265',\n",
       " '2016-07-20 16:11:36.912270',\n",
       " '2016-07-21 14:33:16.276039',\n",
       " '2016-07-21 15:52:52.575817',\n",
       " '2016-07-21 15:56:13.499626',\n",
       " '2016-07-21 15:57:34.487916',\n",
       " '2016-07-21 15:59:55.361077',\n",
       " '2016-07-21 16:20:09.964689',\n",
       " '2016-07-21 16:23:49.454156',\n",
       " '2016-07-21 16:24:53.691256',\n",
       " '2016-07-21 16:27:37.083485',\n",
       " '2016-07-21 16:28:46.897103',\n",
       " '2016-07-21 16:30:00.473435',\n",
       " '2016-07-21 16:33:24.151699',\n",
       " '2016-07-21 16:34:34.745454',\n",
       " '2016-07-21 16:37:33.761637',\n",
       " '2016-07-21 16:44:51.818372',\n",
       " '2016-07-21 16:46:55.239923',\n",
       " '2016-07-21 16:50:01.544369',\n",
       " '2016-07-21 16:57:40.987725',\n",
       " '2016-07-21 17:05:26.945221',\n",
       " '2016-07-21 17:10:27.834649',\n",
       " '2016-07-21 17:11:48.665692',\n",
       " '2016-07-21 19:15:17.809677',\n",
       " '2016-07-21 20:18:28.629622',\n",
       " '2016-07-22 09:12:18.369204',\n",
       " '2016-07-22 09:33:20.848032',\n",
       " '2016-07-22 09:55:03.634299',\n",
       " '2016-07-22 09:56:44.474854',\n",
       " '2016-07-22 11:52:40.369937',\n",
       " '2016-07-22 12:39:06.699739',\n",
       " '2016-07-22 14:23:40.573965',\n",
       " '2016-07-22 14:43:33.261506',\n",
       " '2016-07-22 15:01:06.569349',\n",
       " '2016-07-22 21:12:09.086164',\n",
       " '2016-07-22 21:14:19.160191',\n",
       " '2016-07-22 21:15:50.192180',\n",
       " '2016-07-22 21:17:36.040389',\n",
       " '2016-07-22 21:21:57.886842',\n",
       " '2016-07-22 21:33:21.648959',\n",
       " '2016-07-22 21:33:57.104399',\n",
       " '2016-07-23 07:55:56.462018',\n",
       " '2016-07-23 09:18:55.486046',\n",
       " '2016-07-23 09:20:01.825112',\n",
       " '2016-07-23 09:31:55.651858',\n",
       " '2016-07-23 09:34:14.404590',\n",
       " '2016-07-23 09:51:09.838073',\n",
       " '2016-07-23 11:09:35.738452',\n",
       " '2016-07-23 14:18:40.345240',\n",
       " '2016-07-23 14:19:23.231642',\n",
       " '2016-07-23 14:20:04.680489',\n",
       " '2016-07-23 14:21:34.631639',\n",
       " '2016-07-23 14:24:06.431376',\n",
       " '2016-07-23 21:29:57.559603',\n",
       " '2016-07-23 21:44:09.127505',\n",
       " '2016-07-23 21:54:45.577710',\n",
       " '2016-07-24 12:08:12.816935']"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "[str(r.start) for r in Session().query(RecorderRuns)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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": 28,
   "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
}