Add support for query_template to SQL integration

[bluebag (Brad)]

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.

[VDRainer]

Had that idea a while ago.

Recommendation for custom SQL query setup

Good point! I think this would be a great enhancement for @dgomes’s SQL Sensor. ‘Allow templates in sql querys’ Something like: query: "select name from caller_id_table where number = {{ state_attr('sensor.modem_callerid', 'cid_number') }}"

[booroondook]

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

[Snakuzzo]

Please add this feature

[paolomorabito (Paolo)]

Yes, I need this feature too!

[ghassan (Ghassan Christensen)]

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.

[DivaNicole (Nicole Thornber)]

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 }}

[slimak]

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

[gjohansson]

Feel free to make a PR to add it

[DirkFRA]

Voted for it.
I have the same issue over at

Problems with a SQL query when an Entity is involved

Hello everyone After two full days of testing and trying my way through several forums, I’m now turning to you with my problem in the hope that someone knows and can help me My HA recorder runs via MariaDB, so I already have a database available on the Intel NUC on which HA runs. I would also like to include external data here, so for the sake of simplicity I have simply added another table to the Homeassistant database where I can store th…

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

[slimak]

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.

[MrNorth45]

I have just spent the past 6 hours trying to figure out how to write a simple sql query with input parameters, only to find this thread telling me that it can’t be done. That sounds crazy. How am I supposed to access all the LTS data if I can’t even fire a basic sql query like this one

SELECT 
  ROUND(SUM(delta), 1) AS monthly_consumption 
FROM (
  SELECT 
    start_ts, 
    MAX(state) - MIN(state) AS delta 
  FROM statistics 
  JOIN statistics_meta ON statistics.metadata_id = statistics_meta.id
  WHERE statistics_meta.statistic_id = 'sensor.qubino_3_phase_smart_meter_electric_consumption_kwh'
    AND strftime('%Y-%m', start_ts, 'unixepoch') = '2025-01'
  GROUP BY strftime('%Y-%m-%d', start_ts, 'unixepoch')
)

if I can’t dynamically substitute the 2025-01 parameter for something from input_text or whatever. Frankly I have no clue how to proceed with my application now, the LTS data seems simply untouchable… the SQL sensor was my last hope of ever accessing it…

[gjohansson]

You can use a sql query, but not one including templates as it’s not supported yet.
Not sure why you write you can’t use a sql syntax like your example, since that would probably work just fine.

Adding template support to sql is quite tricky, hence it hasn’t been done yet.

[MrNorth45]

@MrNorth45

f I can’t dynamically substitute the 2025-01 parameter for something from input_text or whatever.

Not sure if you reply to my post, but my guess is that you are. You forgot to read the above text in my post. I CAN execute static SQL statements just fine, but since I can’t add any kind of dynamic parameters to the query, it is not really useful. Why not simply add support for @p1, @p2 or something like that? Granted, I haven’t looked at the source code for this, but surely adding parameters for sql statements must be a pretty high ranking requirement…

I did finally managed to solve the problem by using a node red flow and the sqlite addon, but that is just a crazy workaround to access the data that is already in the home assistant DB.

Sure, there is a REST API for the statistics, but that is only helpful for the most simple scenarios. I am doing custom reports that involve quite heavy SQL queries, and direct SQL is the easiest way to solve that.