New custom component: SQL Sensor

Development
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?

41

Try duplicating the configuration, should work

42

Why haven’t I tried that. Of course that works. Thank you @dgomes :slightly_smiling_face:

43

Im getting error in last versions is that normal?

44

What error are you getting?
I just added this sensor and am getting an error no such function: pg_database_size.
I am using PostgresSQL - and followed the SQL sensor component instructions.

45

Can you run a query on your database directly ? (using a console)

46

Hi and thanks for the great component,

It works great with my postgresql database, but with SQL Server if fails. I beleive it’s due to the coded in LIMIT 1 which does not work in SQL Server. Instead of LIMIT 1 SQL Server may use “SELECT TOP 1 columns FROM database”.

Is this something that can be changed at the integration end at some point in time? If you want me to post this on github please let me know, but I would need some guidance as to where as I am not familiar with the platform.

47

Was not aware of SQL Server not supporting LIMIT, there is a hackish work around:

You you manage to fit “LIMIT” string into the query it will not put in the " LIMIT 1;" for you.

This will probably work:

SELECT TOP 1 columns FROM database; /* LIMIT */

48

Thanks, works like a charm!

49

I get this error at setup:
ModuleNotFoundError: No module named ‘MySQLdb’

do I have to install something? HA 101.3, Raspberry Pi, virutal environment

50

hi everyone, i’m trying to create a sensor from an sqlite query but it wont work and i don’t know why
here is my sensor config


sensor:
  - platform: sql
    db_url: sqlite:////config/home-assistant_v2.db
    scan_interval: 10
    queries:
      - name: test
        query: "SELECT substr(attributes,147,4) FROM states WHERE domain LIKE 'climate' ORDER BY created DESC LIMIT 1;"
        column: "attributes" 
        unit_of_measurement: "#"

and here is my sqlite query


sqlite> SELECT substr(attributes,147,4) FROM states WHERE domain LIKE 'climate' ORDER BY created DESC LIMIT 1;
17.5

it return the temp value i’m looking for but why it didnt succeed with my sensor ?

i’ve tried db_url: sqlite:////home-assistant_v2.db or /home-assistant_v2.db or /usr/share/hassio/homeassistant/home-assistant_v2.db path but none of them seems to work and all are unknown as sensor value

is there any other way to make this working ?
thx
regards

51

i finaly succed to make it work,
i was pretty sure that the issue was caused by my substring but the sqlite db engine seem to be ok with it so i 've been trying to format the result in a way it could be handled in some automation thereafter
and in fact , i’ve succeed to format it correcly with something like

{% set temp = states('sensor.test') |from_json %}
{{ temp.temperature }}

but in fact the issue was my column value that should be

column: "substr(attributes,147,4)"

instead of

column: "attributes"

so here is my complete sensor configuration

sensor:
 - platform: sql
   db_url: sqlite:////config/home-assistant_v2.db
   scan_interval: 10
   queries:
     - name: test
       query: "SELECT substr(attributes,147,4) FROM states WHERE domain LIKE 'climate' ORDER BY created DESC LIMIT 1;"
       column: "substr(attributes,147,4)" 
       unit_of_measurement: "#"

anyway it work so i hope it’ll help some you