Here is an updated version that also adds the corresponding statistics to the statistics and short_term_statistics tables.
Note it also is generalized to work for any sensor, not just power/gas/water (i.e., sensors that are ‘total’ or ‘total increasing’.
There is a lot of flexibility via changing the (global) user variables at the head of the Python code.
Please let me know if you find any bugs in the code. Us at your own risk of course…
Here are just the headers and variables. The full code is to long to post here so see Full code for full code
#!/bin/sh
"exec" "$(dirname $(readlink -f $0))/venv/bin/python3" "$0" "$@"
# ===============================================================================
# NOTE on python virtual environment setup and invocation
# Above shebang used to invoke venv relative to directory in which script is stored
# See: https://stackoverflow.com/questions/20095351/shebang-use-interpreter-relative-to-the-script-path)
#
# Alternatively, substitute below shebang line if all python libraries available in
# the system or if invoking script from within an already activated virtual environment:
# #!/usr/bin/env python3
#
# Note: I use a 'venv' to avoid having to add additional libraries to my system
#
# To set up 'venv'
# python3 -m venv --system-site-packages venv
# source venv/bin/activate
# pip3 install pip --upgrade
# pip3 install pandas paramiko tzlocal
# deactivate
#
#===============================================================================
# DESCRIPTION:
# Batch insert sensor entries for ENTITY_ID into the HA 'states' database table with
# 'last_updated_ts' time backdated to the time the entry was initially recorded
#
# The input data can be either a SQLite database or a CSV file.
# The data should be in two columns with one column containing the UTC timestamp
# of when data was recorded and the other column containing the sensor data.
# Extra columns are ignored
#
# Note if using a CSV file, the column names should be in the first row
#
# Note: Assumes you have ssh access with root privileges to the HA server
# I use the "Advanced SSH & Web Terminal" HA Add-on
#
#===============================================================================
# USAGE:
# add_water.py
#
# Key user variables (in all-caps)
# SENSOR_DB_PATH File containing the sensor data -- may be SQLite or CSV
# SENSOR_TABLE Name of the data table if input file is SQLite
# SENSOR_COLUMNS Names of the 2 columns containing data
# First is for UTC timestamp, second for the corresponding data value
# CONVERSION_FACTOR Multiply 2nd column by this factor to get correct state units
# SKIP_UNCHANGED Skip (and don't insert)duplicate sequential data elements (this makes sense if data is a 'total')
# ENTITY_ID Name of the sensor for which you want to enter the data
# STATISTIC_ID Typically same as ENTITY_ID
# DEFAULT_ATTRIBUTE_ID Use this attribute id if can't find existing one in the database
# STOP_SHART_HA Stop HA before accessing HA db and restart after if set to True
# BACKUP_HA_FLAG Backup the HA db before accessing HA db if set to True
# DELETE_BACKUP_ON_SUCCESS Delete the backup if program completes successfully if set to True
# BACKUP_HA_DIR Name of the directory to store HA db backups if set to True
# Created if doesn't exist
# REBUILD_FULL_STATISTICS If True, then rebuild all statistics back to the first state value stored
# SSH_HOST Name or ip address of the HA server
# SSH_USER Username on the HA server
# SSH_PASSWD SSH password to access SSH_USER@SSH_HOST
# Not needed if using passphrases and ssh-agent
#
#===============================================================================
# VERSION: 0.6.1
#
#===============================================================================
# CHANGELOG
# 0.5.0 (July 2024)
# - First official release
# 0.6.1 (July 2024)
# - Added ability to insert statistics (both 'statistics' and 'statistics_short_term')
# - Added CONVERSION_FACTORE, DEFAULT_ATTRIBUTE_ID
# - Bug fixes and major code cleanup
#
#===============================================================================
# AUTHOR:
# Jeff Kosowsky
# Copyright July 2024
#
#===============================================================================
#### NOTES:
# States are inserted into the HA 'states' table using manual SQL manipulations
# of the 'home-assistant_v2.db' database since the HA UI and REST API
# don't allow for inserting backdated state entries
#
# This is potentially "fragile" since you are manipulating the database directly
# which could lead to lock or race conditions, though it should generally be petty
# safe since nothing else should be writing to that metadata_id/entity_id
#
# If you are concerned about fragility, do one or more of the following:
# 1. Set the variable 'STOP_START_HA' to 'True' which will stop home assistant
# before accessing the SQLite database and restart it at the end of the program
#
# 2. Backup just the 'home-assistant_v2.db' database either by setting 'BACKUP_HA_FLAG'
# to 'True or copying it manually yourself
# If using 'BACKUP_HA_FLAG', then setting 'DELETE_BACKUP_ON_SUCCESS' to 'True' will
# delete the backup if routine completes successfully
#
# 3. Create a full backup of HA using the US
#
# Note my SQlite SENSOR_TABLE is called 'water' and is of form:
# CREATE TABLE IF NOT EXISTS TABLE_NAME (
# timestamp INTEGER PRIMARY KEY,
# consumption REAL,
# reading INTEGER
# )
# Note that only 'timestamp' and 'reading' are used since I want total readings entered
#
#===============================================================================
##IMPORTS
import os
import sys
import warnings
import uuid
import csv
import sqlite3
import time
from datetime import datetime
from tzlocal import get_localzone
import pandas as pd
with warnings.catch_warnings(): #Otherwise get deprecation warnings on Ubuntu 18.04
warnings.filterwarnings("ignore")
import paramiko
#===============================================================================
#### GLOBAL VARIABLES (Note user-changeable variables are in all-caps
##Local sensor data database variables (paths refer to paths on the local machine)
SENSOR_TABLE = 'water' #Only needed for SQLite database inputs
SENSOR_DB_PATH = 'water.db' #Name of and path to local SQLITE db used to store sensor data
#SENSOR_DB_PATH = 'water.csv' #Name of local CSV db used to store sensor data
#NOTE: Assumed to be relative to script directory if not an absolute path
if not SENSOR_DB_PATH.startswith('/'):
SENSOR_DB_PATH = os.getcwd() + '/' + SENSOR_DB_PATH
sensor_db = os.path.basename(SENSOR_DB_PATH)
#First column should be the name of the time-stamp column, second column should be the name of the data column
SENSOR_COLUMNS = ['timestamp', 'reading'] #Names of data columns from your SENSOR_TABLE corresponding to time & data
ha_state_columns = ['last_updated_ts', 'state'] #Don't change
CONVERSION_FACTOR = .01 #Multiply the 2nd column of SENSOR_COLUMNS by this factor to get the appropriate state units (e.g. ft^3/100 -> ft^3 for my water meter)
SKIP_UNCHANGED = True #Skip (and don't insert)duplicate sequential data elements (this makes sense if data is a 'total')
#Typically, but not always, if corresponding statistic_metadata has column 'has_mean' = 0
## HomeAssistant variables (paths refer to paths on the remote HA machine)
ha_db_path = '/homeassistant/home-assistant_v2.db' #Path to 'home-assistant_v2.db' database
ENTITY_ID = f'sensor.meter_water' #Change this to name of sensor you use
STATISTIC_ID = ENTITY_ID #Typically have the same nameo
DEFAULT_ATTRIBUTE_ID = None #Use this as the default attribute_id for the entity_id if no prior states in the databases for entity_id
#Set to None if don't have or need one
DEFAULT_ATTRIBUTE_ID = None #Add your own number corresponding to a default id if no states yet exist for the sensor
STOP_START_HA = False #Stop and later restart HA if set to True
BACKUP_HA_FLAG = True #If True then backup 'home_assistant_v2.db' before editing database
DELETE_BACKUP_ON_SUCCESS = True #If True, then remove backup if routine completes successfully
BACKUP_HA_DIR = "/tmp/backup" #Note directory is created if it doesn't exist
REBUILD_FULL_STATISTICS = False #If True, then rebuild all statistics
## SSH credentials and settings (note no password required if using ssh-agent)
SSH_HOST = 'homeassistant'
SSH_USER = '<YOUR_USER_NAME' #Change to your user name on the HA server
SSH_PASSWD = '<YOUR_PASSWORD>' #Not necessary if using ssh passphrases and ssh user-agent
## Other global variables
localtz = get_localzone()
backup_path = None
short_stat_int = 300 #Interval length of short_term_statistics in seconds