# Como criar uma base de dados leve no HA (DESATUALIZADO)
**URL:** https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-desatualizado/4340
**Category:** Tutoriais
**Created:** [11 Abril , 2020 21:46 UTC](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-desatualizado/4340 "2020-04-11T21:46:08Z")
**Posts on this page:** 20
**Page:** 1
### Author:  [@ricreis394](https://forum.cpha.pt/u/ricreis394)
#### Post date: [11 Abril , 2020 21:46 UTC](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-desatualizado/4340/1 "2020-04-11T21:46:08Z")
⚠ Tópico de desatualizado verificar novo [tópico](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-com-historico-e-apresentacao-grafica/4888)
Antes de mais, fica aqui o link para o [Github](https://github.com/ricreis394/ha-daily-insert-mysql). 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](https://forum.cpha.pt/t/grafico-diario-consumo-de-electricidade/4187)
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** 🥳
# Ok, sei o que faz, mas onde posso aplicá-lo?
Vou deixar aqui algumas:
👉 Publicar valores diários dos gastos do dia de eletricidade.
👉 Publicar a média do dia da temperatura/humidade da casa, ou de uma certa divisão.
👉 Publicar se a regou ou não por dia .
✏ `Por enquanto só aceita valores do tipo *float*, por isso neste caso podem usar 0.0 ou 1.0, sendo *False* ou *True*`
👉 etc…
**Sejam criativos** 🤯
# 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**
```auto
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
```auto
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.
```auto
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](https://www.home-assistant.io/integrations/sql/)
Exemplo:
```auto
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:
> [@Utilização do grafana com uma base de dados mysql](https://forum.cpha.pt/t/utilizacao-do-grafana-com-uma-base-de-dados-mysql/4341):
>
> No seguimento deste [post](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha/4340) a ideia é ensinar-te como podes tirar proveito da base de dados sql para que uses o grafana e possas apresentar posteriomente esses gráficos no HA. Primeiro tens que instalar o addon grafana. Após o teres instalado vai te aparecer algo parecido conforme ves na imagem, tens que clicar onde diz create a data source. Depois tens que fazer scroll e seleccionar a opção MYSQL De seguida preenche todos os dados conforme ves abaixo TOMA NOTA AO QUE TE EST…
# 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.
```auto
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 }}"
```
---
### Author:  [@RodolfoVieira](https://forum.cpha.pt/u/RodolfoVieira)
#### Post date: [12 Abril , 2020 09:05 UTC](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-desatualizado/4340/2 "2020-04-12T09:05:05Z")
Excelente trabalho, para quem quiser ter um registo longo dos dados que queira registar.
Parabéns pela partilha de conhecimento. ⭐ ⭐ ⭐ ⭐ ⭐
---
### Author:  [@ricain](https://forum.cpha.pt/u/ricain)
#### Post date: [12 Abril , 2020 09:18 UTC](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-desatualizado/4340/3 "2020-04-12T09:18:10Z")
Bom trabalho e obrigado pelo esforço 😉
---
### Author:  [@Canossa](https://forum.cpha.pt/u/Canossa)
#### Post date: [12 Abril , 2020 21:23 UTC](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-desatualizado/4340/4 "2020-04-12T21:23:22Z")
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!
---
### Author:  [@ricreis394](https://forum.cpha.pt/u/ricreis394)
#### Post date: [12 Abril , 2020 21:31 UTC](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-desatualizado/4340/5 "2020-04-12T21:31:18Z")
@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.
---
### Author:  [@rukako](https://forum.cpha.pt/u/rukako)
#### Post date: [16 Abril , 2020 11:31 UTC](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-desatualizado/4340/6 "2020-04-16T11:31:08Z")
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
---
### Author:  [@ricreis394](https://forum.cpha.pt/u/ricreis394)
#### Post date: [16 Abril , 2020 12:14 UTC](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-desatualizado/4340/7 "2020-04-16T12:14:56Z")
Boas, o script metes onde quiseres. pode ser mesmo na pasta `/config` se preferires
No `shell_command` mencionas o caminho do ficheiro
---
### Author:  [@rukako](https://forum.cpha.pt/u/rukako)
#### Post date: [16 Abril , 2020 12:55 UTC](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-desatualizado/4340/8 "2020-04-16T12:55:00Z")
em ambiente virtual pyton nao existe pasta /config
---
### Author:  [@ricreis394](https://forum.cpha.pt/u/ricreis394)
#### Post date: [16 Abril , 2020 13:03 UTC](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-desatualizado/4340/9 "2020-04-16T13:03:25Z")
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.
---
### Author:  [@rukako](https://forum.cpha.pt/u/rukako)
#### Post date: [16 Abril , 2020 14:00 UTC](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-desatualizado/4340/10 "2020-04-16T14:00:54Z")
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 ?
---
### Author:  [@ricreis394](https://forum.cpha.pt/u/ricreis394)
#### Post date: [16 Abril , 2020 16:03 UTC](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-desatualizado/4340/11 "2020-04-16T16:03:53Z")
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.
---
### Author:  [@Tiago\_Teixeira](https://forum.cpha.pt/u/Tiago_Teixeira)
#### Post date: [29 Abril , 2020 14:26 UTC](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-desatualizado/4340/12 "2020-04-29T14:26:44Z")
Olá pessoal, sou novo no forum ( e no mundo HA) portanto não me levem a mal se disser muitos disparates 🙂
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 ?
---
### Author:  [@ricreis394](https://forum.cpha.pt/u/ricreis394)
#### Post date: [29 Abril , 2020 14:36 UTC](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-desatualizado/4340/13 "2020-04-29T14:36:46Z")
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.
---
### Author:  [@Canossa](https://forum.cpha.pt/u/Canossa)
#### Post date: [1 Maio , 2020 16:31 UTC](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-desatualizado/4340/14 "2020-05-01T16:31:58Z")
> [@ricreis394](#):
>
> `config`
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
---
### Author:  [@ricreis394](https://forum.cpha.pt/u/ricreis394)
#### Post date: [1 Maio , 2020 16:36 UTC](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-desatualizado/4340/15 "2020-05-01T16:36:03Z")
Boas, tens de meter a localização do ficheiro fixa e não relativa.
Algo como `python3 /config/pasta/daily_insert_mysql.py... `
---
### Author:  [@Canossa](https://forum.cpha.pt/u/Canossa)
#### Post date: [1 Maio , 2020 17:23 UTC](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-desatualizado/4340/16 "2020-05-01T17:23:38Z")
> [@ricreis394](#):
>
> python\_scripts
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.
---
### Author:  [@ricreis394](https://forum.cpha.pt/u/ricreis394)
#### Post date: [1 Maio , 2020 17:39 UTC](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-desatualizado/4340/17 "2020-05-01T17:39:47Z")
Na pasta data? Mas este script é para escrever na base de dados…
---
### Author:  [@Canossa](https://forum.cpha.pt/u/Canossa)
#### Post date: [1 Maio , 2020 17:51 UTC](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-desatualizado/4340/18 "2020-05-01T17:51:32Z")
Desculpa, estava a confundir com outra coisa!
---
### Author:  [@Joao\_Carreira](https://forum.cpha.pt/u/Joao_Carreira)
#### Post date: [1 Maio , 2020 22:51 UTC](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-desatualizado/4340/19 "2020-05-01T22:51:44Z")
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?
---
### Author:  [@ricreis394](https://forum.cpha.pt/u/ricreis394)
#### Post date: [1 Maio , 2020 23:00 UTC](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-desatualizado/4340/20 "2020-05-01T23:00:35Z")
@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.
[Próxima página](https://forum.cpha.pt/t/como-criar-uma-base-de-dados-leve-no-ha-desatualizado/4340.md?page=2)