MARIADB: Automatically delete the data of a single sensor

Configuration
1

Hi,
through phpadmin, I can connect to the MariaDB database and delete the data of a sensor with this command: DELETE FROM homeassistant.states WHERE ʻentity_id` LIKE ‘% sensor.cputemperature%’ LIMIT 250

Would it be possible to automate this command with HomeAssistant (HASSOS)?

Thanks

2

Yes use a shell_command to run the appropriate mysql command.

Then make an automation to run the command periodically.

3

Or if you just don’t care about that sensor’s data, exclude it from the recorder…

4

Thanks for the reply.
Would you have some targeted examples, using MariaDB?
I have never used shell commands

5

Thanks for the reply,
I already use recorder, but there are some sensors that I would like to keep for a long time, others delete them every day, others leave the last 250 results

6

Your sample deletes 250 records, not keeps them. Maybe it is just a sample query and not the real one, but this deletes 250 records based on the order they show up in the result set. Since there is no order by, it might be the newest records that get deleted…

7

Yes,
sorry mine was an example query, the suitable one according to the sensor.
What should I do to delete older data?
It would be fine, indeed it would be better, if you could indicate, for example, the last 3 days

8

Something like

echo "DELETE FROM homeassistant.states WHERE ʻentity_id` LIKE ‘% sensor.cputemperature%’ LIMIT 250" | mysql --defaults-extra-file=filename databasename

Where filename is a file with the following

[mysql]
user=user
password=password
9

https://dev.mysql.com/doc/refman/8.0/en/delete.html

10

Thanks,
I add ORDER BY to the code and try (via PhpAdmin)

In the meantime I have found this solution:

import sqlite3

DB_PATH = ‘/path/to/home-assistant_v2.db’
ENTITY_IDS = [‘sensor.my_sensor1’, ‘sensor.my_sensor2’]

conn = sqlite3.connect (DB_PATH)
c = conn.cursor ()
for eid in ENTITY_IDS:
c.execute (“DELETE FROM events WHERE event_data LIKE?”, (’% \ "entity_id ": \ “’ + eid + '”%’,))
c.execute (“DELETE FROM states WHERE entity_id =?”, (eid,))

conn.commit ()
conn.execute (“VACUUM”)
conn.close ()

I have to see if it can also be adapted to the MariaDB DB (not sqlite3)

11

Or you could just take the answer I gave you.

12

You’re right, sorry, but I didn’t understand if the “echo” DELETE FROM … "command can be used as a shell command or if it should be used in phpadmin

13

That is a shell command. The echo " " | passes the command in quotes to mysql commandline.

PS I would have to try it to make sure I have it exactly right.

14

Yours was already a solution to be integrated into HASSOS, sorry I still didn’t understand :slight_smile:

I saw that the shell command can be reported directly in the configuration.yaml

Example:
User: homeassistant
Password: MyPass
Database: homeassistant
recorder: db_url: mysql://homeassistant:MyPass@core-mariadb/homeassistant?charset=utf8

I should report this shell command:
echo “DELETE FROM homeassistant.states WHERE ʻentity_id` LIKE ‘% sensor.cputemperature%’ LIMIT 250” | mysql --defaults-extra-file = homeassistantMyPass homeassistant

Quite right?