Some time ago i was looking for a simple way to execute queries against a MySQL database from HA so that i could use the results in an automation.
To be more specific: i’m running FreePBX and wanted to announce some information when receiving inbound calls or establishing outgoing calls.
I came across the MySQL command component which inspired and guided me to build my first custom component.
Until now the MySQL Query component has been discussed in this topic.
Now and then there are some feature requests and issues that needs to be discussed. Also this component has not been introduced before.
So i decided to create this topic, instead of further hijacking the other component’s topic.
MySQL Query
A Home Assistant custom component that creates a ResponseData service to execute a query against a MySQL database. The result values become available as an iterable data structure
I’d really appreciate any feedback regarding this component.
Hi Yes,
thanks a lot for doing this; with my beginner’s knowledge I’d also like to query an external SQL DB for data within an automation and I was a bit turned off by the SQL components need to bury any SQL command in the configuration.yaml. Your solution should do the trick to read data much more conveniently.
You were also referring a lot to the “MySQL Command” component, which is allowing to WRITE data using INSERT commands to the SQL database.
Does this mean if I want to READ and WRITE data to an SQL database I would need to work with both components, “MySQL Command” and your “MySQL Query” or does your component also include/allow writing to the DB?
If not, did someone ever try if these two custom components peacefully coexist?
The references i made to the MySQL Command are because it helped me a lot to develop this component.
At that time I was looking for a component that could execute dynamic queries.
But to be honest i don’t have a use case to write to a database directly from HA (yet), so i personally haven’t used MySQL Command yet.
Technically speaking the MySQL Query component should also be able to write data to a database, but since that was not the initial purpose of it and have also never been tested, i decided to let it only accept retrieval (SELECT) queries.
Regarding the coexistence of MySQL Query and MySQL Command, i don’t expect any problem with that. So yes, you should be able to use one for reading and the other one for writing.
In case you want to use data from MySQL as a sensor in a Lovelace dashboard so that it will automatically reflect changes, be sure to also check the HA MySQL component
I have the same problem as royf007 in the previous thread, namely, got empty response on query from HA. Connection from HA to db is OK:
[core-ssh ~]$ nc 192.168.1.19 3306
X
11.5.2-MariaDB-deb11Se3j@>Vwr-=X@xMETU10?+mysql_native_password
Can you please suggest where to start to solve the issue?
HA developer tools \ actions Action MySQL Query Service: query
action: mysql_query.query
data:
query: SELECT column FROM the.table WHERE year=2024 AND month=11;
response_variable: response
Response
result: [ ]
Core 2024.11.2 Supervisor 2024.11.2 Operating System 13.2 Frontend 20241106.2
