robmarkcole
UndefinedColumn: column "domain" does not exist
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: [email protected]
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)
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
https://github.com/robmarkcole/HASS-data-detective/blob/f861f7064c1f69ee0c167d858cca70e7e22fc985/detective/core.py#L100
Even easier is just to create a standalone function like def fetch_all_sensor_data() and modify
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:[email protected]: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.
Almost certainly I expect the port is not exposed, this will be a setting somewhere
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.
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
@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
@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.
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
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
The executed sql query is at https://github.com/robmarkcole/HASS-data-detective/blob/f861f7064c1f69ee0c167d858cca70e7e22fc985/detective/core.py#L99 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
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)
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!
