I like storing data in my own MySQL database. But - whether it is hosted via Home Assistant’s MariaDB addon or externally - I was always baffled by the fact that there was no sleek way to insert data from Home Assistant. In past times I used a shell_command to call the MySQL Command-Line Client. But after switching to Home Assistant OS this was becoming tricky.
So the idea of creating my own custom component grew in my head. I knew that the code wouldn’t be that complex. So one day I sat down, read some tutorials and created (well - mostly copy and pasted) my first custom component:
MySQL Command
A Home Assistant custom component that creates a notify service to send a command to a MySQL server.
If you’re looking for the same thing, check it out. I’m humbled if this helps .
Any feedback appreciated (I am new to Python). Have a nice day!
Very nice solution!
But would there be a way to retrieve the inserted items by a SELECT query?
The use case:
I have a database with phone contacts that i would like to query from an automation.
It would be nice if there would be a way to store the outcome of the query to an entity (configurable or fixed name). It would also be no problem if only the first occurrence would be returned in case the query returns more than 1 row.
Thanks for your feedback. This component creates a notify service. What you are looking for is a sensor. There already is the official SQL sensor: SQL - Home Assistant. Wouldn’t this work for your use case?
I already checked the official SQL sensor, but the problem is that it’s not dynamic enough.
The SQL query for the official component has to be defined in the configuration.yml.
What i’m looking for is a way to dynamically build and execute the query from an automation.
Maybe i can try to build a local custom sensor, based on your code and then add the following peace of code after executing the command:
row = cursor.fetchone()
if row is not None:
self.row = row
This way the sensor will have an attribute named “row” which provides the query result.
Could this approach work?
I would look at other custom components on GitHub that provide a sensor and start from there. I am planning on building a sensor component next and that’s where I would start.
Very interesting and excuse me the possible dumb question but would the service call allow for templated entries? How easy (?) would it be to adapt this for sqlite?
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:
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 .
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?
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.
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
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.
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
I know database config is alright cause i already use it in another HACS integration, maybe they can’t work together or something?
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
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.
.
.
