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.