SQL

The sql sensor integration集成将 Home Assistant 与您的设备、服务等连接和集成。 [Learn more] 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.

This integration can be configured using both config flow and by YAML.

配置

要将 SQL integration 添加到您的 Home Assistant 实例中，请使用此 My 按钮：

Configuration by YAML

To configure this sensor, define the sensor connection variables and a list of queries to your configuration.yamlconfiguration.yaml 文件是 Home Assistant 的主要配置文件。它列出了要加载的集成及其特定配置。在某些情况下，需要直接在 configuration.yaml 文件中手动编辑配置。大多数集成可以在 UI 中配置。 [Learn more] file. A sensor will be created for each query.

To enable it, add the following lines to your configuration.yamlconfiguration.yaml 文件是 Home Assistant 的主要配置文件。它列出了要加载的集成及其特定配置。在某些情况下，需要直接在 configuration.yaml 文件中手动编辑配置。大多数集成可以在 UI 中配置。 [Learn more] file. 在更改了configuration.yamlconfiguration.yaml 文件是 Home Assistant 的主要配置文件。它列出了要加载的集成及其特定配置。在某些情况下，需要直接在 configuration.yaml 文件中手动编辑配置。大多数集成可以在 UI 中配置。 [Learn more] 文件后，重启 Home Assistant 以应用更改。 该集成现在显示在集成页面的 设置 > 设备与服务 下。其实体在集成卡片上以及实体标签上列出。

# Example configuration.yaml
sql:
  - name: Sun state
    query: >
      SELECT
        states.state
      FROM
        states
        LEFT JOIN state_attributes ON (
          states.attributes_id = state_attributes.attributes_id
        )
      WHERE
        metadata_id = (
          SELECT
            metadata_id
          FROM
            states_meta
          where
            entity_id = 'sun.sun'
        )
      ORDER BY
        state_id DESC
      LIMIT
        1;
    column: "state"

Information

See supported engines for which you can connect with this integration.

The SQL integration will connect to the Home Assistant Recorder database if “Database URL” has not been specified.

There is no explicit configuration required for attributes. The integration will set all columns returned by the query as attributes.

Note that in all cases only the first row returned will be used.

使用模板

对于传入数据，值模板将传入的 JSON 或原始数据转换为有效的有效负载。 传入的有效负载使用可能的 JSON 值进行渲染，因此在渲染时，可以使用 value_json 访问基于 JSON 的有效负载中的属性，否则可以使用 value 变量来处理非 JSON 基础的数据。

此外，this 可以作为模板中的变量使用。this 属性指的是实体的当前 实体状态。 关于 this 变量的更多信息可以在 模板文档 中找到。

{ "state": "ON", "temperature": 21.902 }

Examples

In this section, you find some real-life examples of how to use this sensor.

Current state of an entity

This example shows the previously recorded state of the sensor sensor.temperature_in.

sensor:
  - platform: random
    name: Temperature in
    unit_of_measurement: "°C"

The query will look like this:

SELECT
  states.state
FROM
  states
WHERE
  metadata_id = (
    SELECT
      metadata_id
    FROM
      states_meta
    WHERE
      entity_id = 'sensor.temperature_in'
  )
ORDER BY
  state_id DESC
LIMIT
  1;

Use state as column for value.

Previous state of an entity

Based on previous example with temperature, the query to get the former state is :

SELECT
  states.state
FROM
  states
WHERE
  state_id = (
    SELECT
      states.old_state_id
    FROM
      states
    WHERE
      metadata_id = (
        SELECT
          metadata_id
        FROM
          states_meta
        WHERE
          entity_id = 'sensor.temperature_in'
      )
      AND old_state_id IS NOT NULL
    ORDER BY
      last_updated_ts DESC
    LIMIT
      1
  );

Use state as column for value.

State of an entity x time ago

If you want to extract the state of an entity from a day, hour, or minute ago, the query is:

SELECT 
  states.state
FROM 
  states 
  INNER JOIN states_meta ON 
    states.metadata_id = states_meta.metadata_id
WHERE 
  states_meta.entity_id = 'sensor.temperature_in' 
  AND last_updated_ts <= strftime('%s', 'now', '-1 day')
ORDER BY 
  last_updated_ts DESC 
LIMIT
  1;

Replace -1 day with the target offset, for example, -1 hour. Use state as column for value.

Keep in mind that, depending on the update frequency of your sensor and other factors, this may not be a 100% accurate reflection of the actual situation you are measuring. Since your database won’t necessarily have a value saved exactly 24 hours ago, use “>=” or “<=” to get one of the closest values.

MariaDB

On MariaDB the following where clause can be used to compare the timestamp:

...
  AND last_updated_ts <= UNIX_TIMESTAMP(NOW() - INTERVAL 1 DAY)
...

Replace - INTERVAL 1 DAY with the target offset, for example, - INTERVAL 1 HOUR.

Database size

Postgres

SELECT pg_database_size('dsmrreader')/1024/1024 as db_size;

Use db_size as column for value. Replace dsmrreader with the correct name of your database.

MariaDB/MySQL

Change table_schema="homeassistant" to the name that you use as the database name, to ensure that your sensor will work properly.

SELECT table_schema "database", Round(Sum(data_length + index_length) / POWER(1024,2), 1) "value" FROM information_schema.tables WHERE table_schema="homeassistant" GROUP BY table_schema;

Use value as column for value.

SQLite

If you are using the recorder integration then you don’t need to specify the location of the database. For all other cases, add sqlite:////path/to/database.db as Database URL.

SELECT ROUND(page_count * page_size / 1024 / 1024, 1) as size FROM pragma_page_count(), pragma_page_size();

Use size as column for value.

MS SQL

Use the same Database 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.

Example Database URL: "mssql+pyodbc://username:password@SERVER_IP:1433/DB_NAME?charset=utf8&driver=FreeTDS"

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';

Use size as column for value.