Execute SQL statements from within HASS

Configuration
1

Is it possible to ‘fire’ SQL statements from within hass (or maybe pyscript integration)?
I already use the SQL Sensor but it’s limited to SELECT and what I need is DELETE.

What I am planning to do is to purge states and events from my recorder (mariadb) that are older than 2 days, but keep some longtime storage entities (like power consumption) for about a year (or more).
I don’t want to use influxdb for longtime storage.
Other home automation solutions allow configuration of retention time on an entity base, but homeassistant only allows includes and excludes for a single retention period.

2

have you found a solution?

3

Sadly not.

4

SQL integration creates a sensor from a query. Maybe we should ask @dgomes and @gjohansson-ST to add a button as well? Or that they add a service to the integration?

5

Hello there,

in my case, I’m using the phpMyAdmin Addon, which lets you create events that execute arbitrary SQL commands.

For example, I’m using this script as an event to purge invalid battery states. Sometimes, a device reports 250% battery charge, which is obviously invalid.

DELETE FROM states WHERE state_id in (
    SELECT state.state_id FROM `state_attributes` attrs
    JOIN states state ON state.attributes_id = attrs.attributes_id
    WHERE (attrs.shared_attrs like '%"unit_of_measurement":"\%"%' and attrs.shared_attrs like '%device_class":"battery"%')
    and (state > 100 or state < 0)
)

This script retreives and then deletes the states that contain a battery percentage and reported above 100 or below 0 percent charge. The event is set to execute this script daily.

1 Like