New custom component: SQL Sensor

Development
21

well that select will not return a column with the name “value” :slight_smile:

currently can’t access a postgresql server, but please do the query through the CLI or any GUI and check the name of the column which contains the size.

22

Another option that may help.
Cheers

1 Like
23

@Martinvdm try this way:

  - platform: sql
    db_url: postgresql://user:user@localhost/dsmrreader
    queries:
    - name: dsmr_dbsize2
      query: "SELECT pg_size_pretty(pg_database_size('dsmrreader'));"
      column: "pg_size_pretty"
      unit_of_measurement: MB

EDIT:
The query with pg_size_pretty returns the value as a string, this way you can’t have a nice chart or make value comparison. Instead you can query this way to have a number:

  - platform: sql
    db_url: postgresql://user:user@localhost/dsmrreader
    queries:
    - name: dsmr_dbsize2
      query: "SELECT (pg_database_size('dsmrreader')/1024/1024) as db_size;"
      column: "db_size"
      unit_of_measurement: MB
24

Great feedback @namadori!

Can you update the component documentation in the website with that example ?

25

Sure. I’d just wait for a feedback from @Martinvdm, I tried the query in pgAdmin but not the whole configuration in HA.

26

Wow that worked like a charm! thanks. Before the SQL Sensor was introduced, i was using a command line SH script, like this:
sudo sudo -u postgres psql user -t -c “SELECT pg_size_pretty( pg_database_size(‘databse’) );”

This was working, but now with this SQL Sensor i can integate the code within yaml. Extra advantage is i can share the yaml code and keep the string in secret.

thanks!

27

Thanks for the feedback. Created PR to update the docs.

1 Like
28

Fantastic new sensor !

I’m trying to use it to count the events in my database :

- platform: sql
  db_url: sqlite:///home/homeassistant/.homeassistant/home-assistant_v2.db
  queries:
    - name: HASS database events
      query: 'select count(*) from events as number_events;'
      column: number_events

sqlite> select count(*) from events as number_events;
108227
sqlite>

but I don’t get a column with that SQL request, am I doing this wrong ?

Also I was wondering which “unit_of_measurement” I should pick in order to get a graph ? I don’t really want to use something like Kb as it’s not a Kb, it’s just a … number.

29

query is wrong:

SELECT count(*) number_events FROM events;

you can use ‘#’ as the unit_of_measurement

1 Like
30

Thanks ! That works.

- platform: sql
  db_url: sqlite:///home/homeassistant/.homeassistant/home-assistant_v2.db
  scan_interval: 120
  queries:
    - name: HASS db events
      query: 'SELECT count(*) number_events FROM events;'
      column: number_events
      unit_of_measurement: "#"
    - name: HASS db states
      query: 'SELECT count(*) number_states FROM states;'
      column: number_states
      unit_of_measurement: "#"
1 Like
31

Just one question: May i add manually sql.py script inside custom_components folder? Or is it already inside HASS?

I get always this error:
“Error executing query SELECT table_schema “database”, Round(Sum(data_length + index_length) / 1024, 1) “value” FROM information_schema.tables WHERE table_schema=“hass” GROUP BY table_schema LIMIT 1;: (sqlite3.OperationalError) no such table: information_schema.tables [SQL: ‘SELECT table_schema “database”, Round(Sum(data_length + index_length) / 1024, 1) “value” FROM information_schema.tables WHERE table_schema=“hass” GROUP BY table_schema LIMIT 1;’] (Background on this error at: http://sqlalche.me/e/e3q8)”

Where am i wrong?

32

sensor.sql has been part of HA since release 0.63

That query applies only to MySQL databases. From the error message you are using sqlite.

Local sqlite database sizes are better handled through https://www.home-assistant.io/components/sensor.filesize/

1 Like
33

So i can delete sql.py from HASS folder?

34

from custom_components ? yes

35

how often should the queries be run? is this settable? my install seems to only update on reboots?

  - platform: sql
    db_url: mysql://user:password@ip/hass
    queries:
      - name: HASS DB size
        query: 'SELECT table_schema "database", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "value" FROM information_schema.tables GROUP BY table_schema;'
        column: 'value'
        unit_of_measurement: MB
36

It should update every 30seconds

You should also be able to change the value:

37

This could be an unsung hero among sensors!! I have a ton of SQL data that I want to present or alert on

I’m using it to detect if my IoT air quality device hasn’t sent data for a set period of time

I had issues on the install - this fixed it

38

Anyway to make the credentials in the url secret?

Can they be separated out as sensor parameters?

39

Just make the all db_url a secret (that’s what I use)

1 Like
40

The component works like a charm. Is it possible to access two different databases?