Como criar uma base de dados leve no HA (DESATUALIZADO)

:warning: Tópico de desatualizado verificar novo tópico

Antes de mais, fica aqui o link para o Github. Tem a explicação em inglês e o ficheiro que precisam.

Para que este guia funcione terão que fazer download do zip conforme mostro na imagem abaixo.
Após feito o download terão que colocar o ficheiro daily_insert_mysql.py
dentro da pasta python_scripts que deverá dentro dentro da pasta
config
PS: A pasta python_scripts é apenas uma sugestão, podem meter noutra mas depois nos códigos abaixo, têm de atualizar o destino.

Introdução

Nas últimas semanas, eu @ricreis394 , o @RodolfoVieira e o @ricain temos vindo a debater formas de poder guardar dados diários de longo tempo e que ocupassem quase nada no sistema.
Inicialmente foi proposto um método por ficheiro em json publicado neste tópico:

Gráfico Diário consumo de Electricidade

Porém, tinha algumas desvantagens que era a utilização para o Grafana. Com json não havia maneira de importar os dados para o Grafana. Então surgiu a ideia de passar para o MariaDB.
E acredito ser uma solução melhor face ao json.

O que faz mesmo?

É um simples script escrito em python, que regista o valor pretendido, guardando também o dia atual. Ao correr o script mais vezes, o valor anterior será escrito por cima, até ser um dia diferente, ou seja, depois da meia-noite.

A tabela é criada automaticamente ao correr o script, se ela não existir.

Como valor, só é aceite valores em float

Exemplo de como fica a tabela:

date value
2020-04-06 15.5
2020-04-07 16
2020-04-08 14.8
2020-04-09 11.2
2020-04-10 15.4

Ou seja, 365 linhas nem 500KB ocupa :partying_face:

Ok, sei o que faz, mas onde posso aplicá-lo?

Vou deixar aqui algumas:
:point_right: Publicar valores diários dos gastos do dia de eletricidade.

:point_right: Publicar a média do dia da temperatura/humidade da casa, ou de uma certa divisão.

:point_right: Publicar se a regou ou não por dia .

:pencil2: Por enquanto só aceita valores do tipo *float*, por isso neste caso podem usar 0.0 ou 1.0, sendo *False* ou *True*

:point_right: etc…

Sejam criativos :exploding_head:

Utilização

python3 daily_insert_mysql.py --host=db_host(ip or hostname) --user=your_user --password=your_password --db=your_db --table=your_table --value your_value

exemplo em código

Configuração do MariaDB

É necessário a instalação do addon Maria DB

Têm de criar a base de dados, isto só precisam de fazer uma vez. Outras tabelas que possam querer criar mais tarde podem fazê-lo na mesma base de dados
No addon do MariaDB, acrescentem este código.
Não retirem o que lá está, é só acrescentar

databases:
  - custom_data
logins:
  - username: custom_data_user
    host: '%'
    password: teste
rights:
  - username: custom_data_user
    host: '%'
    database: custom_data
    grant: ALL PRIVILEGES ON

No user e password metam o que quiserem.

Como inserir valores na tabela:

O primeiro shell_command é para instalar a dependência necessária para o python e o segundo é o script para inserir os valores

shell_command:
  python_install_pymysql_dependency: "pip install pymysql"
  energy_daily_insert: "python3 /config/python_scripts/daily_insert_mysql.py --host=core-mariadb --user=custom_data_user --password=teste --db=custom_data --table=energy_kwh --value {{ states.sensor.energy_daily_kwh.state }}"

A primeira automation é para instalar a dependencia se necessitar, a segunda é para inserir o valor cada vez que o estado altera.
Podem fazer o trigger do que quiserem, se preferirem podem só fazer o trigger ao final do dia.
No exemplo abaixo a automação faz com que seja publicado na base de dados o consumo energético do dia, e sempre que esse valor se for alterando publica na base de dados automaticamente.

automation:
  - alias: "Energy install python pymysql dependency at startup"
    initial_state: true
    trigger:
      - platform: homeassistant
        event: start
    action:
      - service: shell_command.python_install_pymysql_dependency
  - alias: "Energy daily update total kw"
    initial_state: true
    trigger:
      - platform: state
        entity_id: sensor.energy_daily_kwh
    action:
      - service: shell_command.energy_daily_insert

Apresentação dos valores criados na base de dados

Neste passo, existem várias formas e depende do efeito que queiram, se for para usar no Home Assistant usem a integração oficial SQL
Exemplo:

sensor:
  - platform: sql
    db_url: mysql://user:password@localhost/custom_data
    queries:
      - name: Sql value
        query: "SELECT `value` FROM energy_kwh WHERE `date` = '2020-04-10';"
        column: 'value'
        unit_of_measurement: 'kWh'

Como este script foi a pensar mais no grafana, nesse caso, isto não será preciso. No grafana podem configurar a base de dados MySQL diretamente

Exemplo do grafana:

Se pretendem usar isto para grafana, podem ver este tutorial, que é uma continuação deste tópico:

EDIT 12-04-2020:

Acabei de fazer uma implementação no script. Agora aceita várias colunas, se quiserem. É totalmente opcional.
Usa o paramentro --col
Exemplo: python3 daily_insert_mysql.py --host=core-mariadb --user=data_energy --password=teste --db=custom_data --table=energy_kwh --value 3.4 --col fridge

Se a coluna não existir, ele cria sem stress.

ATENÇÃO PARA OS QUE USARAM O SCRIPT ANTES DESTA ALTERAÇÃO

Têm que fazer uma alteração na tabela, ou então se preferirem eliminem a tabela e o script cria-a de novo.
A coluna value tem de permitir o valor NULL e o valor por defeito tem de ser NULL
Podem usar o HeidiSQL se preferirem para fazer a alteração

EDIT 01-05-2020:

Pro tip

Aconselho vivamente a usarem o script desta forma abaixo. Assim conseguem manter apenas um shell_command que vai servir para qualquer valor que queiram adicionar. E na Automação podem até ter vários triggers e depois fazer a análise para qual coluna ele vai.

automation:
  - alias: "Energy daily insert values to database"
    initial_state: true
    trigger:
      - platform: state
        entity_id: sensor.energy_daily_simples
      - platform: state
        entity_id: sensor.energy_daily_fridge
      - platform: state
        entity_id: sensor.energy_daily_dishwasher
      - platform: state
        entity_id: sensor.energy_daily_drying_machine
      - platform: state
        entity_id: sensor.energy_daily_washing_machine
      - platform: state
        entity_id: sensor.energy_daily_hot_water_tank
    action:
      - service: shell_command.daily_insert_mysql
        data_template:
          table: energy_kwh
          value: >
            {{ trigger.to_state.state }}
          column: >
            {% if trigger.entity_id == "sensor.energy_daily_simples" %}
              total
            {% elif trigger.entity_id == "sensor.energy_daily_fridge" %}
              fridge
            {% elif trigger.entity_id == "sensor.energy_daily_dishwasher" %}
              dishwasher
            {% elif trigger.entity_id == "sensor.energy_daily_drying_machine" %}
              drying_machine
            {% elif trigger.entity_id == "sensor.energy_daily_washing_machine" %}
              washing_machine
            {% elif trigger.entity_id == "sensor.energy_daily_hot_water_tank" %}
              hot_water_tank
            {% endif %}

shell_command:
  daily_insert_mysql: "python3 /config/python_scripts/daily_insert_mysql.py --host=core-mariadb --user=custom_data_user --password=teste --db=custom_data --table={{ table }} --value={{ value }} {{ '--col='+column if column is defined }}"
8 Curtiram

Excelente trabalho, para quem quiser ter um registo longo dos dados que queira registar.
Parabéns pela partilha de conhecimento. :star: :star: :star: :star: :star:

1 Curtiu

Bom trabalho e obrigado pelo esforço :wink:

1 Curtiu

Excelente trabalho.
Não é possível enviar essa informação (um registo diário) usando as ferramentas nativas do HA?
Tipo correr um script todos os dias a 00:00 para efectuar esse registro.
Assim já tínhamos os consumos diário, etc!

@Canossa O exemplo dado é mesmo acerca dos consumos diários.

A única ferramenta que não faz parte do HA é o script em python, todo o resto é nativo.

O trigger do script és tu que decides através da automação. Eu aconselho a fazer o trigger cada vez que o sensor altera de valor, assim mantém a tua base de dados sempre atualizada.

Boas,

tenho um duvida tenho HA instalado num raspberry em ambiente virtual pyton.
Onde e que devo colocar o script visto nao ter a pasta mencionada( [u]python_scripts/u]).

Obrigado

Boas, o script metes onde quiseres. pode ser mesmo na pasta /config se preferires
No shell_command mencionas o caminho do ficheiro

em ambiente virtual pyton nao existe pasta /config

Ela tem de existir porque o HA precisa dela para trabalhar, pode é não estar de fácil acesso.
Eu não uso ambiente virtual não sei como ajudar.

Ja instalei Mariadb e fiz as configuracoes no ha mas tenho estes erros
Component error: logins - Integration ‘logins’ not found.
Component error: rights - Integration ‘rights’ not found.
Component error: databases - Integration ‘databases’ not found.

alguem sabe o que pode ser ?

Estás a meter as configurações do mariadb no home assistant. Não é suposto, como diz acima esse código é para o mariadb addon para criar a base de dados. Se não estás a usar o addon, ignora essr código e cria tu a base de dados e o utilizador manualmente.

Olá pessoal, sou novo no forum ( e no mundo HA) portanto não me levem a mal se disser muitos disparates :slight_smile:

Desde já, obrigado @ricreis394 pelo tutorial. Tive alguns problemas para meter a a funcionar, mas já consegui. No meu caso tive que alterar o script python porque estava a ter erro no “coding”. Alterei para “utf8_general_ci” para resolver (penso que tenha haver com o facto de estar a usar um servidor de mysql e não mariaDB) e tive também que fazer algumas alterações na sintaxe do SQL, mas nada de especial.

Dito isto, gostava de perguntar qual a melhor forma de fazer um gráfico de barras como o do grafana (estou a tentar evitar usar o grafana). A estratégia usada no post sobre “Gráfico Diário consumo de Electricidade” é mais indicada ?

Olá @Tiago_Teixeira

Realmente este script foi feito e testado para o MariaDB Addon, pelo que noutros sistemas pode aparecer erros. De qualquer das maneiras, podes postar as tuas alterações, pode ajudar outros membros mais tarde ou até eu possa adaptar para as 2 situações.

Quanto ao gráfico, apesar de eu ter contribuído para o desenvolvimento do tópico que mencionas, não acho que seja uma boa forma de fazer o gráfico de barras evitando o Grafana, no entanto, acho ser a única solução credível de momento.

Olá já efectuei todas as configurações, mas tenho um erro.
2020-05-01 17:11:36 ERROR (MainThread) [homeassistant.components.shell_command] Error running command: python3 daily_insert_mysql.py --host=core-mariadb --user=custom_data_user --password=teste --db=custom_data --table=energy_kwh --value {{ states.sensor.daily_energy_total.state }}, return code: 2
NoneType: None

Já tentei colocar um valor fixo e dá o mesmo erro!

2020-05-01 17:11:36 ERROR (MainThread) [homeassistant.components.shell_command] Error running command: python3 daily_insert_mysql.py --host=core-mariadb --user=custom_data_user --password=teste --db=custom_data --table=energy_kwh --value 3.3, return code: 2
NoneType: None

Boas, tens de meter a localização do ficheiro fixa e não relativa.
Algo como python3 /config/pasta/daily_insert_mysql.py...

Como referes que o deveria ser colocado dentro de python_scripts, assumi que o caminho não fazia falta!
Agora já não dá erro, mas ainda não tenho nada na pasta data! Até já forcei as automações.

Na pasta data? Mas este script é para escrever na base de dados…

Desculpa, estava a confundir com outra coisa!

Boa publicação.

Eu tenho registo das contagens dos contadores (manualmente com input text) mas só tenho acesso ao último valor.

Esta poderá ser uma excelente ideia para manter registo. Contudo tenho as seguintes dúvidas:

  1. É possível adicionar uma hora, ou corro o risco de manter muitos registos?
  2. Sendo que a entrada é manual (não segue um padrão) só vão entrar registos quando corro o script certo?

@Joao_Carreira O objetivo desta implementação era poupar nos dados e então só permitir guardar dados por dia, se queres usar mais detalhado a solução passa por teres uma base de dados em influxdb

Sim, só serão adicionados dados ao correres o script. No meu caso, o que costumo fazer é, cada vez que o estado que quero guardar alterar o valor, ele vai chamar o script e escrever. A próxima vez que chamar o script vai escrever por cima se o dia for o mesmo.


Copyright © 2017-2021. Todos os direitos reservados
CPHA.pt - info@cpha.pt


FAQ | Termos de Serviço/Regras | Política de Privacidade