Request Graph image from sensor (history) over telegram

Community Guides
, ,
1

hi everyone, this is my first post, also new to this forum…
not super experienced HA user, but willing to learn.
should actually be studying… but u know… tinkering is more interesting :slight_smile:

I wanted to be able to receive a 24h plot of the temperature recorded upon request over telegram.

I will try to explain hoping i can nudge people into the right direction if they want to do the same.

u will need:

how i want this to work:

  1. request over Telegram
  2. automation triggered
    • python script will execute
      • SQL request to retreive sensor data from Database
      • generate plot from data
      • save plot as PNG
    • delay of 7s to give time for python
    • sending image back to telegram chat

in configuration.yaml i have:

notify:
  - platform: telegram
    name: your_own_name
    chat_id: -12345678

python_script:
  requirements:
  - matplotlib
  - pandas
  - sqlite3
  - csv

in automations.yaml i have

- alias: Generate Temperature Plot
  trigger:
    platform: event
    event_type: telegram_command
    event_data:
      command: '/temp24'
  action:
    - service: python_script.exec  
      data:
        file: python_scripts/temperature_plot.py
        cache: false
    - delay:
        seconds: 7
    - service: script.png_request

in scripts.yaml i have:

png_request:
  alias: PNG Request
  sequence:
    - service: notify.your_own_name
      data:
        message: "Temperature for the last 24 hours"
        data:
          photo:
            - file: 'www/images/temp24.png'
              caption: "Temperature Plot for Last 24 Hours"

my python code is:

import os
import sqlite3
import csv
import time
import matplotlib.pyplot as plt
import pandas as pd
from datetime import datetime, timedelta

#Define the database and CSV file paths
db_file = 'home-assistant_v2.db'
csv_file = 'python_scripts/temperature_plots/temp24.csv'

#Define the image file path for the plot
image_file = 'www/images/temp24.png'  # Saving as PNG

#Define the SQL query
sql_query = """
SELECT states.last_updated_ts, states.state, states_meta.entity_id
FROM states
JOIN states_meta ON states.metadata_id = states_meta.metadata_id
WHERE states_meta.entity_id = 'sensor.xiaomi_lywsd03mmc_temperatur'
  AND states.last_updated_ts >= strftime('%s', 'now', '-24 hours')
ORDER BY states.state_id ASC;
"""

#Execute the SQL query and write to CSV
connection = sqlite3.connect(db_file)
cursor = connection.cursor()
cursor.execute(sql_query)
with open(csv_file, 'w', newline='') as csvfile:
    csv_writer = csv.writer(csvfile)
    # No need to write headers as your CSV doesn't have them
    csv_writer.writerows(cursor.fetchall())
connection.close()

#Introduce a 5-second wait
time.sleep(2.5)

#Read the CSV data into a Pandas DataFrame
df = pd.read_csv(csv_file, names=['timestamp', 'temperature', 'entity_id'])

#Convert the timestamp column to a datetime object
df['timestamp'] = pd.to_datetime(df['timestamp'], unit='s')

#Manually adjust timestamps to Berlin timezone (CET) for display on the x-axis
berlin_offset = timedelta(hours=1)  # Berlin is UTC+1
df['timestamp'] = df['timestamp'] + berlin_offset

#Get the current time in Berlin timezone for the title
current_time_berlin = datetime.now() + berlin_offset

#Create and save the plot with the corrected x-axis timestamp and the title's original current time
plt.figure(figsize=(12, 6))
plt.plot(df['timestamp'], df['temperature'], marker='o', linestyle='-', linewidth=2)
plt.title(f'Temperature Data for Last 24 Hours\nCurrent Time: {datetime.now().strftime("%Y-%m-%d %H:%M:%S")} CET')
plt.xlabel('Timestamp (Berlin Time)')
plt.ylabel('Temperature (°C)')
plt.grid(True)
plt.xticks(rotation=45)  # Rotate x-axis labels for readability
plt.tight_layout()  # Adjust layout to fit labels

plt.savefig(image_file, format='png')  # Save as PNG
plt.close()

additional notes

it took me a long time to get this running, unsure how many problems i actually solved myself, and how many problems were solved through a reboot…

things i read which i think could be important:

  • make sure u save the png wo /www/whatever, as that way it can be sent over telegram
  • i read somewhere, that files generated cannot be accessed before rebooting, so if you encounter a problem reboot (unsure if /www or reboot solved the problem for me)
  • running this on a RPi 2 so i’m sure u can adjust the delays, i am receiving a picture on telegram after 15s
  • chatGPT helps :slight_smile:

Wish u success and hopes this can help anyone!
Feel free to clean my code up to make it more comprehensible

1 Like