MySQL Command

Share your Projects!
8

Hi! Yes, it does allow templates within the “message”, the same way as all other notification services. I’ve updated the readme with an example. Here it is:

- service: notify.mysql_command_example_db
      data_template:
        message: >
          INSERT INTO `table` (datetime, column1, column2) VALUES ('{{ now().timestamp() | timestamp_custom('%Y-%m-%d %H:%M:%S') }}', 'value1', 'value2');

That should be fairly easy as the MySQL part in my custom component is really basic. The connection itself is just 13 lines. I don’t know how SQLite handles authentication, but using sqlite3 — DB-API 2.0 interface for SQLite databases — Python 3.12.2 documentation you should be able to alter notify.py in a way that it works with SQLite. Don’t forget to update the requirements / imports. Just go ahead and fork my repository to create your own homeassistant-sqlite_command :wink: .

9

Thanks, will make a note to try this out when I have more time to focus

10

I’m trying to use this custom component to run queries on the offical MariaDB external component.

I’ve edited my configuration file in order to add this notify service, but when I try to reboot HomeAssistant I get this error:

Platform error notify.mysql_command - Requirements for mysql_command not found: [‘mysql-connector-python==8.0.32’].

It looks like mysql-connector is not installed, but I don’t know where I’m wrong.

Any help appreciated.

11

Hi, from what i understand, you’re trying to use a custom sensor that executes a SELECT command and the sensor delivers the value, right? Did you finally make it work?

I’m trying to get a value from MySQL running an automation, insert the value in a variable and then use it.
Can you share what you did or help me?

Thanks in advance.

12

Hi, yes that was my intention indeed.

My first approach was to construct a custom component to achieve that.
The final goal was to provide it a telephone number which then should be looked up in the MySQL database.
The challenge i faced at that time was finding a method to communicate the result(s) back to HA.
Since I was already working on a specific custom Asterisk component, i decided to incorporate the MySQL functionality directly in this component.

So unfortunately no, i haven’t solve it the way i originally wanted to do.

1 Like
13

Hi,

Over the past few days I decided to try to solve the problem and succeeded.
I’ve created a custom service that will handle what we were after.
The service takes an sql query as input and fires it against the MySQL database that has been configured in the configuration.yaml
After invoking the service it responds with an iterable structure that contains the requested values.

For testing purposes i have used the output of a query that returns 3 columns and sends it through the gmail notification service from within an automation.

The query should be written in the form:
select col1, col2, col3,...|* from <table> [where condition]

examples:
select * from contacts
select name, phonenumber from contacts where language='nl'

Since this is just my second python project i’m sure there is still enough to brush up :wink:

Also since i haven’t published anything through HACS yet, it will cost me some extra time to figure that out too.

Inspired by the topicstarter (@qrioniclabs) i named the project: homeassistant-mysql_query.

2 Likes
14

And this is the github location for the MySQL Query component:

1 Like
15

Hi, thank you for replying.

I’m trying to use your integration cause it’s exactly what i was looking for but an error appears after installing it by HACS (all good) but then after writing the MySQL database configuration in configuration.yaml this appears
image
I know database config is alright cause i already use it in another HACS integration, maybe they can’t work together or something?
image

Hope you can help me

16

I’m sorry to hear that it doesn’t work for you.

It does not seem to be related to using database config more than once.
It has something to do with the import of the python module “SupportsResponse” from the homeassisant.core package.
I must say that “SupportsResponse” mechanism is only briefly documented in the HA developers documentation, but i got the idea that this functionality has been introduced in one of the latest HA versions.

So can you check if you are on the latest HA version?
Also are you running HA as a container, or the OS version (HAOS)?

My versions on which i’ve tested are:
Home Assistant 2023.10.1
Supervixor 2023.10.0
Operating System 10.5
Frontend 20231005.0 - latest

17

I’m running it as a container, and here are my versions
image
Not sure if the problem could be the container or the version :sweat_smile:

18

I think the version is causing the problem.
Responding services is a feature that has been introduced in Home Assistant 2023.7.0

I will update the readme on github.

Can you try and update your container to the newest HA version?

19

Gonna check if i can, i’ll let you know

20

Hi, sorry for late reply.

I could finally make it work and it works awesome, but i’m having some trouble cause i wanna get on evalue from the result and it’s not working.
My plan is to get one number from de database and using an “if” sentence, do something if that´s the number i want, but i think i can´t get the number from ‘response’.
I’m using this automation and it gets the number right but then the “if” sentence doesn’t work properly, maybe because the ‘value_template’ isn’t right.

image
image672×641 21.1 KB

I show you the MQTT results below.
image

Maybe changing the ‘value_template’ sentence or extracting just the number from ‘response’ into another variable it would work

21

Hi,

Good to hear that the query command works at your side.
Did you update your container to a recent HA version?

Regarding your automation, i also think the template is incorrect.
Maybe you can try one thing, if that does not work, i will try to replicate your query and automation to debug the problem.

Since result is a collection, in order to be able to return a recordset (multiple records), depending on the query, you’ll have to specify the item number.

in your case:

prueba = {‘result’: [{‘idAP’: 379}]}

you can see the square brackets around the value, because it is a collection.

So you can try this template:

value_template: “{{ state_attr(‘sensor.response’, ‘result[0]’, ‘idAP’) == 379 }}”

22

Or you could try something like:

value_template: “{{ sensor.response.result[0].idAP == 379 }}”

23

After almost an hout and a half trying any possibility this finally worked!

value_template: "{{ response.result[0].idAP == 379 }}"

Thanks for your time and help!!!

24

Y.w.c. and glad to hear you’ve got it working now.
Thanks for trying the service and for your feedback :wink:

1 Like
25

@Yes Thank you for creating this custom service. Installed and working on my side with Home Assistant 2023.12.1 against Mariadb.

26

You’re welcome.

Glad to hear it works for you too.
And many thanks for your time to respond.

27

I was wondering if I can seek your advice. I am able to do a test mysql_query in dev tools.

But wondering how to call your service and display the mysql response in lovelace UI? (2nd screenshot shows entity display with desired mysql response as secondary info.)

Happy to use a custom UI component if that is required.
Thank you in advance for your help. Happy holidays!

dev tools
dev tools2672×1757 333 KB

lovelace
lovelace2683×1760 529 KB