Getting Started Error

[yozh]

I have been trying to get this to work for a long time, on and off, I was told before the books were outdated, but I always get an error when I try to run it.

---

AttributeError Traceback (most recent call last)
File /usr/local/lib/python3.11/dist-packages/sqlalchemy/engine/base.py:1412, in Connection.execute(self, statement, parameters, execution_options)
1411 try:
-> 1412 meth = statement._execute_on_connection
1413 except AttributeError as err:

AttributeError: 'str' object has no attribute '_execute_on_connection'

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

ObjectNotExecutableError Traceback (most recent call last)
Cell In[10], line 13
9 # We keep track of contexts that we processed so that we will only process
10 # the first service call in a context, and not subsequent calls.
11 context_processed = set()
---> 13 for event in db.perform_query("SELECT * FROM events WHERE event_type = 'call_service' ORDER BY time"):
14 entity_ids = None
16 # Skip if we have already processed an event that was part of this context

File /usr/local/lib/python3.11/dist-packages/detective/core.py:75, in HassDatabase.perform_query(self, query, **params)
73 try:
74 with self.engine.connect() as conn:
---> 75 return conn.execute(query, params)
76 except:
77 print(f"Error with query: {query}")

File /usr/local/lib/python3.11/dist-packages/sqlalchemy/engine/base.py:1414, in Connection.execute(self, statement, parameters, execution_options)
1412 meth = statement._execute_on_connection
1413 except AttributeError as err:
-> 1414 raise exc.ObjectNotExecutableError(statement) from err
1415 else:
1416 return meth(
1417 self,
1418 distilled_parameters,
1419 execution_options or NO_OPTIONS,
1420 )

ObjectNotExecutableError: Not an executable object: "SELECT * FROM events WHERE event_type = 'call_service' ORDER BY time"

What am I doing wrong ?

[Vitani]

I think this is the fix

from sqlalchemy import text
...

for event in db.perform_query(text("SELECT * FROM events JOIN event_data ON events.data_id = event_data.data_id WHERE event_type = 'call_service' ORDER BY time_fired")):

[yozh]

I think this is the fix

from sqlalchemy import text
...

for event in db.perform_query(text("SELECT * FROM events JOIN event_data ON events.data_id = event_data.data_id WHERE event_type = 'call_service' ORDER BY time_fired")):

Tried it....

for event in db.perform_query(text("SELECT * FROM events JOIN event_data ON events.data_id = event_data.data_id WHERE event_type = 'call_service' ORDER BY time_fired")):

and got this.

---

NameError Traceback (most recent call last)
Cell In[5], line 13
9 # We keep track of contexts that we processed so that we will only process
10 # the first service call in a context, and not subsequent calls.
11 context_processed = set()
---> 13 for event in db.perform_query(text("SELECT * FROM events JOIN event_data ON events.data_id = event_data.data_id WHERE event_type = 'call_service' ORDER BY time_fired")):
14 entity_ids = None
16 # Skip if we have already processed an event that was part of this context

NameError: name 'text' is not defined

[Zapfmeister]

I think this is the fix

from sqlalchemy import text
...

for event in db.perform_query(text("SELECT * FROM events JOIN event_data ON events.data_id = event_data.data_id WHERE event_type = 'call_service' ORDER BY time_fired")):

This seems to work. But for me all event_type are "None" and therefore i get not data