File size custom component

Share your Projects! Custom Integrations
1

I’ve published a custom component adding a sensor which displays the size of a file in MB. Instructions in the repo below. If you are unclear how to use a custom component, please check the docs.
Cheers

image
image774×796 47.7 KB

11 Likes
2

Working great here, thanks!

I have a lot of sensors which update frequently wich makes my database gets filled rather quickly. Therefor I stop hass, delete the DB-file and restart Hass once it gets larger dan 1Gb. It would be great if I could automate this based on the value of this sensor.

Does anyone know how this can be done? If I don’t stop Hass before deleting the database, the db gets corrupted (which is not strange).If I stop Hass, obviously automations also don’t work.

3

i seem to remember reading about a service call you can make to clear the db. if it does in fact exist then you could certainly create an automation. i just use the recorder purge in my config.yaml

recorder:
  purge_interval: 1
  purge_keep_days: 10

that seems to keep my db at a reasonable size.

4

@sparkydave I’m using purge as well but it doesn’t seem to make a lot of difference.

recorder:
  purge_interval: 1
  purge_keep_days: 5
  exclude:
    entities:
      - sensor.time
      - sensor.since_last_boot_templated
5

You could presumably use a shell command to shutdown HA and delete the db

1 Like
6

Maybe you do not need to have some sensors / groups logged? Then you can use exclude or include :slight_smile:

7

Make a bash script with the commands you would use in order, and launch the bash script from shell command.

I use this method for a few things, have a look at my repo (the bash scripts are in extras/bash_scripts and the shell commands are called from the packages/virtual_devices/maintenance package)

link removed

Hope this helps :+1:

2 Likes
8

@anon43302295 thanks, lots of usefull stuff there!
*backing up my config to Dropbox now for instance… :slight_smile:

1 Like
9

If I understood the code, this works only with the standard SQLite DB and not with a full MySQL installation. :roll_eyes:

10

Correct. Actually this code works with any file. Is it possible to use sqalchemy to query the db size in your case?

11

Hi,
Ive tried something like this recently using a command line sensor to get the size of the MySQL db file. However, the file size is not necessarily growing linearly with the stuff it stores. The size would remain the same for days as MySQL apparently allocates in bigger blocks.

My original goal was to determine the size to see the growth rate over time to find out how it corellates to the recorder settings. Wanted to tune this a bit because query times for HA’s history/logbook are just unbelievably slow…

12

OK updated this to work with any file, and now the path the file is displayed as an attribute.

13

Here’s a python script that i use as command_line sensor.

import pymysql.cursors

mysql_host = '127.0.0.1'
mysql_user = 'user'
mysql_pass = 'pass'
mysql_db   = 'homeassi'

db = pymysql.connect (host=mysql_host, user=mysql_user, passwd=mysql_pass, db=mysql_db)
cursor = db.cursor(pymysql.cursors.DictCursor)

sql = """SELECT table_schema '%s',
         sum( data_length + index_length ) / 1024 / 1024 "database size",
         sum( data_free )/ 1024 / 1024 "free space"
         FROM information_schema.TABLES""" % (mysql_db)

cursor.execute(sql)
result = cursor.fetchall()
db.close()

db_size = float(result[0]['database size'])
free_space = float(result[0]['free space'])

real_size = db_size - free_space
print(real_size)

This prints only the first value from the following query where you can see the free space.

SELECT table_schema "homeassi",
    sum( data_length + index_length ) / 1024 / 1024 "database size in MB",
    sum( data_free )/ 1024 / 1024 "free space in MB"
FROM information_schema.TABLES

EDIT:
Updated the script, so it should print the real size.

1 Like
14

Very useful, thanks!

15

Can you explain adding a python file to “your config”? Does this mean that one needs to drop the python file in the /config/ dir or declare it somewhere in the configuration.yaml file?

16

You add the script to a python_scripts directory that you make in your .homeassistant directory, then add

python_script:

To your configuration.yaml, you can then run the python scripts from automations or scripts in homeassistant.

17

Being somewhat dense here… In Hassio I would create a /config/python directory and then just add the include in my configuration.yaml. No need to call out the specific script by name in the configuration file as Hassio will just slurp all of the .py files in the directory into it brain.

18

I don’t know where it would go in hass.io, but basically it should be in the same directory as your www folder.

And you don’t put anything after python_script: , you just call them from automations.

If it helps, my GitHub link is further up the page. The python_script declaration is in my configuration.yaml, the directory is in the correct place relative to the other files and it has two scripts in it.

Then the automation I use them both from is in packages/system/device.yaml

19

@anon43302295, @mattlward, are you talking about my script above?
It is called as a command_line sensor in HA.
BTW, i monitored the values since yesterday, and like @jo-me said, mysql allocates bigger blocks and the result go up and down.

20

Added last_updated attribute