Request Graph image from sensor (history) over telegram

raffler · February 1, 2024, 2:10pm

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

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:

sensor
Python Scripts for Home Assistant (GitHub - AlexxIT/PythonScriptsPro: Advanced version of python scripts for Home Assistant without limits)
telegram bot already set up
file editor
terminal
a bit of python skills
time

how i want this to work:

request over Telegram
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

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

SincHome · May 9, 2024, 7:48am

Hi,

This was massively helpful. I have no background in SQL, Python or HA development but I’ve managed to make it work for my case.

The above python code did not initially work for me. Below are the issues I had:

I use MariaDB so sqlite3 was not working (or at least that was my conclusion) and the SQL query fails where the 24-hour timestamp is calculated.
My temperature data had “unavailable” states that were getting imported and messing up the plot.
When CSV was being read, the temperature data is read as str. This causes the final plot y-axis to be out of order.

(1) and (2) were fixed by using MySQLdb instead of sqlite3 and changing the query to exclude “unavailable” states. The database connection is:

import MySQLdb

db_file = MySQLdb.connect("core-mariadb","username","password","homeassistant")

The SQL Query is constructed as below to omit “unavailable” records and the timedelta to get records from 24 hours ago is calculated outside the query string and substituted into it.

#Get timestamp for 24 hours ago
time_24h_ago = datetime.now() + timedelta(hours = -24)
time_24h_ago_timestamp = datetime.timestamp(time_24h_ago)

#Define the SQL query
sql_query_str = """
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.temperature'
  AND states.state != "unavailable"
  AND states.last_updated_ts >= %s
ORDER BY states.state_id ASC;
"""

Replace the %s with time_24h_ago_timestamp

sql_query = sql_query_str.replace("%s", str(time_24_ago_timestamp))

To convert the temperature from str to a float I used the dtype method in pandas to convert the temperature as its being read. The other two variables are imported as objects.

types_dict = {'timestamp' : object, 'temperature': float, 'entity_id' : object}
df = pd.read_csv(csv_file, names=['timestamp', 'temperature', 'entity_id'], dtype=types_dict)

Finally, I was interested in a 8-hour power usage chart, so I changed all of the above to use a power usage sensor.

So now I end up with this on Telegram. Massive thanks to your solution @raffler

nickrout · June 13, 2024, 3:49am

This is just what I was after. I spent half the day searching the graph cards in HACS for the ability to export images, without any luck. Then I found this. Excellent.

One query, I don’t much like the hard coding of the timezone, nothing against Berliners, but I get the feeling that there must be a better way. I can obviously hard code my own zone at -12, but then I have to change it once daylight savings starts. I am crap at timezones and time calculations. Every time I do a time based template I have to play around for ages.

Anyway, thanks for the post.

raffler · August 6, 2024, 12:49pm

am glad people are using it or take it as a basis

It actually stopped working shortly after making the post after updating HA, so i had it backrolled. As i use that specific HA specifically only for this graph and nothing else, i am ok with it!

Will perhaps use your correction for my own HA at home! Ty

raffler · August 6, 2024, 1:09pm

so i found a timezone library for pyhon its called pytz
so just add/ adapt with this:


import pytz


# Convert the timestamp column to a datetime object and localize to Berlin timezone
df['timestamp'] = pd.to_datetime(df['timestamp'], unit='s')
#define your timezone u want here
berlin_tz = pytz.timezone('Europe/Berlin')

df['timestamp'] = df['timestamp'].dt.tz_localize('UTC').dt.tz_convert(berlin_tz)

# Get the current time in the Berlin timezone for the title
current_time_berlin = datetime.now(pytz.utc).astimezone(berlin_tz)

Havent tested it, but should work