Access to history database

[simeneide]

Hi,
I am wondering what the best practice would be to access the history that is stored in the home assistant database? I see multiple solutions:

1. install a sql driver and query directly
2. use the HA api (encountered an issue when creating here client here inside pyscript, see error below)
3. some native way of doing it in pyscript?

Anyone has any good solutions here?

Error on using api:

RuntimeError: I/O must be done in the executor; Use `await hass.async_add_executor_job()` at custom_components/pyscript/eval.py, line 1899: return func(*args, **kwargs)

[dlashua]

The Home Assistant API is likely the way to go. You shared the error, but not the code that produced it. If you share the code, someone can probably help.

[simeneide]

The Home Assistant API is likely the way to go. You shared the error, but not the code that produced it. If you share the code, someone can probably help.

ok, good! And of course, I run a hass pyscript jupyter kernel and the code I run is the following (with token being my actual token):

from homeassistant_api import Client
client = Client(
    'http://192.168.xx.xxx:8123',
    token
)

This gives the error

Exception in <jupyter_2> line 4:
        token
        ^
RuntimeError: I/O must be done in the executor; Use `await hass.async_add_executor_job()` at custom_components/pyscript/eval.py, line 1899: return func(*args, **kwargs)

[craigbarratt]

See this section in the docs.

[dlashua]

Also, when you said "HA api" I assumed you meant the Internal API. Using the "Client" is accessing the External API. There's nothing wrong with either way, though, as Craig pointed out, the "Client" uses blocking I/O. Using some other Client (i.e aiohttp) that supports asyncio is likely a better approach.

Pyscript was written to make creating Home Assistant automations in python a simple, easy to understand process. However, it sometimes makes doing things outside of that box different than it would be in "plain python". Depending on your skill level with Python, you may have an easier time writing a plain python script to gather the data you're looking for an publishing it to a sensor using Home Assistant's REST API. With this approach, existing examples for running a command line script with Home Assistant, accessing REST APIs in Python, and Home Assistants REST API documentation and examples can get you the result you desire.

To access Home Assistant history using the HA Internal APIs, you're likely going to want to look into the "recorder" component.

To use the REST API in an async way, you can use aiohttp, some other asyncio capable http client, or you can look at homeassistant.helpers.aiohttp_client. Alternately, as Craig pointed out, you can also use blocking I/O and pyscript's task.executor.

[schlop]

Has anyone looked into this recently? I need access to history data for a couple of automations. With nodered and AppDaemon this is relatively easy to do but Pyscript does not seem to have this feature implemented. Any advice how to query the internal home assistant api via the hass object for history data would be much appreciated.

[jkaberg]

@schlop Haven't looked myself, but pretty sure the hass object is you're friend here. I don't think the structure is defined somewhere, but traversing the object is trivial, use this as an starting point

[herveja]

Hi,
Don't know if it still usfull but here is how I read / add data into statistics table of homeassistant > https://github.com/herveja/homeAssistant/blob/main/gazpar_import_statistics.py

[ALERTua]

#405 has the solution it seems to me.

[MartvanMale]

As far as I can tell there is a native way within Pyscript to access historical data.
The example below will connect to the recorder database and get a dataframe of the date.

There is some documentation related to connecting to your database here

import detective.core as detective

db_url = "URL of database"
db = detective.HassDatabase(db_url)

df = db.fetch_all_data_of(['person.mart'])

log.info(df.head())

This returns a panda dataframe , with which you can do a lot.