Skip to content

HACS Pyscript to query an InfluxDB database and copy key:value pair results into a Home Assistant entity

License

Notifications You must be signed in to change notification settings

markcocker/influxdb-query-to-entity

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

10 Commits
 
 
 
 
 
 
 
 

Repository files navigation

influxdb-query-to-entity

Python script to query an InfluxDB database and create a Home Assistant entity with the results in the entity's attributes as key: value pairs.

Installation

  1. Install the HACS custom integration Pyscript custom component.

    For example: HACS → Integrations → + EXPLORE & ADD REPOSITORIES → search for pyscript and install it.

  2. Install Home Assistant integration for Pyscript, and enable Pyscript to resolve dependencies. For details see Configuration.

    For example: Configuration → Integrations → + ADD INTEGRATION → search for Pyscript Python scriptingAllow all imports → Submit.

  3. Restart Home Assistant.

  4. Create the folder config/pyscript/apps/influxdb_query_to_entity/ and copy the script __init__.py into it.

    For example, in Terminal enter:

    mkdir -p /config/pyscript/apps/influxdb_query_to_entity/ && cd $_
    
    git clone https://github.com/markcocker/influxdb-query-to-entity.git __init__.py
  5. Add the influxdb_query_to_entity application entry and configuration into the apps section of the Pyscript configuration.

    For example, in /config/configuration.yaml add:

    pyscript: !include pyscript.yaml

    And in /config/pyscript.yaml configure your InfluxDB endpoint host, port, username and password. Not shown here, but it is best practice to store usernames and passwords in a separate secrets.yaml file.

    allow_all_imports: true
    apps:
      influxdb_query_to_entity:
        host: a0d7b954-influxdb
        port: 8086
        username: homeassistant
        password: XYZ

Syntax for service

With the above installation complete, you can now call the script via the Home Assistant service influxdb_query_to_entity. The service accepts the following service data.

Parameter Type Required? Default Description
database string InfluxDB database name
query string InfluxDB query. The query should return at least the two fields specified by key_field_name and value_field_name. The field time is always returned so typically query will only specify one field. For each point in the result, an attribute will be added to the entity, so be careful not to return too many (100s) of points. Test the query in the InfluxDB web UI or command line interface
key_field_name string time Name of the field returned by the query that will be used as the attribute key
value_field_name string sum Name of the field returned by the query that will be used as the attribute value
entity_id string Entity in Home Assistant to create or update
unit_of_measurement string If specified, add the entity attribute unit_of_measurement with the value
friendly_name string If specified, add the entity attribute friendly_name with the value
icon string If specified, add the entity attribute icon with the value

Results

The service will:

  • connect to InfluxDB and send the query
  • create the entity and remove all previous attributes
  • set the entity value to the current timestamp
  • optionally set the entity attributes unit_of_measurement, friendly_name, icon if they were specified
  • for each point returned in the query, extract the fields specified by key_field_name and value_field_name and use them to add as a entity attribute

Example

To query InfluxDB, call the influxdb_query_to_entity service and pass the query and entity details in the service data. For example:

  1. Select Developer Tools → SERVICES → Service: Pyscript Python scripting: influxdb_query_to_entity

  2. Select GO TO YAML MODE

  3. In Service data enter the query. You will need to substitute your InfluxDB database name, query, field names, and entity details here. Use the InfluxDB or Grafana web UIs to create and test the query:

    database: octopus
    query: >-
      SELECT sum("consumption")
      FROM "electricity"
      WHERE time >= now() - 30d
      GROUP BY time(1d)
      fill(none)
    key_field_name: time
    value_field_name: sum
    entity_id: sensor.octopus_electricity_consumption_30days
    unit_of_measurement: kWh
    friendly_name: Import
    icon: 'mdi:flash'
  4. Click CALL SERVICE. See Developer Tools screenshot.

  5. Check the entity in Developer Tools → SERVICES by clicking STATES and search for the name of the entity. See Developer Tools - States screenshot an example of the query results in the entity.

You can now automate running the query from Configuration > Automations, Node Red and elsewhere.

Example card using the entity

Once you have the entity being populated with the query results, you can create a chart to display the data. For example:

  1. Install the Lovelace card apexcharts-card

    For example: HACS → Frontend → + EXPLORE & ADD REPOSITORIES → apexcharts-card

  2. Navigate to the dashboard for the card → Edit Dashboard+ ADD CARDManual. Enter the card configuration. In the following example note the data_generator option that iterates over the entity attributes to use all timestamp: value entries as data for the series:

    type: 'custom:apexcharts-card'
    header:
    title: Electricity import over last 30 days
    show_states: false
    series:
    - entity: sensor.octopus_electricity_consumption_30days
        show:
        extremas: true
        data_generator: |
        let res = [];
        for (const [key, value] of Object.entries(entity.attributes)) {
            let d = new Date(key).getTime();
            if (d > 0)
               res.push([d, value]);
        }
        return res.sort((a, b) => { return a[0] - b[0] });
    graph_span: 30days
    all_series_config:
    type: column
    group_by:
        func: sum
        duration: 1d
  3. The graph can be seen in the following screenshot. Card Configuration

Feedback

Please use GitHub issues to raise questions, and suggestions. Good luck.

License

Apache License, Version 2.0, January 2004

About

HACS Pyscript to query an InfluxDB database and copy key:value pair results into a Home Assistant entity

Topics

Resources

License

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published

Languages