Import Data via WebAPI manipulating HA's SQLite-DB

[GregorEstebanCardosoSchiller]

Discussed in #17

^{Originally posted by loeffelpan September 10, 2023}

Second is a powershell script to create sql statements with api data from senec for adding that data to your energy dashboard.
You can get the script here. new Script here.

So I get from the script the following output, which makes no sense for me, can you assist? To me it seems, that it finds everything (mode ADD), but in the end it says the state or sum is not in db? @loeffelplan

[loeffelpan]

I will take a look later this day. Sorry for my late response.

[loeffelpan]

You provided "$first" and "$last" with hours. But as we're trying to replace whole days with data from api, this should be just dates like "01.08.2021" and "01.11.2023".
I added some error handling for this case and improved logging in tha latest revision of my script. Please try again.

PS: You may wait a couple of days, as I'm working on a version which pulls hourly data from api and not just whole days.

[GregorEstebanCardosoSchiller]

Hi, I provided the dates in different versions but the function ToUniversalTime creates these timestamps. Same results, always.

[loeffelpan]

I have to use universal time (UTC) for calculation, because its used in HA's database.
But you have to set DateTime-objects with 00:00:00 as time. As the timestamps in the WARNING are not modified, I can see in you screenshot that you used 01.08.2021 02:00:00 and 01.11.2023 01:00:00.
This is not valid. Lastest version of the script is validating that.

[GregorEstebanCardosoSchiller]

Tested the new script, here the output: (FYI the unix timestamp is translated Wed Nov 01 2023 23:00:00 GMT+0100 (Mitteleuropäische Normalzeit)

[loeffelpan]

Great. The script is looking on data for 23:00 at the day provided as $last. These are base values for any calculation. Please note the comments in the config part of the script.

So there is no data for 01. Nov 23:00 in your DB (copy). You should take a look at your energy dashboard or directly in the db (e. g. with any SQLite Browser).

[GregorEstebanCardosoSchiller]

saw it....still digging, got the syntax now ^^

[GregorEstebanCardosoSchiller]

Okay, okay, so now its working, the output is an sql that shows a lot of inserts. Am I assuming correct that I have to trigger this url into the database myself?

[loeffelpan]

Correct. The script creates one DELETE and many INSERTS for the "statistics" table.
And you have to apply them by yourself.

[GregorEstebanCardosoSchiller]

after the corruption of the database etc. I recognized, that the home assistant database is an insecure safespace :-D

Furthermore the info for "make a backup when HA core is stopped because then the database is not inconsistent when copied" would have been helpful.

BUT the conclusion and question is: Wouldn´t it be better if the imports target were the influx db?!

[loeffelpan]

What do you mean by "influx db"?
It is not intended to apply changes to the database copy and replace the original database. See my latest post.

[GregorEstebanCardosoSchiller]

So I deleted most of the entries for debugging and better output and tried to read the query into the database. This is what I am getting:

the sql:

[loeffelpan]

I found an error and will fix it later today.
But the message you got, does not make sense.

[GregorEstebanCardosoSchiller]

I agree, and when I do an insert directly via text, not from the file, everything works fine. Seems to be like a line break in the file? Have to do a bit of research for windows and line breaks via powershell and/or sqlite...

[GregorEstebanCardosoSchiller]

So it seems, that your double quotes "" are causing the problem. I changed them with single quotes and now I get this:

[loeffelpan]

Thats the error I found this morning.
The values for ˋstart_tsˋ and ˋcreated_tsˋ are missing at the end of my statements. I will fix it next few hours.
Therefore you got those constrained errors. But I'm wondering about your problem with double quotes. These are working fine in my case.

[loeffelpan]

Fixed the error I found and also changed to no quotes.
As the data type of those fields is FLOAT it should be fine without quotes.
Please note that I had to recreate the gist. You'll find the script here. Please change the link in your main post. I also changed it in my original post in #17 .

[GregorEstebanCardosoSchiller]

Changed the Script, please debug the Login Part to senec. You also altered double quotes to singles. This caused an error with the login into senec API.

Working:

$headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
$headers.Add("Authorization", "$token")

Not Working:

$headers = New-Object 'System.Collections.Generic.Dictionary[[String],[String]]'
$headers.Add('Authorization', '$token')

So one database corruption due to: the update (dunno what went wrong there):

UPDATE statistics SET sum = state - -0.001310285180806476 WHERE metadata_id = 460;
UPDATE statistics SET sum = state - -0.0006847286224361 WHERE metadata_id = 459;
UPDATE statistics SET sum = state - -0.0012807559967034 WHERE metadata_id = 482;
UPDATE statistics SET sum = state - 0.003149552978573927 WHERE metadata_id = 461;
UPDATE statistics SET sum = state - -0.001967207193377044 WHERE metadata_id = 462;
UPDATE statistics SET sum = state - -0.0021222996711744 WHERE metadata_id = 483;
UPDATE statistics_short_term SET sum = state - -0.001310285180806476 WHERE metadata_id = 460;
UPDATE statistics_short_term SET sum = state - -0.0006847286224361 WHERE metadata_id = 459;
UPDATE statistics_short_term SET sum = state - -0.0012807559967034 WHERE metadata_id = 482;
UPDATE statistics_short_term SET sum = state - 0.003149552978573927 WHERE metadata_id = 461;
UPDATE statistics_short_term SET sum = state - -0.001967207193377044 WHERE metadata_id = 462;
UPDATE statistics_short_term SET sum = state - -0.0021222996711744 WHERE metadata_id = 483;

It seems that the Inserts worked....but it also seems, that they are not in the database, also after restarting HA:

Just saw, that the database got corrupted again....puh...trying to use the formerly "copied live database for test issues with successfully imported senec data"

[GregorEstebanCardosoSchiller]

So a strict reimport of the database seems to be an issue...after stopping HA and copying the "old" db, it always declines the old one as corrupted and creates a blank new one...puh...

[loeffelpan]

I will take a look to the quotes in the logon part and the update statements. Maybe the caculatiom (sum - x) requires quotes. Thanks for that feedback.

[loeffelpan]

Thank you for your feedback. Just updated the script and fixed the quotes for the login part. All other quotes (or not quotes) were fine.
I assume that your corrupted database was only due to the reimport of a modified database copy.

I tried to clarify the intended procedure in the latest version of the script.
The database copy is only for reading. You have to apply the statements to the database of your running homeassistant instance.
Please note that this will not alter any sensor of the integration. Just statistic data will be added to be shown on the energy dashboard.

Let me know if it's working for you now.

PS: Please check the script link in your initial post. You linked to a raw file of a specific revision and not to the full gist.

[loeffelpan]

Any news?
I would like to hear if its working.

[GregorEstebanCardosoSchiller]

Hi, got some Govee/Hue/LED Synchronization for Christmas on the todo - did not have the time. BUT anyhow I lack the motivation since I understood that not the webapi sensors, but the statistics are filled with data. I thought I could migrate the data into grafana and influxdb someday. Nobody uses the statistics in home assistant.

[loeffelpan]

You understood correctly.
And I just like my whole data in homeassistants energy dashboard, which uses statistics.

You may adopt the script and update "states" table instead of "statistics" to match your case.

Can you please correct the link in the first post and link the whole gist instead of the raw file?