AttributeError: 'Engine' object has no attribute 'execute' when excuting detective.HassDatabase(db_url)

[ZZDDD]

When excuting the below section in jupyter notebook - ''Getting started with detective.ipynb":

db = detective.HassDatabase(db_url) # To init without fetching entities fetch_entities=False

The output is:

Successfully connected to database sqlite:////home/config/.homeassistant/home-assistant_v2.db Error with query: SELECT DISTINCT(entity_id) FROM states

'Engine' object has no attribute 'execute'

The error message:

......... /site-packages/detective/core.py:73), in HassDatabase.perform_query(self, query, **params) 71 """Perform a query.""" ... ---> 73 return self.engine.execute(query, params) 74 except: 75 print(f"Error with query: {query}")

AttributeError: 'Engine' object has no attribute 'execute'

It seems like the the problem occured in the sqlalchemy module.

I have tried to run this notebook on both a Raspberry Pi installed with Home Assistant OS and a Windows machine. The connection to the database is fine, but the same error above is consistent, what could go wrong? Thanks a lot.

[ZZDDD]

According to #150 , I modified the code given by: https://stackoverflow.com/questions/75316741/attributeerror-engine-object-has-no-attribute-execute-when-trying-to-run-sq/75316945#75316945 in the core.py file, line 74 to:

with self.engine.connect() as conn:
    return conn.execute(query, params)

However, this didn't work when running the notebook section: db = detective.HassDatabase(db_url) # To init without fetching entities fetch_entities=False and threw the following error:

Successfully connected to database sqlite:////home/config/.homeassistant/home-assistant_v2.db Error with query: SELECT DISTINCT(entity_id) FROM states

(sqlite3.OperationalError) unable to open database file (Background on this error at: https://sqlalche.me/e/14/e3q8)

OperationalError: (sqlite3.OperationalError) unable to open database file (Background on this error at: https://sqlalche.me/e/14/e3q8)

After this, I tried to change the sqlalchemy version to 1.4.x by:

pip install sqlalchemy==1.4.1 and run the same section: db = detective.HassDatabase(db_url) # To init without fetching entities fetch_entities=False This still got the same error.

But interestingly, the section: db = detective.db_from_hass_config() worked! The output is:

Successfully connected to database sqlite:////config/home-assistant_v2.db There are 89 entities with data Entities are listed on an attribute

So, what could be done to solve this problem? Is there any better ways to modified the code so that it could adapt the 2.0.x version of sqlalchemy?

[robmarkcole]

resolved with

    def perform_query(self, query, **params):
        """Perform a query."""
        try:
            with self.engine.connect() as conn:
                return conn.execute(query, params)
        except:
            print(f"Error with query: {query}")
            raise

[martin3000]

This means that HASS-data-detective only runs with HA 2023.4 and newer. In older versions, there is no state_meta and HASS-data-detective 2.6 does not work because it uses engine.execute()