New custom component: SQL Sensor

Development
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

52

This seems to have broken on the latest upgrade to Ha 0.114 today

All of the sensors from

sensor:
 - platform: sql
   db_url:
53

No one has touched this integration in a long time.

Any more details ? Error log ?

54

My sql sensors are working without a problem with 0.114.0.

55

hmm odd.

I’ve opened this:

Seems to be timing out

56

dennis can you give an example of the sql queries you are using?

Thanks

mariadb logs:

2020-08-13 16:33:14 53 [Warning] Aborted connection 53 to db: 'homeassistant' user: 'homeassistant' host: '172.30.32.1' (Got an error reading communication packets)
57

Sure.

  - platform: sql
    db_url: !secret mariadb_url
    queries:
      - name: database_size
        query: 'SELECT table_schema "database", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "value" FROM information_schema.tables WHERE table_schema="home_assistant" GROUP BY table_schema;'
        column: 'value'
        unit_of_measurement: MB
58

Thanks.
I’m using custom views that had been working fine.

Now all the entities show:

Entity not available: sensor.master_bedroom_heating_cost

view (Heating_Downstairs is the name of the view)
Have about 10 and been working perfectly

      - name: Heating Downstairs OnTime
        query: 'SELECT sum(Duration) as Duration  FROM homeassistant.Heating_Downstairs'
        column: 'Duration'
        unit_of_measurement: 'minutes'

If I run them directly in the DB they work fine.

59

I’ll try formatting the names without spaces
so

  • name: Heating Downstairs Cost
    becomes
  • name: heating_downstairs_cost
60

nope didn’t work. must be a new issue with creating entities with a custom sql view!!
not good. none of my enitties are available