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:
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.
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:
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.
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.
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ā¦
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.
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.