Como criar uma base de dados leve no HA com histórico e apresentação gráfica

image

O propósito deste tópico é te ensinar como podes criar uma base de dados leve no teu HA para que registes valores do que quiseres nessa mesmo base e mantenhas um histórico de longa data em uma base super leve.

Para fazeres esta configuração precisas de:

:point_right: Home Assistant SO ( possibilidade de instalares addons)
:point_right: Programa Heidisql
:point_right: Addon Maria DB
:point_right: Hacs
:point_right: Card Chartjs Card

Para começares precisas de um ficheiro.py
Onde ir buscar? AQUI

Fazes download do zip como te mostrei na imagem acima.
Descompactas a pasta e copias o ficheiro daily_insert_mysql.py para uma pasta chamada py_scripts caso não tenhas essa pasta cria uma dentro da tua pasta config.
Ou seja o caminho final será /config/py_scripts/daily_insert_mysql.py

De seguida vais instalar o addon MariaDb que podes encontrar na Add-on Store nos addons oficiais:

Após instalares o addon vais ao separados config e colas o que esta abaixo, colocando os teus dados:

databases:
  - custom_data
logins:
  - username: Escolhe um username
    host: '%'
    password: Escolhe uma password
rights:
  - username: O mesmo username que escolheste acima
    host: '%'
    database: custom_data
    grant: ALL PRIVILEGES ON

Faz SAVE voltas ao separador info e clicas em START

Ok no addon MariaDB já não precisas fazer mais nada… instalaste e já não precisas mais dele :nerd_face:

Ok agora vamos elaborar o ficheiro yaml para começar a construir a base de dados.
Usas ficheiros em modo package? Não? Não sabes o que é? Caso não uses :stop_sign: pára aqui e vê isto depois quando já tiveres essa parte configurada volta aqui.
Continuando…

Cria um ficheiro chamado exemplo: base_dados.yaml

E vais começar por colar lá o código que vou agora começar a colar abaixo
:warning: A T E N C A O tudo as linhas que tu vires que tenham XXXX são campos que TU TENS que alterar para os teus dados. Todo o código abaixo é para ir colando sempre por baixo no mesmo ficheiro.

shell_command:
  daily_insert_mysql: "python3 /config/py_scripts/daily_insert_mysql.py --host=core-mariadb --user=XXXX --password=XXXX --db=custom_data --table={{ table }} --value={{ value }} {{ '--col='+column if column is defined }}"
  python_install_pymysql_dependency: "pip install pymysql"

Estes dois shell comando acima :point_up: um deles é responsável para te escrever na tua base de dados, e o segundo para instalar a dependência automática sempre que reinicias o HA.

automation:

  - alias: "Instala a dependencia no arranque do ha"
    initial_state: true
    trigger:
      - platform: homeassistant
        event: start
    action:
      - service: shell_command.python_install_pymysql_dependency

Esta automação acima :point_up: sempre que reinicias o HA ela instala a dependência.

AGORA pára um bocado, pensa queres registar o que nesta tabela? Que dados? Queres registar?
Imagina que pensaste que querias registar o consumo geral de electricidade da tua casa.
Então vais agora criar essa tabela.
:warning: Lembra-te é um exemplo podia ser outro dado qualquer, temperatura,humidade, etc…

  - alias: "Registar o consumo diário de energia"
    initial_state: true
    trigger:
      - platform: state
        entity_id: sensor.XXXX
    action:
      - service: shell_command.daily_insert_mysql
        data_template:
          table: consumo_energia
          value: >
            {{ trigger.to_state.state }}
          column: >
            {% if trigger.entity_id == "sensor.XXXX_O_MESMO_DE_CIMA" %}
              consumo_energia_diario
            {% endif %}

Então que isto acima :point_up: faz para que entendas a lógica.
No trigger vais escolher o sensor que tem o valor que queres registar.

Na Action onde diz TABLE é o nome da tabela que queres dar onde vai registar os teus dados.
ainda dentro da action tens column aqui é o nome da coluna que vais dar a tabela que criaste anteriormente.

image ok e como são registados esses dados?

Os dados são automaticamente registados assim que o sensor que tu definiste no trigger o valor seja alterado.
Ou seja imagina que o valor está 1.25 assim que passar para 1.26 o shell_command que viste lá em cima vai se encarregar de escrever o novo valor na base.

image ok e posso verificar e ter a certeza que isso está mesmo a escrever na base de dados?

Podes… lembras-te do programa que te disse para instalares mais acima? O heidisql?
Ok Abre o programa…
Quando abres o programa vai te aparecer isto:


Clica onde diz NOVA no canto inferior esquerdo.

Ok agora


Onde tens nome de anfitrão colocas o ip da tua máquina onde tens Username e password vais por os dados que usaste no addon mariadb.
E por fim clicas em ABRIR

Vai te aparecer uma nova janela igual a que estás a ver abaixo e clicas no + custom data do lado esquerdo

Depois irá aparecer isto:
image
e clicas em CIMA de consumo_energia

Vai se extender a janela onde vais clicar mais a direita onde diz DADOS e mais abaixo como podes verificar no exemplo tens a column consumo_energia_diario que tinhamos falado mais acima, e dentro dessa coluna por baixo está o ANO/MES/DIA com o valor do teu sensor que se vai actualizando automaticamente sempre que o mesmo altera.
Quando passamos para um novo dia às 24horas 00:00 um novo dia é registado na coluna que por sua vez inicia a nova contagem do sensor, assim acabaste de criar uma base de dados onde nela criaste uma tabela… e nessa tabela criaste uma coluna onde regista os teus valores.

image Fixe posso registar mais valores?
Posso registar quantas horas a luz da minha cozinha está acesa?
Claro …

No mesmo ficheiro yaml colocas:

  - alias: "Insere na tabela o número de Horas que a luz da cozinha esta ligada"
    initial_state: true
    trigger:
      - platform: state
        entity_id: sensor.horas_luz_cozinha
    condition:
      - condition: template
        value_template: >
          {{ (trigger.to_state.state | float) != 0.0 }}
    action:
      - service: shell_command.daily_insert_mysql
        data_template:
          table: horas_luzes
          value: >
            {{ trigger.to_state.state }}
          column: luzcozinha

Ok aqui em cima :point_up: explicando mais uma vez no trigger tens um sensor… esse sensor chamasse sensor.horas_luz_cozinha esse nome tem que ser igual ao sensor que vais ver mais abaixo.
Ok depois tens novamente uma tabela que diz horas_luzes como podes estar a perceber estamos a criar uma nova tabela… e depois tens uma column com o nome luzcozinha.
Resumindo na tua base de dados vais então criar mais uma tabela e uma coluna onde nessa coluna vai estar registado o número de horas diárias que a tua luz da cozinha esteve acessa.

sensor:
  - platform: history_stats
    entity_id: light.XXXX
    state: 'on'
    name: Horas Luz Cozinha
    start: '{{ now().replace(hour=0, minute=0, second=0) }}'
    end: '{{ now() }}'

Aqui acima :point_up: está o sensor “manual” que criamos que neste caso se chama horas luz cozinha lembras-te mais acima onde te disse que ias ver o nome do sensor que deste no trigger aqui está é este.
E tens também aqui definido no sensor o light.XXXX que é a tua luz da cozinha , atenção que pode a tua luz ser light ou swich.

Então e como faço para testar? Simples. Salvas o ficheiro… Ligas a luz da cozinha e reinicias o HA…a tabela vai ser criada e a coluna também e vai começar na base o registo da horas em que a luz está acesa.
Para confirmar vais novamente ao programa heidisq e vais ver que tens uma nova tabela:
image

E se clicares onde na tabela horas_luzes verificas que tens uma nova coluna criada:
image

Ok fixe se está tudo correcto até agora, estás no bom caminho.
Agora presumindo que já tens o HACS instalado vais instalar o card Chartjs Card.

Como instalar??
Simples, copias este endereço : :point_right: https://github.com/ricreis394/chartjs-card

Depois vais ao HACS no teu UI
Para acederes clicas onde diz HACS e depois onde diz FRONTEND

Irá te aparecer uma nova janela semelhante à que ves abaixo e clicas nos três pontos do lado superior direito.

Clicas em Custom Repositories
image

Vai aparecer nova janela colas o repositorio que copiaste uns passos mais acima e na categoria escolhes LOVELACE e clicas em ADD
image

O cartão irá aparecer mais abaixo em qualquer sitio, procura-o e clicas em INSTALL

Ok fixe, se correu tudo bem também até aqui tens o teu cartão Instalado.
Bora lá por então o teu primeiro com os teus registos da base no HA.
O cartão para veres o teu consumo de electricidade com dinheiro total gasto.

Lembras-te que tens um ficheiro criado com o nome base_dados.yaml certo?
Ok dentro do teu sensor: por baixo do que já lá tens vais colocar

  - platform: sql
    db_url: mysql://XXXX:XXXX@core-mariadb/custom_data
    queries:
      - name: consumo_electricidade
        query: >-
          SELECT
              CONCAT(
                  '[',
                  GROUP_CONCAT(DAY(date) SEPARATOR ','),
                  ']'
              ) AS labels,
              CONCAT(
                  '[',
                  GROUP_CONCAT(consumo_energia_diario SEPARATOR ','),
                  ']'
              ) AS data,
              CONCAT(
                  '["',
                  GROUP_CONCAT(IF(`consumo_energia_diario` >= 10, 'red', IF(`consumo_energia_diario` >= 5, 'orange', 'green')) SEPARATOR '","'),
                  '"]'
              ) AS colors,
              ROUND(SUM(consumo_energia_diario), 2) AS total,
              FORMAT(round(((sum(consumo_energia_diario) * 0.15184 + 0.1183 + 0.041 + 0.107)*1.23), 2), 2) AS 'total_eur',
              'info in attributes' AS value
          FROM consumo_energia
          WHERE MONTH(date) = MONTH(CURRENT_DATE);
        column: "value"

:warning: aqui em cima :point_up: os unicos dados que tens que alterar é a 2ª linha
db_url onde estão os 4 XXXX troca pelo username e password da tua mariadb.

Ok Salva o ficheiro e faz uma verificação para ver se tens erros. Se tiveres comenta este post para chegarmos a uma possível solução.
image

Ok verificaste não deu erros ? fixe , podes reiniciar o HA mais uma vez.

Porreiro visto que já reiniciaste vamos então finalmente por o cartão.
Já deves saber colocar cartões no Lovelace mas se não souberes , sem problema eu ensino.te.
Lado superior direito clicas nos 3 pontos
image

De seguida configurar UI
image

Clicas no +
image

Andas com a barra para baixo e clicas em MANUAL

Apagas o que está dentro do “circulo a vermelho”

E colas isto :point_down:

chart: bar
custom_options:
  showLegend: false
data:
  datasets:
    - backgroundColor: '${states["sensor.consumo_electricidade"].attributes.colors}'
      borderWidth: 1
      data: '${states["sensor.consumo_electricidade"].attributes.data}'
      label: Eletricidade
  labels: '${states["sensor.consumo_electricidade"].attributes.labels}'
entity_row: false
options:
  scales:
    yAxes:
      - ticks:
          beginAtZero: true
  title:
    display: true
    text: >
      ${"Consumo energético Mês Corrente -> " +
      states["sensor.consumo_electricidade"].attributes.total_eur + "€"}
type: 'custom:chartjs-card'

E clicas em SAVE

Irá te aparecer:

image

:partying_face: :partying_face: :partying_face: PARABÉNS tens o teu primeiro gráfico da tua a mostrar valores da base de dados que tu criaste. :star_struck:

Podes agora criar muitos mais cartões para que visualizes no HA os dados que estão na tua base.
Diverte-te e partilha conosco as tuas dúvidas e os teus cartões.

TODOS OS CRÉDITOS PARA ricreis394

7 Likes

Muito bom… :+1:

Em modo de implementação…

O meu obrigado :smiley:

1 Like

Muito bom, obrigado.

Arranjar um tempinho para olhar e tentar implementar!

Para conseguires medir os consumos de cada equipamento individualmente (placa, forno, etc…), o que usas?

1 Like

Para equipamentos que usam mais Potência tipo forno, placa, microondas, uso o Shelly EM
para equipamentos tipo frigorifico, impressora 3d, etc uso tomadas Blitzwolf

Muitos parabéns pelo tópico, imensa informação útil e sinceramente vou usar este método para todos os meus registos de consumo e para apresentação gráfica dos mesmo, algo que vai durar algum tempo pois tenho de adaptar tudo o que já tenho a este método.

Uma questão:

1ª - Eu já tinha o mariadb como a base de dados do HA e então usei os dados que já tinha também para este método. É aconselhável fazê-lo ou deveria acrescentar uma base de dados nova no add-on só para isto?

1 Like

Podes manter o mariadb para o HA, mas confirma que crias uma nova base de dados à parte para não criar conflito com a do HA, o @RodolfoVieira explica bem no tópico. Não metas o script a registar dados na BD do HA senão vai dar para o torto.

Ok , então antes de outra coisa vou tratar disso.

o meu nao esta a escrever na base de dados…

shell_command:
  daily_insert_mysql: "python3 /config/py_scripts/daily_insert_mysql.py --host=core-mariadb --user=xxx --password=xxx --db=custom_data --table={{ table }} --value={{ value }} {{ '--col='+column if column is defined }}"
  python_install_pymysql_dependency: "pip install pymysql"


automation:

  - alias: "Instala a dependencia no arranque do ha"
    initial_state: true
    trigger:
      - platform: homeassistant
        event: start
    action:
      - service: shell_command.python_install_pymysql_dependency


  - alias: "Registar o consumo diário de energia"
    initial_state: true
    trigger:
      - platform: state
        entity_id: sensor.energia_rede
    action:
      - service: shell_command.daily_insert_mysql
        data_template:
          table: consumo_energia
          value: >
            {{ trigger.to_state.state }}
          column: >
            {% if trigger.entity_id == "sensor.energia_rede" %}
              consumo_energia_diario
            {% endif %}

add on maria db log:
[00:28:45] INFO: Ensuring internal database upgrades are performed
2020-07-03 0:28:53 24 [Warning] ‘proxies_priv’ entry ‘@% root@core-mariadb’ ignored in --skip-name-resolve mode.
[00:28:53] INFO: Ensure databases exists
[00:28:54] INFO: Create database custom_data
[00:28:54] INFO: Ensure users exists and are updated
[00:28:55] INFO: Create user skysurf
[00:28:56] INFO: Init/Update rights
[00:28:57] INFO: Alter rights for skysurf to custom_data

[00:28:59] INFO: Successfully send service information to Home Assistant.

a entidade sensor.energia_rede muda de valor e nao me escreve na base de dados:


e

o k esta mal ?

@Skysurf Edita o teu post na parte final que não se entende.

No shell command meteste o user e password que criaste no mariadb? Aqui aparece a xxx, mas assumo que no teu código esteja correto, só para confirmar.

o shell_command mesmo estando a retornar erro, não aparece nos erros do HA, a melhor solução é tentar entrar no container do HA e correr esse comando no terminal

python3 /config/py_scripts/daily_insert_mysql.py --host=core-mariadb --user=xxx --password=xxx --db=custom_data --table=consumo_energia --value=2 --col=consumo_energia_diario
:point_up: muda o user e pass

@RodolfoVieira e @ricreis394, parabéns pelo trabalho e muito obrigado por compartilhar.
Fiz aqui (com pequenas alterações, para atender às características locais), e funcionou perfeitamente. Vocês farão alterações, para que seja possível escolher o mês? Eu tentei algumas coisas, mas sem sucesso. Eu não sei onde é guardado o índice de um input_select.

1 Like

já deu… desliguei a box e voltei a ligar e ja funcionou … thanks…

A questão de selecionar o mês não é possível porque o sensor sql não suporta templates, então não consegues criar uma query dinâmica com base no input_select
Tenho nos meus planos, desenvolver um custom_component, exatamente igual ao sensor sql, com a funcionalidade de templates. Mas ainda não sei quando lhe pegar

Se quiseres sair fora da caixa e experimentar coisas novas, podes experimentar com o nodered. Existe um node chamado node-red-node-mysql, não experimentei mas acredito que consigas fazer isso. Depois de obter o resultado, só tens de meter a enviar o valor para um sensor no HA
Este node está a ser utilizado pelo @ricain, foi ele que me deu a conhecer

1 Like

Muito obrigado por compartilhar…
vou buscar um tempo para fazer essa implementação

Parabéns pela iniciativa

1 Like

@ricreis394
no dev do ha dá-me o sensor: sensor.horas_luz_led_strip state: 1.2
corri esse comando mas adaptado para a minha luz led para saber as horas e dá-me o seguinte erro:

python3 /config/py_scripts/daily_insert_mysql.py --host=core-mariadb --user=xxx --password=xxx --db=custom_data --table=horas_luzes --value=2 --col=luzledstrip
alterando para o meu o user e pass

codigo que tenho:

- alias: "Insere na tabela o número de Horas que o led strip esta ligado"
    initial_state: true
    trigger:
      - platform: state
        entity_id: sensor.horas_luz_led_strip
    condition:
      - condition: template
        value_template: >
          {{ (trigger.to_state.state | float) != 0.0 }}
    action:
      - service: shell_command.daily_insert_mysql
        data_template:
          table: horas_luzes
          value: >
            {{ trigger.to_state.state }}
          column: luzledstrip

sensor:
  - platform: history_stats
    entity_id: light.led_strip
    state: 'on'
    name: Horas Luz Led Strip
    start: '{{ now().replace(hour=0, minute=0, second=0) }}'
    end: '{{ now() }}'

a coluna, e os dados das horas das luzes, nao me apareçe na base de dados… o que esta mal ?

Pelo erro, está a faltar a dependência que supostamente criaste uma automação para a instalar cada vez que o HA é reiniciado. Tenta reiniciar o HA a ver se instala, caso continue a não dar, vê se a automação existe ou está ativa

ela esta lá:

shell_command:

shell_command:
  daily_insert_mysql: "python3 /config/py_scripts/daily_insert_mysql.py --host=core-mariadb --user=xxx --password=xxx --db=custom_data --table={{ table }} --value={{ value }} {{ '--col='+column if column is defined }}"
  python_install_pymysql_dependency: "pip install pymysql"

```
já reiniciei e esta igual ...

nao percebo, ja tinha reniciado uma 4 vezes, e nada, e agora já esta, já criou …

1 Like

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


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