I am trying to run a python programm using extrnal lib. The python works, if executed through ssh.
How can I trigger it from an automation?
using pyscript :
developer tools >> actions
Python Scripts: Benzinp3
Die Aktion python_script.benzinp3 konnte nicht ausgeführt werden. Unknown error
using a shell command:
action: shell_command.benzin_pyscript
data: {}
stdout: Starting program /pyscript/Benzinp3.py
stderr: |-
Traceback (most recent call last):
File "/config/pyscript/Benzinp3.py", line 94, in <module>
cursor.execute(sql_query)
sqlite3.OperationalError: no such table: states
What I did:
I installed
and
to create a sensor which I will referr to later (it is working well)
with the
I made those edits:
configuratation.yaml
shell_command:
benzin_pyscript: "python3 /config/pyscript/Benzinp3.py"
/homeassistant/pyscript/requirements.txt
matplotlib
mathplot
pandas
sys
subprocess
importlib.util
homeassistant/pyscript/Benzinp3.py
if executed in the terminal this works well.
print(f"Starting program /pyscript/Benzinp3.py")
import os
import sqlite3
import csv
import time
import matplotlib.pyplot as plt
import matplotlib.dates as mdates
import pandas as pd
from datetime import datetime, timedelta
# Define the database and CSV file paths
db_file = '../home-assistant_v2.db'
csv_file = 'fuel_price_data.csv'
# Define the image file path for the plot
image_file = '../www/fuel_price_history.jpg' # Saving as PNG
# Define the SQL query
# Calculate yesterday's date and its start and end times
yday = datetime.now() - timedelta(days=1)
# Define the start time as 00:00:00 of yesterday
date_start = yday.replace(hour=0, minute=0, second=0, microsecond=0)
# The end time should be the current time today
date_end = datetime.now()
# Convert the start and end times to UNIX timestamps for the query
yday_start_ts = int(date_start.timestamp())
today_end_ts = int(date_end.timestamp())
# Adjusted SQL query using the calculated timestamps
sql_query = f"""
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.aral_sodener_strasse_29_super'
AND states.last_updated_ts >= {yday_start_ts}
AND states.last_updated_ts <= {today_end_ts}
ORDER BY states.state_id ASC;
"""
# Execute the SQL query and fetch the data
connection = sqlite3.connect(db_file)
cursor = connection.cursor()
cursor.execute(sql_query)
data = cursor.fetchall() # Fetch all data directly into a variable
connection.close()
# Introduce a 2.5-second wait (if necessary for data consistency)
time.sleep(4.5)
# Convert the fetched data into a Pandas DataFrame
df = pd.DataFrame(data, columns=['timestamp', 'price', 'entity_id'])
# Convert the timestamp column to a datetime object
df['timestamp'] = pd.to_datetime(df['timestamp'], unit='s')
# Convert 'price' to numeric, forcing non-numeric values to NaN
df['price'] = pd.to_numeric(df['price'], errors='coerce')
# Drop rows where 'price' is NaN
df = df.dropna(subset=['price'])
# Manually adjust timestamps to Berlin timezone (CET) for display on the x-axis
berlin_offset = timedelta(hours=0) # Berlin is UTC+1
df['timestamp'] = df['timestamp'] + berlin_offset
# Get the current time in Berlin timezone
current_time = datetime.now() + berlin_offset
# Get the current time and today's date
today = current_time.date()
# Define fixed start and end times for today
start_time = datetime.combine(today, datetime.min.time()) # 00:00:00 of today
end_time = datetime.combine(today, datetime.max.time()) # 23:59:59 of today
print(f"Running: ..... ")
# print(f"start_time: {start_time}")
# print(f"end_time: {end_time}")
# Define start and end times for yesterday
yday = today - timedelta(days=1)
yesterday_start_time = datetime.combine(yday, datetime.min.time()) # 00:00:00 of yesterday
yesterday_end_time = datetime.combine(yday, datetime.max.time()) # 23:59:59 of yesterday
print(f"yesterday_start_time: {yesterday_start_time}")
print(f"yesterday_end_time: {yesterday_end_time}")
today_df = df[(df['timestamp'] >= start_time) & (df['timestamp'] <= end_time)]
yesterday_df = df[(df['timestamp'] >= yesterday_start_time) & (df['timestamp'] <= yesterday_end_time)]
# Create a new column 'adjusted_timestamp' in yesterday_df
# It retains the time from yesterday's timestamps but sets the date to today.
yesterday_df['timestamp'] = yesterday_df['timestamp'].apply(
lambda x: datetime.combine(today, x.time())
)
print(f"start_time: {start_time}")
print(f"end_time: {end_time}")
print(f"Yesterday's Data Count: {len(yesterday_df)}")
print(yesterday_df.head()) # Show first few rows
print(f"--1-- ")
print(df[['timestamp', 'price']].head())
print(f"--2-- ")
print(f"Berlin-adjusted yesterday_start_time: {yesterday_start_time}")
print(f"Berlin-adjusted yesterday_end_time: {yesterday_end_time}")
print(f"--3-- ")
print(today_df.head()) # Show first few rows
# Create a step plot for both today's and yesterday's data
plt.figure(figsize=(2.9, 3))
fs = 6
# Step plot for today's data
plt.step(today_df['timestamp'], today_df['price'], where='post', linestyle='-', color='darkblue', label='Heute', linewidth=2,alpha=0.9)
# Step plot for yesterday's data
plt.step(yesterday_df['timestamp'], yesterday_df['price'], where='post', linestyle='-', color='lightblue', label='Gestern', linewidth=2, alpha=0.6)
#plt.step(yesterday_df['time'], yesterday_df['price'], where='post', linestyle='-', color='lightblue', label='Gestern', linewidth=2, alpha=0.6)
# Use fill_between to fill the area under yesterday's curve
plt.fill_between(yesterday_df['timestamp'], yesterday_df['price'], step='post', color='lightblue', alpha=0.3, label='')
# Add titles and labels
plt.title(f'Benzinp3 Price (E5) at ARAL Frankfurter Straße 52-54\nLast 24 Hours - {current_time.strftime("%d-%m-%Y %H:%M:%S")} CET', fontsize=fs)
plt.xlabel('24hrs Time')
plt.ylabel('Price (€)')
plt.grid(True)
# Formatting y-axis to show 2 decimals
plt.gca().yaxis.set_major_formatter(plt.FormatStrFormatter('%.3f'))
#plt.autoscale(axis='y')
plt.ylim([df['price'].min()-0.2, df['price'].max()+0.2])
# Set x-axis limits to show the full 24 hours
today = datetime.now().date()
# Define the start and end times for the x-axis
start_time = datetime.combine(today, datetime.min.time()) # Today 00:00:00
end_time = datetime.combine(today, datetime.max.time()) # Today 23:59:59
plt.xlim([start_time, end_time])
#plt.xlim([0:00, 23:59:59])
#plt.xlim(['00:00:00', '23:59:59'])
# Format the x-axis to show time only
plt.gca().xaxis.set_major_formatter(mdates.DateFormatter('%H:%M'))
#plt.gca().xaxis.set_major_formatter(plt.FuncFormatter(lambda x, _: x))
# Set major locator for the x-axis ticks
plt.gca().xaxis.set_major_locator(mdates.MinuteLocator(interval=120)) # Show ticks every x minutes
# Rotate x-axis labels for readability
plt.xticks(rotation=45)
# Add a legend to distinguish between today and yesterday
plt.legend()
plt.xticks(fontsize=fs)
plt.yticks(fontsize=fs)
# Adjust tick parameters for both x and y axis
plt.tick_params(axis='both', which='major', labelsize=fs) # 'major' affects major ticks
plt.tick_params(axis='both', which='minor', labelsize=fs) # 'minor' affects minor ticks if they exist
# Adjust layout to fit labels
plt.tight_layout()
# Save the plot as a image file
plt.savefig(image_file, format='jpg')
plt.close()
my assumption is that pyscript is executing the .py file in a different environment than the terminal does.
Can you help?