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

[rorso]

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: [email protected]
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

[robmarkcole]

Make sure you are using a virtual env - there were changes in sqlalchemy==2.0.7

[k-w-1]

Make sure you are using a virtual env - there were changes in sqlalchemy==2.0.7

Could you expand? I'm not sure if this is what @rorso meant, but I'm also running HAOS in an x86 VM (e.g. with supervisor & the HACA JupyterLab add-on), and getting the same error. Although I'm guessing it is inconsequential per the DB connect success that I'm seeing, I'm also running the official MariaDB add-on.

I can't see any (er, obvious to me) way to run/change a venv in this setup, although I did try adding a cell immediately following !pip show HASS-data-detective with !pip install --force-reinstall -v "SQLAlchemy==2.0.6" which notably complains that hass-data-detective 3.0 requires SQLAlchemy>=2.0.7; and !pip install --force-reinstall -v "SQLAlchemy==2.0.7" still presents the same error.

TIA!

[rorso]

I'm really sorry. It may be obvious to you, but not to me. "Just do lfakjsasjfladjfhal" sounds like a magic spell to me. There are people outside that are not digital natives, although I follow the technology a couple of decades now.

But I did try to get enlightened about "virtual env". It seems that I have to mess around heavily with the way my HA installation is running - which will not happen. I'm glad that it works great as is and I see no point in a total rework of my installation just to get this python script running, no matter how useful it might be.

[k-w-1]

I'm really sorry. It may be obvious to you, but not to me. "Just do lfakjsasjfladjfhal" sounds like a magic spell to me. There are people outside that are not digital natives, although I follow the technology a couple of decades now.

But I did try to get enlightened about "virtual env". It seems that I have to mess around heavily with the way my HA installation is running - which will not happen. I'm glad that it works great as is and I see no point in a total rework of my installation just to get this python script running, no matter how useful it might be.

Sorry, I think I was misunderstood 🙂.

I was asking robmarkcole for more information about what he meant by using a virtual env (for what it's worth, I'm glad you didn't mess with your HA -- this add-on is a docker container, so AFAIK, any changes to the virtual env (aka venv) would need to be inside the docker container (each HA Add-on being in essence, a lightweight VM running inside our HA VMs)).

Anyway, I only happened to mention your username as, like you, I also happen to be running HA as a virtual machine -- in case this is relevant to the issue at hand.

Cheers;
K

[robmarkcole]

Some info on virtual environments:

https://docs.python.org/3/tutorial/venv.html

This repository readme also include instructions for how to run detective in Docker

The issue here is I suspect the python environment people are installing this package into has some conflicting requirements, so the way to rule this out is by using a virtual or isolated environment

[geiseri]

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.

[rorso]

Actually, this is an issue unique to home assistant's addon. It is out of date.

Thank you! This gets me almost running.

Hopefully the addon will be updated soon.

[martin18d]

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?

[ahoy]

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?

This is what worked for me. I am using a Postgres database as the back-end, so not sure if that affects how these functions work.

[vb7445]

I just installed the JupterLabs AddOn to HA, Version | core-2024.8.3 and low and behold I'm getting this attribute error.

JupyterLab Current version: 0.15.2

AttributeError Traceback (most recent call last)
File /usr/local/lib/python3.11/dist-packages/sqlalchemy/engine/base.py:1414, in Connection.execute(self, statement, parameters, execution_options)
1413 try:
-> 1414 meth = statement._execute_on_connection
1415 except AttributeError as err:
AttributeError: 'str' object has no attribute '_execute_on_connection'
The above exception was the direct cause of the following exception:

Am I to assume this AddOn is still broke?

I performed the File/Open from URL as mentioned earlier and that worked.

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.

[rct]

This seems to be an an old issue/confusing issue. If anyone tries Home Assistant Data Science which is linked from home-assistant.io pages, it no longer works and appears to have not worked since spring of 2023.

The problem may not be in this module, HASS-data-detective but may that the JupyterLab from Frenck in the Home Assistant Community Add-on store, https://github.com/hassio-addons/addon-jupyterlab has got old versions of Hass-data-detective, SQLAlchemy, and possibly other modules(*)

(* I didn't follow all the SQLalchemy discussion in this issue to see if just a newer version of SQLAlchemy fixes things or not. I did try the workaround of importing text from sqlalchemy and wrapping the event query from GETTING_STARTED.ipynb in a call to text. While the query didn't produce an error, the result was empty.

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"))

[robmarkcole]

I think the best action is to open an issue in the addon as I'm not sure how actively it is updated

[rct]

I think the best action is to open an issue in the addon as I'm not sure how actively it is updated

Done, github already linked it here: hassio-addons/addon-jupyterlab#634

Note the add-on currently contains version 3.0, from the !pip show HASS-data-detective in the notebook:

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: [email protected]
License: MIT
Location: /usr/local/lib/python3.11/dist-packages
Requires: pandas, pytz, ruamel.yaml, SQLAlchemy

The addon is using 2.0.35 of sqlalchemy.

Name: SQLAlchemy
Version: 2.0.35
Summary: Database Abstraction Library
Home-page: https://www.sqlalchemy.org/
Author: Mike Bayer
Author-email: [email protected]
License: MIT
Location: /usr/local/lib/python3.11/dist-packages
Requires: greenlet, typing-extensions
Required-by: HASS-data-detective

I see that 3.1 was released two weeks ago, but it looks like requirements.txt still has sqlalchemy==2.0.7?

So does your module still work with current (2024.10.x) Home Assistant as long as sqlalchemy 2.0.7 (from March 2023) is used?

[robmarkcole]

I vaguely remember there was a breaking change in sqlalchemy.. best thing is to try it

[rct]

I vaguely remember there was a breaking change in sqlalchemy.. best thing is to try it

First, thanks for getting back to me so quickly. I'll try to setup a Jupyter environment where I can easily test this (conda doesn't like pip installs).

I'm not sure if it is the cause or not, but see above in this issue about sqlalchemy needing text vs. raw: #157 (comment)

[rct]

More info: It also appears that the notebook in the add-on, GETTING_STARTED.ipynb (from3.0?) in the add-on is considerably different than what's in master now Getting started with detective.ipynb

That notebook seems to work (with a few warnings and last_updated_ts doesn't get displayed correctly) with sqlalchemy 2.0.35 in the add-on.

See @geiseri comment above in this issue: #157 (comment)

So I don't know if the problem is more to do with the notebook or the version of alchemy

---

Also here is the add-on's requirements: https://github.com/hassio-addons/addon-jupyterlab/blob/main/jupyterlab/requirements.txt

HASS-data-detective==3.0
...
jupyterlab==4.2.5
matplotlib==3.9.2
...
pandas==2.2.3
...
python-dateutil==2.9.0.post0
...
SQLAlchemy==2.0.35

Maybe getting Frenck (or whoever has access to that repo to pull in 3.1 would make things work.

[robmarkcole]

There might be some reason for not upgrading the dependency in the add on - not sure

[rct]

There might be some reason for not upgrading the dependency in the add on - not sure

To be clear, the add-on uses the current version of sqlalchemy, not the version required by requirements.txt from HASS-data-detective.

However, doing some more digging and testing I found:

The example notebook in the add-on fails because it calls db.perform_query. Your example notebook works because it does NOT call db.perform_query directly.

I created a local venv, cloned your repo, pip installed requirements and tests. The existing tests succeed. However, even with sqlalchemy==2.0.7, running `db.perform_query("SELECT 1;") fails with the same underlying error "ObjectNotExecutableError: Not an executable object.

Should something like `db.perform_query("SELECT 1;") work, or does perform_query need something other than a string?

[robmarkcole]

Ok there is a bug here then

[frenck]

I'm failing to see how this is an add-on issue. IMHO the issues are downstream, so not really sure what you want me to do here.

../Frenck

[rct]

Frenck wrote:

so not really sure what you want me to do here.

Now that it is understood to be a problem with the HASS-data-detective module and not the notebooks pulled in from home-assistant-notebooks, nothing until a fixed version of this module is available.