Extraindo dados de sensores do MariaDB primário para um secundário - aantonelloborges/codesamples GitHub Wiki

Exporta dados específicos do Home Assistant para um segundo banco MariaDB usando uma abordagem automatizada e filtrada.

Aqui está o guia detalhado:

Objetivo

• Extrair dados (como consumo de energia) do banco principal (MariaDB 1) do Home Assistant
• Inserir esses dados no segundo banco MariaDB (MariaDB 2)
• Realizar isso periodicamente, de forma automatizada, com um script ou integração

Pré-requisitos:

• Home Assistant com Record primário configurado para MariaDB
• Dados dos sensores incluídos no Record primário
• Acesso de administrador no HA
• Add-On do Terminal ou VS Code instalado
• Conhecimentos básicos em SQL para conexão com a base de dados (conhecimentos e em Linux e Python são recomendados)

1. No MariaDB de destino, crie uma tabela para armazenar os dados:

Configuração do Banco MariaDB 2 (Destino)

CREATE DATABASE energia_dedicada;

USE energia_dedicada;

CREATE TABLE energy_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    entity_id VARCHAR(255) NOT NULL,
    state FLOAT NOT NULL,
    last_updated DATETIME NOT NULL
);

2. Conecte-se ao MariaDB 1 (principal) para verificar a estrutura dos dados que deseja replicar. Isso pode ser feito com o comando:

USE homeassistant; -- Ou o nome do banco principal
SHOW TABLES;

Normalmente, os sensores estão na tabela states ou statistics.

3. Crie um Script para Exportação de Dados

No terminal, crie uma subpasta na pasta 'config'

Ex.:

mkdir -p /config/scripts

E crie um arquivo para armazenar o seu script

Ex.:

nano /config/scripts/energy_export.py

E adicione o script abaixo nesse arquivo

O script Python abaixo lê os dados do primeiro banco e os insere no segundo banco, filtrando apenas os dados desejados (ex.: consumo de energia).

import mysql.connector
from datetime import datetime
        
# Configurações do MariaDB principal (fonte)
DB_SOURCE_HOST = "IP_DO_MARIADB_PRINCIPAL"
DB_SOURCE_USER = "usuario_fonte"
DB_SOURCE_PASSWORD = "senha_fonte"
DB_SOURCE_NAME = "homeassistant"
        
# Configurações do MariaDB secundário (destino)
DB_DEST_HOST = "IP_DO_MARIADB_DESTINO"
DB_DEST_USER = "usuario_destino"
DB_DEST_PASSWORD = "senha_destino"
DB_DEST_NAME = "energia_dedicada"
        
# ID da entidade que será exportada (ex.: sensor.energy_usage)
# Ajuste o nome da entidade (ENTITY_ID) para o sensor desejado.

ENTITY_ID = "sensor.energy_usage"
         
### ----------------------------------------------------------

# Função para obter os dados do banco fonte
def fetch_energy_data():
    try:
        conn = mysql.connector.connect(
            host=DB_SOURCE_HOST,
            user=DB_SOURCE_USER,
            password=DB_SOURCE_PASSWORD,
            database=DB_SOURCE_NAME
        )
        cursor = conn.cursor(dictionary=True)
        query = """
            SELECT entity_id, state, last_updated
            FROM states
            WHERE entity_id = %s
            ORDER BY last_updated DESC
            LIMIT 1;
        """
        cursor.execute(query, (ENTITY_ID,))
        data = cursor.fetchone()
        return data
    except mysql.connector.Error as err:
        print(f"Erro no banco fonte: {err}")
        return None
    finally:
        if conn.is_connected():
            cursor.close()
            conn.close()

# Função para inserir dados no banco destino
def insert_data_into_dest(data):
    try:
        conn = mysql.connector.connect(
            host=DB_DEST_HOST,
            user=DB_DEST_USER,
            password=DB_DEST_PASSWORD,
            database=DB_DEST_NAME
        )
        cursor = conn.cursor()
        query = """
            INSERT INTO energy_data (entity_id, state, last_updated)
            VALUES (%s, %s, %s);
        """
        cursor.execute(query, (data["entity_id"], float(data["state"]), data["last_updated"]))
        conn.commit()
        print("Dados inseridos no banco destino com sucesso")
    except mysql.connector.Error as err:
        print(f"Erro no banco destino: {err}")
    finally:
        if conn.is_connected():
            cursor.close()
            conn.close()
 
# Fluxo principal
if __name__ == "__main__":
    data = fetch_energy_data()
    if data:
        insert_data_into_dest(data)
    else:
        print("Nenhum dado encontrado para exportação")

4. Crie um agendamento de execução do script

4.1. Opção A: Agendamento do Script via Automação do HA

Primeiro passo é habilitar o Shell Command no configuration.yaml

Adicione uma configuração para o comando que executa o script Python:

shell_command:
   run_energy_script: "python3 /config/scripts/energy_export.py"

Reinicie o HA

Segundo passo é criar a automação no HA

alias: Agendar Exportação de Energia
description: Executa o script Python para exportação de dados
trigger:
  - platform: time
    at: "03:00:00"  # Executa às 03:00 AM
action:
  - service: shell_command.run_energy_script
mode: s

4.2. Opção B: Agendamento do Script via CRON

No terminal do linux (pelo Add-On do Terminal), abra o "Cron"

crontab -e

Adicione a linha para executar o script a cada 5 minutos:

*/5 * * * * /usr/bin/python3 /config/scripts/energy_export.py