Column "domain" does not exist

[andjones132]

I have installed PostgreSQL + TimescaleDB following the instructions here. I am able to connect to the postgersql database and execute db.entities[:10] function and it returns the first 10 entities, so I know I am connected. However, when I try to execute df1 = db.fetch_all_data_of(('sensor.aqara_multi_sensor_master_bath_humidity',)) on one of my sensors I get UndefinedColumn: column "domain" does not exist error. I checked that states table that this function appears to be querying and the error message is correct, there is no 'domain' column.

[SQL: 
            SELECT domain, entity_id, state, last_changed, attributes
            FROM states
            WHERE
                entity_id IN ('sensor.aqara_multi_sensor_master_bath_humidity')
            AND
                state NOT IN ('unknown', 'unavailable')
            ORDER BY last_changed DESC
            LIMIT 50000
            ]

if I run !pip show HASS-data-detective I get the follwoing information. Am I running and old version of this module? Is there some way to change what columns are included in this function?

Name: HASS-data-detective
Version: 2.4
Summary: Tools for studying Home Assistant data.
Home-page: https://github.com/robmarkcole/HASS-data-detective
Author: Robin Cole
Author-email: robmarkcole@gmail.com
License: MIT
Location: /usr/local/lib/python3.9/dist-packages
Requires: pandas, pytz, ruamel.yaml, SQLAlchemy
Required-by: 

Error message details:

---------------------------------------------------------------------------
UndefinedColumn                           Traceback (most recent call last)
File /usr/local/lib/python3.9/dist-packages/sqlalchemy/engine/base.py:1799, in Connection._execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1798     if not evt_handled:
-> 1799         self.dialect.do_execute(
   1800             cursor, statement, parameters, context
   1801         )
   1803 if self._has_events or self.engine._has_events:

File /usr/local/lib/python3.9/dist-packages/sqlalchemy/engine/default.py:717, in DefaultDialect.do_execute(self, cursor, statement, parameters, context)
    716 def do_execute(self, cursor, statement, parameters, context=None):
--> 717     cursor.execute(statement, parameters)

UndefinedColumn: column "domain" does not exist
LINE 2:             SELECT domain, entity_id, state, last_changed, a...
                           ^


The above exception was the direct cause of the following exception:

ProgrammingError                          Traceback (most recent call last)
Input In [19], in <cell line: 1>()
----> 1 df1 = db.fetch_all_data_of(('sensor.aqara_multi_sensor_master_bath_humidity',))

File /usr/local/lib/python3.9/dist-packages/detective/core.py:127, in HassDatabase.fetch_all_data_of(self, sensors, limit)
    115     sensors_str = sensors_str.replace(",", "")
    117 query = f"""
    118     SELECT domain, entity_id, state, last_changed, attributes
    119     FROM states
   (...)
    125     LIMIT {limit}
    126     """
--> 127 df = pd.read_sql_query(query, self.url)
    128 print(f"The returned Pandas dataframe has {df.shape[0]} rows of data.")
    129 return df

File /usr/local/lib/python3.9/dist-packages/pandas/io/sql.py:436, in read_sql_query(sql, con, index_col, coerce_float, params, parse_dates, chunksize, dtype)
    378 """
    379 Read SQL query into a DataFrame.
    380 
   (...)
    433 parameter will be converted to UTC.
    434 """
    435 pandas_sql = pandasSQL_builder(con)
--> 436 return pandas_sql.read_query(
    437     sql,
    438     index_col=index_col,
    439     params=params,
    440     coerce_float=coerce_float,
    441     parse_dates=parse_dates,
    442     chunksize=chunksize,
    443     dtype=dtype,
    444 )

File /usr/local/lib/python3.9/dist-packages/pandas/io/sql.py:1579, in SQLDatabase.read_query(self, sql, index_col, coerce_float, parse_dates, params, chunksize, dtype)
   1531 """
   1532 Read SQL query into a DataFrame.
   1533 
   (...)
   1575 
   1576 """
   1577 args = _convert_params(sql, params)
-> 1579 result = self.execute(*args)
   1580 columns = result.keys()
   1582 if chunksize is not None:

File /usr/local/lib/python3.9/dist-packages/pandas/io/sql.py:1424, in SQLDatabase.execute(self, *args, **kwargs)
   1422 def execute(self, *args, **kwargs):
   1423     """Simple passthrough to SQLAlchemy connectable"""
-> 1424     return self.connectable.execution_options().execute(*args, **kwargs)

File <string>:2, in execute(self, statement, *multiparams, **params)

File /usr/local/lib/python3.9/dist-packages/sqlalchemy/util/deprecations.py:390, in _decorate_with_warning.<locals>.warned(fn, *args, **kwargs)
    388 if not skip_warning:
    389     _warn_with_version(message, version, wtype, stacklevel=3)
--> 390 return fn(*args, **kwargs)

File /usr/local/lib/python3.9/dist-packages/sqlalchemy/engine/base.py:3136, in Engine.execute(self, statement, *multiparams, **params)
   3118 """Executes the given construct and returns a
   3119 :class:`_engine.CursorResult`.
   3120 
   (...)
   3133 
   3134 """
   3135 connection = self.connect(close_with_result=True)
-> 3136 return connection.execute(statement, *multiparams, **params)

File /usr/local/lib/python3.9/dist-packages/sqlalchemy/engine/base.py:1271, in Connection.execute(self, statement, *multiparams, **params)
   1262 if isinstance(statement, util.string_types):
   1263     util.warn_deprecated_20(
   1264         "Passing a string to Connection.execute() is "
   1265         "deprecated and will be removed in version 2.0.  Use the "
   (...)
   1268         "driver-level SQL string."
   1269     )
-> 1271     return self._exec_driver_sql(
   1272         statement,
   1273         multiparams,
   1274         params,
   1275         _EMPTY_EXECUTION_OPTS,
   1276         future=False,
   1277     )
   1279 try:
   1280     meth = statement._execute_on_connection

File /usr/local/lib/python3.9/dist-packages/sqlalchemy/engine/base.py:1575, in Connection._exec_driver_sql(self, statement, multiparams, params, execution_options, future)
   1565         (
   1566             statement,
   1567             distilled_params,
   (...)
   1571             statement, distilled_parameters, execution_options
   1572         )
   1574 dialect = self.dialect
-> 1575 ret = self._execute_context(
   1576     dialect,
   1577     dialect.execution_ctx_cls._init_statement,
   1578     statement,
   1579     distilled_parameters,
   1580     execution_options,
   1581     statement,
   1582     distilled_parameters,
   1583 )
   1585 if not future:
   1586     if self._has_events or self.engine._has_events:

File /usr/local/lib/python3.9/dist-packages/sqlalchemy/engine/base.py:1842, in Connection._execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1839             branched.close()
   1841 except BaseException as e:
-> 1842     self._handle_dbapi_exception(
   1843         e, statement, parameters, cursor, context
   1844     )
   1846 return result

File /usr/local/lib/python3.9/dist-packages/sqlalchemy/engine/base.py:2023, in Connection._handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   2021     util.raise_(newraise, with_traceback=exc_info[2], from_=e)
   2022 elif should_wrap:
-> 2023     util.raise_(
   2024         sqlalchemy_exception, with_traceback=exc_info[2], from_=e
   2025     )
   2026 else:
   2027     util.raise_(exc_info[1], with_traceback=exc_info[2])

File /usr/local/lib/python3.9/dist-packages/sqlalchemy/util/compat.py:207, in raise_(***failed resolving arguments***)
    204     exception.__cause__ = replace_context
    206 try:
--> 207     raise exception
    208 finally:
    209     # credit to
    210     # https://cosmicpercolator.com/2016/01/13/exception-leaks-in-python-2-and-3/
    211     # as the __traceback__ object creates a cycle
    212     del exception, replace_context, from_, with_traceback

File /usr/local/lib/python3.9/dist-packages/sqlalchemy/engine/base.py:1799, in Connection._execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1797                 break
   1798     if not evt_handled:
-> 1799         self.dialect.do_execute(
   1800             cursor, statement, parameters, context
   1801         )
   1803 if self._has_events or self.engine._has_events:
   1804     self.dispatch.after_cursor_execute(
   1805         self,
   1806         cursor,
   (...)
   1810         context.executemany,
   1811     )

File /usr/local/lib/python3.9/dist-packages/sqlalchemy/engine/default.py:717, in DefaultDialect.do_execute(self, cursor, statement, parameters, context)
    716 def do_execute(self, cursor, statement, parameters, context=None):
--> 717     cursor.execute(statement, parameters)

ProgrammingError: (psycopg2.errors.UndefinedColumn) column "domain" does not exist
LINE 2:             SELECT domain, entity_id, state, last_changed, a...
                           ^

[SQL: 
            SELECT domain, entity_id, state, last_changed, attributes
            FROM states
            WHERE
                entity_id IN ('sensor.aqara_multi_sensor_master_bath_humidity')
            AND
                state NOT IN ('unknown', 'unavailable')
            ORDER BY last_changed DESC
            LIMIT 50000
            ]
(Background on this error at: https://sqlalche.me/e/14/f405)

[robmarkcole]

Suggest asking the author of the addon if this is an intentional omission, however I dont think so. Could also be an issue with the sensor, check the HA issues.

The easiest solution is to install detective in editable mode using pip install -e ... and edit the query at

HASS-data-detective/detective/core.py

Line 100 in f861f70

SELECT domain, entity_id, state, last_changed, attributes

Even easier is just to create a standalone function like def fetch_all_sensor_data() and modify

[andjones132]

Hi @robmarkcole. I did as suggested and cloned the repository locally, fixed the SQL queries in core.py and then uninstalled and reinstalled the HASS-data-detective module using pip install -e. Unfortunately, I could not figure out how to get this to work on hassos directly. After installing the 'new' version and running the query it was still showing the old SQL query error. Not sure if this because it is somehow getting stored in the docker container or what. Anyway, I gave up on that for right now. Instead I would like to just use HASS-data-detective on my local copy of Jupyterlab. However, for th life of my I cannot figure out how to connect to my tiemseriesdb on my HASSOS instance. Keep getting Connection to port 5432 refused error.

This is the connection url I am using:

postgresql+psycopg2://postgres:password@192.168.1.50:5432/77b2833f-timescaledb/homeassistant

I have tried both with and without the '77b2833f-timescaledb' and it doesn't seem to make any difference. I realize this has nothing to do with your module, but wanted to ask if you have been able to connect to a postgres db running on home assistant os? I may have to give up and just install a postgres db in a seperate docker container.

[robmarkcole]

Almost certainly I expect the port is not exposed, this will be a setting somewhere

[andjones132]

You are correct, Sir. After a lot of Googling I found that I needed to update /data/postgres/pg_hba.conf in the timescaledb docker container to add the following line to include my local lan ips:

host    all             all              192.168.1.0/24		md5

You can also use '0.0.0.0/0' as the Foreign Address ip, but I preferred to keep it to just my local lan ips.

I also checked /data/postgres/postgresql.conf to make sure 'listen_addresses' = '*'. This was already set. I may narrow down this setting later to be 'localhost' and a comma-separated list of local lan ip addresses to lock down access a bit more.

Once I made these two changes and restarted the timescaledb docker container, then suddenly I was able to connnect remotely. This was my first experience with working with Postgresql so working my way up the learning curve.

The only issue I have now is that I still cannot use the HASS-data-detective even when installing it using pip install -e /path/to/HASS-data-detective in Jupyter. Even after uninstalling, reinstalling, and restarting the Kernal it is not picking up my changes. However, if copied the updated functions form core.py into my NB and then they worked. I am not sure why 'pip install -e' is not working.

Thanks again for your support.

[pebe-espana]

HI @robmarkcole,

just brand new to using Home Assistant. Installed the JupyterLab add-on and ran into similar errors using the GETTING_STARTED example code.

after having successfully used call "db = detective.db_from_hass_config()"
message is:
'Successfully connected to database sqlite:////config/home-assistant_v2.db
There are 152 entities with data'

Which all looks ok

But thereafter example code in home-assistant JupyterLab notebook breaks down.
Inspired by https://github.com/robmarkcole/HASS-data-detective#simple-query also tried inserting alternate lines to explore first what db may contain using

df1 = db.fetch_all_data_of(('sensor.wlan_switch_energy_power'))
or
df1 = db.fetch_all_sensor_data()
or
df1 = db.fetch_all_sensor_data_of(('sensor.wlan_switch_energy_power')) and variants

and always got error "OperationalError: no such column: domain"

As I am working within the HomeAssistant OS WebUI provided, all the above explanations re pip install etc etc remain mysterious to me - any chance to fix from within HomeAssistant web-interface???

Thanks,
Peter

[robmarkcole]

@pebe-espana can you access your db using a tool like https://dbeaver.io/ and check what columns are available? I have not been keeping a track on all the changes in HA recently and it is possible the schema has changed

[pebe-espana]

@robmarkcole - thanks for the prompt reply. Apologies if my answers are of RTFM kind - this SQL database stuff is still foreign to me. Used beaver as you suggested, but am a bit lost in seeing what it tells me about the structure and 'schema' that connects back to integrations and the 152 entities. I attach two screenshots that break down the database: database seems to contain tables named event_data, events, etc etc , each of which has columns (of different column names & associated data type) ... so what am I looking/filtering for to locate e.g. 'sensor.wlan_switch_energy_power' . From python code it would seem to me that the code looks at table ='states' and specific 'entity_id' (is that =sensor.wlan_swith_energy_power'???) in there. Indeed column domain is absent in the states table (or the other tables).

What takes the place of domain?

And how to fix it?

I hope this helps.

[robmarkcole]

Looks like domain has been removed from the states table - would be useful to identify this change in the home assistant release notes to be sure this is an across the board change, and not just for some recorders

[pebe-espana]

Tried to see if I could isolate the function "fetch_all_data_of()" as a function "my_fetch_all_data_of()" in my own notebook, but then got lost on the dependencies of other parts, it being part of a class definition .....

As the collection is already successful, any chance to convert db to pandas and then throw away all the unneeded parts using panda handling?

If you could give me a hint for a minimalistic subsection of the full HASS-data-detective GitHub code I would appreciate it.
Thanks

[robmarkcole]

The executed sql query is at

HASS-data-detective/detective/core.py

Line 99 in f861f70

query = f"""

but it is dependent on domain. You can install detective and editable mode and make changes on the fly to the query.

eg try

            SELECT entity_id, state, last_changed, attributes
            FROM states
            WHERE
                state NOT IN ('unknown', 'unavailable')
            ORDER BY last_changed DESC

[pebe-espana]

Thank you. I succeeded with the following code after having fetched the database using
the code in attached text file 'code snippet.txt. (sorry editor function <> does not seem to work in this comment box)

codesnippet.txt

note: I had to put the string 'sensor.wlan_switch_energy_power' explicitly into the query, somehow the query string would not allow the variable to be inserted, as in your python class. I could also put two such strings in, then two entities were extracted by query.

note: Also had then the problem that in the sample code GETTING STARTED [Popular entities section]
reading event_data = json.loads(event.event_data)
crashes with message
TypeError: the JSON object must be str, bytes or bytearray, not NoneType

But thanks - the goal of extracting a particular data set works, and then I export it anyway to other pc and python/excel or whatever.

Much appreciated your help, and maybe others read this minimalist solution and workaround and find teh 'code snippet.txt' already helpful as well!

[robmarkcole]

In my own db domain does exist but appears no longer used: