HASS-data-detective: AttributeError: 'str' object has no attribute '_execute_on_connection'

Running the supplied GETTING_STARTED.ipynb it stops with the following error:

Error with query: SELECT * FROM events WHERE event_type = 'call_service' ORDER BY time_fired
---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
File /usr/local/lib/python3.9/dist-packages/sqlalchemy/engine/base.py:1410, in Connection.execute(self, statement, parameters, execution_options)
   1409 try:
-> 1410     meth = statement._execute_on_connection
   1411 except AttributeError as err:

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

I tried this running on HA with Jupyter Lab 0.10.2 HA is running in a virtual machine on X86.

OS Version:               Home Assistant OS 10.1
Home Assistant Core:      2023.5.3

The information on starting:

!pip show HASS-data-detective
Name: HASS-data-detective
Version: 3.0
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: 
import detective.core as detective
import detective.functions as functions
import pandas as pd
​
db = detective.db_from_hass_config()
YAML tag !include_dir_merge_list is not supported
YAML tag !include_dir_named is not supported
YAML tag !include_dir_merge_named is not supported
Successfully connected to database sqlite:////config/home-assistant_v2.db
There are 390 entities with data

About this issue

  • Original URL
  • State: open
  • Created 4 years ago
  • Comments: 21 (6 by maintainers)

Most upvoted comments

I think the issue here is with how the “perform_query” function is being called. It’s setup to accept queries in the form of sqlalchemy text clauses, but the notebook has the function accepting a raw string.

To use the perform_query function, you need:

from sqlalchemy import text

db.perform_query(text(‘SELECT * FROM events’))

Right?

Actually, this is an issue unique to home assistant’s addon. It is out of date. For a better experience in jupyter “File” -> “Open From URL” and use the link here. Then all is right with the world! Since you are in home-assistant you can use the db = detective.db_from_hass_config() to get the db connection.