sql sensor platform enables you to use values from an SQL database supported by the sqlalchemy library, to populate a sensor state (and attributes).
This can be used to present statistics about Home Assistant sensors if used with the
recorder integration database. It can also be used with an external data source.
To configure this sensor, you need to define the sensor connection variables and a list of queries to your
configuration.yaml file. A sensor will be created for each query:
To enable it, add the following lines to your
# Example configuration.yaml sensor: - platform: sql queries: - name: Sun state query: "SELECT * FROM states WHERE entity_id = 'sun.sun' ORDER BY state_id DESC LIMIT 1;" column: "state"
The URL which points to your database. See supported engines.
Defaults to the default recorder
db_url (not the current
db_url of recorder).
List of your queries.
Defines a template to extract a value from the payload.
There is no explicit configuration required for attributes. The integration will set all additional columns returned by the query as attributes.
Note that in all cases only the first row returned will be used.
In this section, you find some real-life examples of how to use this sensor.
This example shows the previously recorded state of the sensor
sensor: - platform: random name: Temperature in unit_of_measurement: "°C"
The query will look like this:
SELECT * FROM states WHERE entity_id = 'sensor.temperature_in' ORDER BY state_id DESC LIMIT 1;
# Example configuration.yaml sensor: - platform: sql queries: - name: Temperature in query: "SELECT * FROM states WHERE entity_id = 'sensor.temperature_in' ORDER BY state_id DESC LIMIT 1;" column: "state"
Note that the SQL sensor state corresponds to the last row of the SQL result set.
This example only works with binary_sensors:
SELECT * FROM states WHERE entity_id = 'binary_sensor.xyz789' GROUP BY state ORDER BY last_changed DESC LIMIT 1;
sensor: - platform: sql db_url: postgresql://user:[email protected]/dbname queries: - name: DB size query: "SELECT (pg_database_size('dsmrreader')/1024/1024) as db_size;" column: "db_size" unit_of_measurement: MB
table_schema="hass" to the name that you use as the database name, to ensure that your sensor will work properly.
sensor: - platform: sql db_url: mysql://user:[email protected]/hass queries: - name: DB size query: 'SELECT table_schema "database", Round(Sum(data_length + index_length) / 1024, 1) "value" FROM information_schema.tables WHERE table_schema="hass" GROUP BY table_schema;' column: "value" unit_of_measurement: kB
If you are using the
recorder integration then you don’t need to specify the location of the database. For all other cases, add
sensor: - platform: sql queries: - name: DB Size query: 'SELECT ROUND(page_count * page_size / 1024 / 1024, 1) as size FROM pragma_page_count(), pragma_page_size();' column: "size" unit_of_measurement: "MiB"
Use the same
db_url as for the
recorder integration. Change
DB_NAME to the name that you use as the database name, to ensure that your sensor will work properly. Be sure
username has enough rights to access the sys tables.
sensor: - platform: sql db_url: "mssql+pyodbc://username:[email protected]_IP:1433/DB_NAME?charset=utf8&driver=FreeTDS" queries: - name: DB size query: "SELECT TOP 1 SUM(m.size) * 8 / 1024 as size FROM sys.master_files m INNER JOIN sys.databases d ON d.database_id=m.database_id WHERE d.name='DB_NAME';" column: "size" unit_of_measurement: MiB