SQL - How to send UPDATE Command from Home Assistant

Configuration
1

Hello Everyone,

I am looking to update a SQL table, with data from Entities from Home Assistant.

I am not an expert in SQL, or HA, so any guidance on more info would be great.

I created a SQL table in HA using SQL-Lite Web HACS Add On:

I can query the table fine from the SQL Integration, but I am curious how to UPDATE the table.

I can do so from the SQL Lite Web GUI, but am hoping that I can use the state of an entity to update the SQL Table via an automation.

I simply dont know where to start to accomplish this, and have tried searching, but cannot find much to update tables via automation.

If there are any leads onto how i can do this; Automation, etc. That would be greatly appreciated!

Thank You!

2

For as far as I know you cannot update, only read.
There are some exceptions on loading statistics (spook uses this)- but I guess (andunderstand) that HA wants to keep data clean from external influences

3

What, exactly, is it you are trying to automate, or log to the database? Maybe there’s a more “HA way of doing it” than trying to create and update db tables. HA prefers to handle the database writes itself, and leave the user to interact with entities instead.

Edited to add: it should also be pointed out that, directly modifying the HA database if you don’t know exactly what you are doing, can be a great way to break things. If you absolutely must write to a database, consider using the command-line action to write to a separate database with the sqlite3 command.

4

I may be able to assist you if you can be ‘exact’ about what you are trying to achieve.

Many ways to skin a cat…

  • Write fully qualified SQL Queries to a text file and run all of the inserts or updates later
  • Write an App Daemon App that listens to Home assistant activity and updates your database table in real-time
  • Create some template sensors that gather and record the outcomes you are hoping for leveraging solely existing functionality in Home assistant

This thread may also end up going somewhere and help you

5

I can confirm that writing even a single thing into HA’s sqlite database definitely and immediately corrupts that DB and HA creates a new, blank one (i’ve done that!). In fact, even solely connecting to live HA’s db with third party software causes db to crash, you don’t even need to write into it.
Writing into MariaDB or correcting existing data survives, though (i’ve done that, too).
But, that said, Kami said that he created a new, blank DB, if i understand correct, so he doesn’t want to write into HA db but into that new blank one…

6

Thank You for the Reply

7

Hello, and thanks for your Reply.

Mainly just messing around. I wanted to have my own table in HA SQL i could reference.

I get a Steps Tracker per day from fitbit Integration.

I was hoping to setup an automation to have the steps saved on a table. Everyday UPDATE steps to steps + (Insert Steps from Fitbit Here) to keep track of total steps.

Maybe track the total per week, month, etc.

I am more comfortable with the SQL Queries, i get alittle confused with the Entities and Helpers.

Thought it would be simple, added a table via SQLLite but hit a wall when i realized i couldnt UPDATE.

As you all mentioned, looks like i am playing with fire haha.

Thank You.

8

Hello,

Something alittle, simpler…

Mainly just messing around. I wanted to have my own table in HA SQL i could reference.

I get a Steps Tracker per day from fitbit Integration.

I was hoping to setup an automation to have the steps saved on a table. Everyday UPDATE steps to steps + (Insert Steps from Fitbit Here) to keep track of total steps.

Maybe track the total per week, month, etc.

I did create a table using SQLLite, but as you all mentioned, i may be playing with fire haha.

Ignoring safety - is there a way to update the table through the text file like you mentioned?

I will take a look at your link.

Thanks for the Reply

Thank You.

9

Hi There,

So i did create a new table on the existing HA Database…

I see now that maybe wasnt the best idea.

I was hoping to use that table to manipulate data via a sql command, and store it there.

I can do so via SQLLite, just unsure how to UPDATE without using SQLLite GUI.

Thanks for your reply.

10

If you have integrated the Fitbit into home assistant already, the the data is probably already being stored or accessible through homeassistant.

I expect that all you need to do to achieve your goal to “track the total per week, month, etc.” is to ensure that the data gets stored in long term statistics (which are retained indefinitely)

I found this link that has additional information on actually getting the Fitbit readings available in HASS

Also (from the same post) this clearly indicates that steps (and calories etc.) can be brought into Home Assistant

I think your goal should be to get the data into Home Assistant (as sensors) using the above methods and then to make a nice page in the UI that displays your long term statistics

11

Okay neat!

I had no idea about the Statistics card. I am able to use it to get the total steps this month for example.

image
image1180×900 28.7 KB

This is probably a better direction, I assumed i would have to update a “Total Steps” Entity or Helper or something.

Never used Statistics card before!

I will keep looking into it. It looks like everything is limited to current week, month, etc.

I am going to see if there are any other Statistics cards in HACS as well.

Thanks!

1 Like