I’m posting this custom sensor in order to get some feedback (improvements and validation)
The basic idea is that a sensor retrieves data from an SQL query. My current use case is monitoring HA database size, but I see various other use cases. Please share your ideas 
It seems a nice idea 
What happens if the query returns more than one row? If the result set is very large? The retrieval frequency can be set like the Ping component?
“What happens if the query returns more than one row? If the result set is very large?”
Currently it will get you the last row, nonetheless this is an advanced sensor (it required SQL knowledge) and would assume users know what they are doing… also thought about pervasively adding a “LIMIT 1” to the query, so to avoid a very large result by mistake.
“The retrieval frequency can be set like the Ping component?”
Can you be more specific ? can’t find anything related in the binary_sensor/ping
Sorry, i was writing from my phone and had no time to make proper reseach
I was referring to the scan_interval settings that the Ping Binary sensor inherit from the Entity class. Looking at the code your component uses that too, so the query interval can be set with the same property. Is this correct?
I’m installing the sensor, I have a MariaDB installation on a Synology NAS, and will report back the outcomes. I think you should add a PR to include the sensor in the main HA distribution.
- platform: sql
db_url: mysql://myuser:[email protected]:3307/
scan_interval: 300
queries:
- name: Hassio DB size
query: 'SELECT table_schema "database", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "value" FROM information_schema.tables WHERE table_schema="homeassistant" GROUP BY table_schema'
column: 'value'
unit_of_measurement: MB
You can also access the db directly, see the statistics sensor.
Cheers
Tks for the feedback!
Currently have a PR open:
https://github.com/home-assistant/home-assistant/pull/12142
Please take the opportunity to review the code
That property is inherited, so you can use it anywhere
@robmarkcole, what do you mean with “access the db directly”? I had a look at the Statistics Sensor https://home-assistant.io/components/sensor.statistics/ and it seems it can calculate max, min, median etc. from the values of an existing sensor. I don’t see how it could measure the database size…
@namadori I am not sure exactly what range of queries can be performed via the API, but queries against sensors certainly can be performed, from the repo:
@asyncio.coroutine
def _initzialize_from_database(self):
"""Initialize the list of states from the database.
The query will get the list of states in DESCENDING order so that we
can limit the result to self._sample_size. Afterwards reverse the
list so that we get it in the right order again.
"""
from homeassistant.components.recorder.models import States
_LOGGER.debug("initializing values for %s from the database",
self.entity_id)
with session_scope(hass=self._hass) as session:
query = session.query(States)\
.filter(States.entity_id == self._entity_id.lower())\
.order_by(States.last_updated.desc())\
.limit(self._sampling_size)
states = execute(query)
for state in reversed(states):
self._add_state_to_queue(state)
_LOGGER.debug("initializing from database completed")
Btw if you simply want to monitor the db size of an sqlite db, I wrote a custom component for precisely that use. However this SQL component will work with any db server.
Cheers
@robmarkcole yes, I remember your component. In the use case of “knowing the size of the default SQLite database” both components should give the same result, it would be interesting to make a comparison…
The capability to query the DB via recorder is there, but it’s not exposed (a PR on this might get some traction)
Eitherway, the focus of my component is not on the HA DB but on any database.
@dgomes I am interested in enhancing the capabilities for querying the database from within HA components, for example to enable more sophisticated filtering of sensor data, making predictions etc. With that in mind I’ve been working on a python package to parse data returned by SQL queries to the HA database, which might be of interest to you. Repo here.
Cheers
Good work there
I would focus in developing a meta sensor such as the statistics_sensor, that would use a new service in the recorder component (these must be 2 different PR nonetheless)
I sure am interested in your prediction sensors, but I’m currently focusing on creating a filter_sensor with hysteresis to clean up noisy sensors that randomly push error states.
Can you define the requirements for the new service on the recorder?
Should be a simple interface that would return a list (size = parameter) of previous states. This list can then be used for all our crazy ideas 
I do understanding the reason for a custom component, but i do the same with just a command line sensor to a script with a single line mysql query.
A component works on every platform and installs dependencies. For a command line you need first to install the right client in the environment, and in containers (like Hassio) it could be difficult for beginners.
Happy to report that the SQL sensor will be part of the next official release
nice sensor component!
Was trying to get this working with PostGreSQL, but doesn’t seems to work.
When using a commandline sensor to and sh script it works, but using the sensor i can’t get it working.
- platform: sql
db_url: postgresql://user:user@localhost/dsmrreader
queries:
- name: dsmr_dbsize2
query: "SELECT pg_size_pretty(pg_database_size('dsmrreader'));"
column: "value"
unit_of_measurement: MBerror:
"Could not locate column in row for column ‘value’
Done some changes with " and ', but couldn’t get it working.