New custom component: SQL Sensor

Development
17

I do understanding the reason for a custom component, but i do the same with just a command line sensor to a script with a single line mysql query.

18

A component works on every platform and installs dependencies. For a command line you need first to install the right client in the environment, and in containers (like Hassio) it could be difficult for beginners.

1 Like
19

Happy to report that the SQL sensor will be part of the next official release :slight_smile:

3 Likes
20

nice sensor component!
Was trying to get this working with PostGreSQL, but doesn’t seems to work.

When using a commandline sensor to and sh script it works, but using the sensor i can’t get it working.

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

error:
"Could not locate column in row for column ‘value’

Done some changes with " and ', but couldn’t get it working.

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: