Comments (12)
Suggest asking the author of the addon if this is an intentional omission, however I dont think so. Could also be an issue with the sensor, check the HA issues.
The easiest solution is to install detective in editable mode using pip install -e ...
and edit the query at
HASS-data-detective/detective/core.py
Line 100 in f861f70
Even easier is just to create a standalone function like def fetch_all_sensor_data()
and modify
from hass-data-detective.
Hi @robmarkcole. I did as suggested and cloned the repository locally, fixed the SQL queries in core.py and then uninstalled and reinstalled the HASS-data-detective module using pip install -e. Unfortunately, I could not figure out how to get this to work on hassos directly. After installing the 'new' version and running the query it was still showing the old SQL query error. Not sure if this because it is somehow getting stored in the docker container or what. Anyway, I gave up on that for right now. Instead I would like to just use HASS-data-detective on my local copy of Jupyterlab. However, for th life of my I cannot figure out how to connect to my tiemseriesdb on my HASSOS instance. Keep getting Connection to port 5432 refused error.
This is the connection url I am using:
postgresql+psycopg2://postgres:[email protected]:5432/77b2833f-timescaledb/homeassistant
I have tried both with and without the '77b2833f-timescaledb' and it doesn't seem to make any difference. I realize this has nothing to do with your module, but wanted to ask if you have been able to connect to a postgres db running on home assistant os? I may have to give up and just install a postgres db in a seperate docker container.
from hass-data-detective.
Almost certainly I expect the port is not exposed, this will be a setting somewhere
from hass-data-detective.
You are correct, Sir. After a lot of Googling I found that I needed to update /data/postgres/pg_hba.conf in the timescaledb docker container to add the following line to include my local lan ips:
host all all 192.168.1.0/24 md5
You can also use '0.0.0.0/0' as the Foreign Address ip, but I preferred to keep it to just my local lan ips.
I also checked /data/postgres/postgresql.conf to make sure 'listen_addresses' = '*'. This was already set. I may narrow down this setting later to be 'localhost' and a comma-separated list of local lan ip addresses to lock down access a bit more.
Once I made these two changes and restarted the timescaledb docker container, then suddenly I was able to connnect remotely. This was my first experience with working with Postgresql so working my way up the learning curve.
The only issue I have now is that I still cannot use the HASS-data-detective even when installing it using pip install -e /path/to/HASS-data-detective
in Jupyter. Even after uninstalling, reinstalling, and restarting the Kernal it is not picking up my changes. However, if copied the updated functions form core.py into my NB and then they worked. I am not sure why 'pip install -e' is not working.
Thanks again for your support.
from hass-data-detective.
HI @robmarkcole,
just brand new to using Home Assistant. Installed the JupyterLab add-on and ran into similar errors using the GETTING_STARTED example code.
after having successfully used call "db = detective.db_from_hass_config()"
message is:
'Successfully connected to database sqlite:////config/home-assistant_v2.db
There are 152 entities with data'
Which all looks ok
But thereafter example code in home-assistant JupyterLab notebook breaks down.
Inspired by https://github.com/robmarkcole/HASS-data-detective#simple-query also tried inserting alternate lines to explore first what db may contain using
df1 = db.fetch_all_data_of(('sensor.wlan_switch_energy_power'))
or
df1 = db.fetch_all_sensor_data()
or
df1 = db.fetch_all_sensor_data_of(('sensor.wlan_switch_energy_power')) and variants
and always got error "OperationalError: no such column: domain"
As I am working within the HomeAssistant OS WebUI provided, all the above explanations re pip install etc etc remain mysterious to me - any chance to fix from within HomeAssistant web-interface???
Thanks,
Peter
from hass-data-detective.
@pebe-espana can you access your db using a tool like https://dbeaver.io/ and check what columns are available? I have not been keeping a track on all the changes in HA recently and it is possible the schema has changed
from hass-data-detective.
@robmarkcole - thanks for the prompt reply. Apologies if my answers are of RTFM kind - this SQL database stuff is still foreign to me. Used beaver as you suggested, but am a bit lost in seeing what it tells me about the structure and 'schema' that connects back to integrations and the 152 entities. I attach two screenshots that break down the database: database seems to contain tables named event_data, events, etc etc , each of which has columns (of different column names & associated data type) ... so what am I looking/filtering for to locate e.g. 'sensor.wlan_switch_energy_power' . From python code it would seem to me that the code looks at table ='states' and specific 'entity_id' (is that =sensor.wlan_swith_energy_power'???) in there. Indeed column domain is absent in the states table (or the other tables).
What takes the place of domain?
And how to fix it?
I hope this helps.
from hass-data-detective.
Looks like domain
has been removed from the states table - would be useful to identify this change in the home assistant release notes to be sure this is an across the board change, and not just for some recorders
from hass-data-detective.
Tried to see if I could isolate the function "fetch_all_data_of()" as a function "my_fetch_all_data_of()" in my own notebook, but then got lost on the dependencies of other parts, it being part of a class definition .....
As the collection is already successful, any chance to convert db to pandas and then throw away all the unneeded parts using panda handling?
If you could give me a hint for a minimalistic subsection of the full HASS-data-detective GitHub code I would appreciate it.
Thanks
from hass-data-detective.
The executed sql query is at
HASS-data-detective/detective/core.py
Line 99 in f861f70
domain
. You can install detective and editable mode and make changes on the fly to the query.
eg try
SELECT entity_id, state, last_changed, attributes
FROM states
WHERE
state NOT IN ('unknown', 'unavailable')
ORDER BY last_changed DESC
from hass-data-detective.
Thank you. I succeeded with the following code after having fetched the database using
the code in attached text file 'code snippet.txt. (sorry editor function <> does not seem to work in this comment box)
note: I had to put the string 'sensor.wlan_switch_energy_power' explicitly into the query, somehow the query string would not allow the variable to be inserted, as in your python class. I could also put two such strings in, then two entities were extracted by query.
note: Also had then the problem that in the sample code GETTING STARTED [Popular entities section]
reading event_data = json.loads(event.event_data)
crashes with message
TypeError: the JSON object must be str, bytes or bytearray, not NoneType
But thanks - the goal of extracting a particular data set works, and then I export it anyway to other pc and python/excel or whatever.
Much appreciated your help, and maybe others read this minimalist solution and workaround and find teh 'code snippet.txt' already helpful as well!
from hass-data-detective.
In my own db domain does exist but appears no longer used:
from hass-data-detective.
Related Issues (20)
- Query event table
- checkout traces
- Problem with View States section in Getting Started : 'HassDatabase' object has no attribute 'fetch_all_data' HOT 6
- Add streamlit UI to allow viewing historical data and make predictions HOT 3
- Add support for !include_dir_* HOT 10
- Tryout plotly time series
- Checkout jupyterlite HOT 3
- OperationalError: no such column: domain HOT 1
- Add query on statistics tables
- CI action failing to to flake8
- TypeError: the JSON object must be str, bytes or bytearray, not NoneType HOT 5
- Raspbian failed HOT 1
- Fix sql api HOT 3
- Add test.db for functional tests
- AttributeError: 'Engine' object has no attribute 'execute' when excuting detective.HassDatabase(db_url) HOT 3
- Updates to schema
- Wrong time format in functions.format_dataframe() output? HOT 2
- Error using Windows: expected str, bytes or os.PathLike object, not HassDatabase HOT 1
- AttributeError: 'str' object has no attribute '_execute_on_connection' HOT 9
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from hass-data-detective.