SQL integration: limit the query to once a day

[JazekerXX]

hi, there is a nice integration for SQL to do a query on the database. the only disadventage it has is that the query runs continuously and in result the cpu usage goes up. i tested it with a query for my database size, and in the screenshot below you see the difference in cpu usage.

so does anyone know how we can limit the execution to once a day for example?
i think it’s just missing from that integration, right?..
or could it be possible to do a query in an automation?

[digdilem]

I can think of a way, but it might not be the best way.

Create a dummy sensor in HA to record the value,

Write a script (in bash, perl, python, whatever) which is run once a day by cron, or even by HA via automation and a timer. That script connects to the database, runs your query, collects the answer and sends that to HA to update the dummy sensor.

That would work okay, but the DBA part of me would prefer to either investigate why the query is causing so much load on the database. That might be possible to reduce that through tweaking the query, the table’s schema, or the server’s database settings.

Alternatives: Put the database on another machine if you have one or throw hardware at the problem and upgrade from an rpi to a more powerful server.

[JazekerXX]

@digdilem

Write a script (in bash, perl, python, whatever) which is run once a day by cron, or even by HA via automation and a timer. That script connects to the database, runs your query, collects the answer and sends that to HA to update the dummy sensor.

Hi Digdilem,
thanks for your reply!
you describe a lot of things that i have not used yet, so after some searching i found that i could use an automation to run a python script. like you suggested.
the python script will be available as a service after you put the .py file in the folder /config/python_scripts
described here: Python Scripts - Home Assistant

for the content of the .py file i found this thread:

Accessing database from a python script

Thanks robmarkcole! I went for the first option and it worked! My configuration.yaml: shell_command: script1: "python3 /config/python_scripts/script1.py" My /config/python_scripts/script1.py: import MySQLdb logfile = open("script1.log","w") database = MySQLdb.connect("core-mariadb","MY_USER","MY_PASSWORD","homeassistant") cursor = database.cursor() query = """select DATE(created) `Day`, ROUND(SUM(state)/1000, 1) `Consumption` from states where entity_id = 'sensor.energy_counter' grou…

maybe that’s something i could use?
not sure why they put this in configuration.yaml, because the activation is by the service.

shell_command:
  script1: "python3 /config/python_scripts/script1.py"

but after the script has run, how do we get the result in an entity?

the query i did is:

SELECT SUM("pgsize")*0.0000009999999999999999 as "size"  FROM "dbstat";

it takes some time to process, but i think that’s normal because it has to calculate the size of all files and folders. and did this continuously. the cpu has still enough power, but if this is done once a day it’s certainly no problem.

[parautenbach (Pieter Rautenbach)]

Turn off the automatic polling (every 30 sec) under the SQL integration’s settings. Then create an automation for your preferred time and call the homeassistant.update_entity service on your sensor.

[IOTNinja (Maya Chawala)]

Where is this option found? I’m looking in integrations\sql but I’m not seeing an option to disable the constant polling.

[JazekerXX]

@parautenbach

Turn off the automatic polling (every 30 sec) under the SQL integration’s settings. Then create an automation for your preferred time and call the homeassistant.update_entity service on your sensor.

YES! this is the solution i was looking for. so simple and it still uses the SQL integration which is very easy to use. thanks!

@IOTNinja just open the SQL integration, press the 3 dots, system settings.

and the automation looks like this:

- alias: 'Update entity'
  trigger:
    - platform: time
      at: "05:00:00"
  action:
    - service: homeassistant.update_entity
      data: {}
      target:
        entity_id: 
          - sensor.database_size

[IOTNinja (Maya Chawala)]

Thanks!