Fix sql api

[geiseri]

With the hass addon the current code breaks with the error:

---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
Cell In[7], line 5
      2 import detective.functions as functions
      3 import pandas as pd
----> 5 db = detective.db_from_hass_config()

File /usr/local/lib/python3.9/dist-packages/detective/core.py:19, in db_from_hass_config(path, **kwargs)
     16     path = config.find_hass_config()
     18 url = config.db_url_from_hass_config(path)
---> 19 return HassDatabase(url, **kwargs)

File /usr/local/lib/python3.9/dist-packages/detective/core.py:57, in HassDatabase.__init__(self, url, fetch_entities)
     55     print("Successfully connected to database", stripped_db_url(url))
     56     if fetch_entities:
---> 57         self.fetch_entities()
     58 except Exception as exc:
     59     if isinstance(exc, ImportError):

File /usr/local/lib/python3.9/dist-packages/detective/core.py:85, in HassDatabase.fetch_entities(self)
     79 """Fetch entities for which we have data."""
     80 query = text(
     81     """
     82     SELECT DISTINCT(entity_id) FROM states
     83     """
     84 )
---> 85 response = self.perform_query(query)
     87 # Parse the domains from the entities.
     88 self.entities = [e[0] for e in response]

File /usr/local/lib/python3.9/dist-packages/detective/core.py:73, in HassDatabase.perform_query(self, query, **params)
     71 """Perform a query."""
     72 try:
---> 73     return self.engine.execute(query, params)
     74 except:
     75     print(f"Error with query: {query}")

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

it looks like the fix is noted in: https://stackoverflow.com/a/75316945/3278031

[robmarkcole]

Looks like a straightforward fix, can you create a PR? Thanks

[geiseri]

Let me give it a whack. I was hoping for an "easy way out" though ;)

[timoei]

@geiseri I got the same problems with the API usage. Are you working on a fix?