Recommendation for custom SQL query setup

Configuration
1

All,

I have successfully established a Caller ID automation that detects incoming calls and announces the caller name over Alexa.

- alias: Notify CallerID
  trigger:
    platform: state
    entity_id: sensor.modem_callerid
    to: "callerid"
  action:
  - service: notify.alexa_media
    data:
      target:
        - media_player.larry_s_echo_show
      data:
        type: announce
      message: >
        "Call from {{ state_attr('sensor.modem_callerid', 'cid_name') }}"

Unfortunately, many of the incoming calls are from wireless devices and the name “Wireless Caller” is announced instead of the real name of the caller.

What I’d like to do is set up a MariaDB table (on another server I run that database already) with the name and phone number of wireless callers I frequently receive. Then, when the caller ID event is triggered, I would like to run a query to see if that number is registered in my table with the real name, and then substitute that name in place of cid_name seen above.

Something like: Select name from caller_id_table where number = %s

where %s is the incoming cid_number. If the result is null, then keep the name I have from the caller ID event, otherwise, use the returned name.

I looked at the SQL Sensor, but that doesn’t seem to fit this use case since it is not populated by an event, but rather by polling.

I looked into the Shell Command integration, thinking I could possibly run the query via a custom python script. However, since I’m running HA via an Unraid docker container, it lacks some python libraries for MySQL (MariaDB) integration. I held up pursuing that for the moment.

What does the community think is my best option for meeting this SQL requirement?

Thanks in advance!

2

You can update the sensor with the homeassistant.update_entity service.

3

Yes, I can force the sensor to be updated, but not seeing how I can pass in a query parameter.

4

Good point!

I think this would be a great enhancement for @dgomes’s SQL Sensor. :sunglasses:
‘Allow templates in sql querys’
Something like:

query: "select name from caller_id_table where number = {{ state_attr('sensor.modem_callerid', 'cid_number') }}"
5

idea is nice… but “with great powers comes great responsibility” that is a big target for an exploit… :frowning:

6

Thanks for the replies regarding the SQL Sensor, but I think the most likely solution lies elsewhere.

Does anyone recommend an approach for intercepting the state of the modem sensor, and, if needed, manipulating that before it gets passed into the alexa media player?

Larry