Add support for query_template to SQL integration

Feature Requests
,
1

The SQL Integration currently supports use of the value_template configuration variable to allow definition of a template to extract a value from the payload.

However, it does not currently support the use of a query_template configuration variable to allow for a dynamically defined SQL query.

The use case I have is to be able to lookup a friendly text for error codes. Normally, I would use a template sensor to achieve this, but there are two concerns with doing this:

  1. We embed data in our configuration files. That isn’t too bad, but it does mean that a restart is required if a software update on your device has resulted in new or changed error codes and/or descriptions. It’s like a form of hard coding.
  2. When the number of error codes is large, the resulting configuration will become large and somewhat unwieldy and difficult to maintain. In my use case there are 229 possible error codes.

Hence, I’m wondering if it would be possible to add template support to the query configuration variable in the SQL integration please. I think it would be very powerful and a valuable capability.

An example of the type of configuration that would then be allowed (no matter how many trip codes exist) is below:

  - platform: sql
    db_url: sqlite:////local_db/local.db
    queries:
      - name: Trip 0 Text
        query: "SELECT * FROM lookup WHERE  Code = {{states('sensor.dx_trip_0')}};"
        column: 'Friendly Text'

To achieve the same outcome through using a template sensor would result in configuration like this:

      dx_error_status_text:
        friendly_name: "Trip 0 Text"
        entity_id:
          - sensor.dx_trip_0
        value_template: >-
          {% if is_state('sensor.dx_trip_0', '0') %}
            No error
          {% elif is_state('sensor.dx_trip_0', '10') %}       
            Solutions Module operating system error
          {% elif is_state('sensor.dx_trip_0', '20') %}       
            Real-time clock error
          {% elif is_state('sensor.dx_trip_0', '30') %}       
            IP54 cooling fan power supply fault
          {% elif is_state('sensor.dx_trip_0', '40') %}       
            Drive to Solutions Module interface error
          {% elif is_state('sensor.dx_trip_0', '50') %}       
            Building Automation Network error
          {% elif is_state('sensor.dx_trip_0', '74') %}       
            Solutions Module PCB over-temperature
          {% else %}
            Unknown
          {% endif %}

Please consider this and vote for it if you see value.

2

Had that idea a while ago.

1 Like
3

It is a very good idea. It’s a shame that the developers don’t want to implement this idea.

4

Please add this feature :slight_smile:

5

Yes, I need this feature too!

6

I’d like to have it too, currently struggeling finding an alternative solution as I do need to fire a dynamic sql based on another sensor value.

Please add. :slight_smile:

7

Same situation here. I’m trying to look up a friendly base station name from a BSSID string, and I can’t find any examples of dynamically constructing the query based on other sensor values. Was this ever resolved?

FWIW, here’s what I’m currently using. It’s a little more concise than using a whole bunch of if statements. (Not sure my BSSID’s could be used maliciously, but I’ll mask parts of them with ** anyway.) It returns the friendly name if it’s on the list, or just the BSSID string if it isn’t.

        value_template: >
          {% set apid = (states('sensor.redmi_note_8_pro_wifi_bssid')) %}
          {% set bssidmapper =  {
            '74:da:88:**:**:2e' : 'HOME: Main Deco 2.4GHz',
            '74:da:88:**:**:2f' : 'HOME: Main Deco 5GHz',
            '74:da:88:**:**:6e' : 'HOME: Upstairs Deco 2.4GHz',
            '74:da:88:**:**:6f' : 'HOME: Upstairs Deco 5GHz',
            '74:da:88:**:**:0e' : 'HOME: Conservatory Deco 2.4GHz',
            '74:da:88:**:**:0f' : 'HOME: Conservatory Deco 5GHz',
            'e4:95:6e:**:**:64' : 'HOME: VPN Router',
            '8c:19:b5:**:**:96' : 'HOME: BT Router'
          } %}
          {{ bssidmapper[apid] if apid in bssidmapper else apid }}
1 Like
8

Why isn’t this a thing yet? It’s so basic… without, there’s no way of doing parametrized queries :frowning:

9

Feel free to make a PR to add it

10

Voted for it.
I have the same issue over at

As I’m kinda new to this feature request stuff…
How are the chances this is getting looked at?

With my issue, I’m unable to find a hot fix in a lengthy config unfortunately. I’m relying on a database query :cry:

11

My workaround is to use external program (or script), which retrives the data from HA’s database. This program exists within HA as a command line sensor, which accepts templates, so you can pass SQL parameters as command line args, e.g.:

- sensor:
    name: "Jogging, distances and times, summary"
    command: "/config/jogging_summary {{ states('input_datetime.start_date') }} {{ states('input_datetime.end_date') }}"
    scan_interval: 315360000 # disable automatic updates of this sensor

“/config/jogging_summary” writes the results to std output. That goes to sensor’s state. If the result is too long, you can put it into an attribute.

The hardest part was to make the program/script. I had no luck with Python in HA, so I ended up putting my program into Proxmox host and calling it via ssh.