New custom component: SQL Sensor

huziahmetov · February 16, 2021, 8:20am

i have appdaemon script who put data in to HA sensor. Attributes like:

дата: 16.02.2021
время: 19:32
unit_of_measurement: RUB

But

  - platform: sql
    scan_interval: 180
    queries:
      - name: vtb_shared_sql
        query: "SELECT state,attributes FROM states WHERE entity_id = 'sensor.vtb_shared' and state != 'unknown' and state != 'none' ORDER BY state_id DESC LIMIT 1;"
        column: "state"

gives to me non readable text (broken codepage i assume)

state: 7948.48
attributes: {"\u0434\u0430\u0442\u0430": "16.02.2021", "\u0432\u0440\u0435\u043c\u044f": "19:32", "unit_of_measurement": "RUB"}
friendly_name: vtb_sergey_sql

What i am doing wrong?

dgomes · February 16, 2021, 10:39am

SQL sensor is unable to know the encoding of your attribute string. Sorry

huziahmetov · February 16, 2021, 10:42am

utf-8 as i understand? HA and all components use utf-8, database codepage also utf-8

dgomes · February 16, 2021, 10:45am

HA does, but SQL sensor is not HA sensor, so it does not assume anything from the database based on how HA is configured.

It would require an extra configuration option to support that hint.

Nounours54 · February 17, 2021, 6:49pm

Hi.
I’m using sql sensors against a MariaDB database; it works like a charm, but I was wondering if some extensions were possible…
For example, I was looking for a way of running an SQL request with a “calculated” query in an automation
Something like : select value from table where column=entity_id
where entity_id would come from the trigger part of the automation.

Something like : sql.query = xxxx
sql.execute

Any idea ?

Best regards

rvderm · February 18, 2021, 10:26pm

Hi,

Is it possible to run queries returning and binding multiple values.
Something like:

sensor:
  - platform: sql
    db_url: mysql://usr:[email protected]:3307/slimme_meter
    queries:
      - name: DB size
        query: 'SELECT 1 as value, 2 as value2 FROM DUAL;'
        column: "value"
        column: "value2"
        unit_of_measurement: kB

Above configuration will only return 2kB.

Thanks,
Richard

dgomes · February 18, 2021, 10:48pm

In theory it is possible, but not currently.

You can open a PR

dgomes · February 18, 2021, 10:49pm

it’s a sensor… you can only have 1 value, since you have 2 keys (column) it will only consider the last declared (value2)

rvderm · February 18, 2021, 11:45pm

Ah yes of course, now I understand,
Thank you very much Diogo!

DavidFW1960 · February 19, 2021, 12:14am

Thought I’d try this. I use a mariadb
Getting this error:

2021-02-19 11:08:42 ERROR (MainThread) [homeassistant.components.sensor] Error while setting up sql platform for sensor
Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/helpers/entity_platform.py", line 198, in _async_setup_platform
    await asyncio.shield(task)
  File "/usr/local/lib/python3.8/concurrent/futures/thread.py", line 57, in run
    result = self.fn(*self.args, **self.kwargs)
  File "/usr/src/homeassistant/homeassistant/components/sql/sensor.py", line 63, in setup_platform
    sess.close()
UnboundLocalError: local variable 'sess' referenced before assignment

Config:

  - platform: sql
    db_url: secret! sqlurl
    queries:
      - name: DB size
        query: 'SELECT table_schema "database", Round(Sum(data_length + index_length) / 1024, 1) "value" FROM information_schema.tables WHERE table_schema="homeassistant" GROUP BY table_schema;'
        column: "value"
        unit_of_measurement: kB

url in secret:

sqlurl: mysql://hassmaria:my-password@core-mariadb/homeassistant

What am I doing wrong here?

Running querie in phpmyadmin seems valid

dgomes · February 19, 2021, 8:40am

HA is having trouble connecting to the database, but you should get an error previous to that… don’t you have more errors in your logs ?

DavidFW1960 · February 19, 2021, 8:48am

No more errors and I can cut/paste the query in phpadmin

dgomes · February 19, 2021, 9:24am

The issue is not with the Query, it’s prior to running the query…

I think I found it:

db_url: secret! sqlurl

should be

db_url: !secret sqlurl

DavidFW1960 · February 19, 2021, 10:18am

Oh BUGGER!!! You are right. Doh!!!

Nounours54 · February 19, 2021, 4:42pm

I thought keyword “column” had to be unique.
It could be a solution for me

Thanks !
Alain

Nounours54 · February 19, 2021, 10:11pm

PR ? How can I do that ?

dgomes · February 19, 2021, 10:30pm

PR = Pull Request

You write the code to support that feature, and it eventually gets accepted into HA

Nounours54 · February 19, 2021, 10:56pm

I used to be a C developer… I’m not familiar with Python. I have almost no time for me : I’m afraid I won’t be able to do that PR during the next 15 years

Thanks for your work.

capstan1 · May 3, 2021, 4:38pm

Hi, I’m trying to get the average time of the day a sensor turns to on during the day using the sql sensor

I treid various things, but all give me errors. Maybe you can help?

- platform: sql
  queries:
      - name: schlafenszeit_filter
        query: "SELECT SEC_TO_TIME(AVG(TIME_TO_SEC(s.`state`))) FROM states s INNER JOIN (SELECT state_id FROM states WHERE entity_id='input_datetime.schlafenszeit_test' AND state <> 'unavailable' ORDER BY state_id DESC LIMIT 2) x ON x.state_id = s.state_id"
        column: 'AVG(s.`state`)'

Thanks in advance!

wigster · August 30, 2022, 10:50am

Hi

Unfortunately I don’t know where to start with writing the query, so this is a plea for help. An example would let me start off on figuring this out.

I would like to create a sensor which would get the value of a chosen sensor from 30 days before. It would have to come from the long-term statistics database and I guess should just return the entry in time closest to the current date time minus 30days.

Thanks!