OK,
I finally managed to reach some success. Though there are some residual problems.
If anyone is interested, here is my solution:
I wrote a short python script that I ran within Jupyter Notebook and uses the MariaDB plugin for python (pip install mariadb).
The script reads the Sensor-IDs of the source table and the IDs of the target table.
Then it selects all the data from the statistics source-table with sensors which are present within both tables and translates them into the target table.
# Module Imports
import mariadb
import sys
# Connect to MariaDB Platform1 - source table - verify port 3307 is used or change port-number in script to default 3306
try:
conn = mariadb.connect(
user="user",
password="password",
host="<sql server ip-address>",
port=3306,
database="homeassistant_db"
)
except mariadb.Error as e:
print(f"Error connecting to MariaDB Platform: {e}")
sys.exit(1)
# Get Cursor
cur_s = conn.cursor()
# Get Cursor2
cur_s2 = conn.cursor()
# Connect to MariaDB Platform2 - target table (Verify addon-port 3306 is enabled in the config on home-assistant)
try:
conn2 = mariadb.connect(
user="username",
password="password",
host="<server-ip>",
port=3306,
database="homeassistant_db"
)
except mariadb.Error as e:
print(f"Error connecting to MariaDB Platform: {e}")
sys.exit(1)
# Get Cursor
cur_t = conn2.cursor()
#Get meta-data ids from target-table
t_id_dict={}
cur_t.execute(
"SELECT * FROM statistics_meta")
for (t_id, t_statistic_id,t_source,t_unit_of_measurement,t_has_mean,t_has_sum,t_name) in cur_t:
t_id_dict[t_statistic_id]=[t_id, t_statistic_id,t_source,t_unit_of_measurement,t_has_mean,t_has_sum,t_name]
#Migrate
count=0
cur_s.execute(
"SELECT * FROM statistics_meta")
for (s_id, s_statistic_id,s_source,s_unit_of_measurement,s_has_mean,s_has_sum,s_name) in cur_s:
if(s_statistic_id in t_id_dict):
#print("id: "+str(s_id)+" " + s_statistic_id+"--> "+str(t_id_dict[s_statistic_id][0])+"\n\t")
cur_s2.execute(
"SELECT * from statistics where metadata_id like "+str(s_id))
#for (s2_id,s2_created,s2_start,s2_mean,s2_min,s2_max,s2_last_reset,s2_state,s2_sum,s2_metadata_id) in cur_s2:
# print("\t"+str(s2_id)+"\t"+str(s2_created))
for (q_id,q_created,q_start,q_mean,q_min,q_max,q_last_reset,q_state,q_sum,q_metadata_id) in cur_s2:
query="""INSERT ignore INTO statistics (created,start,mean,min,max,last_reset,state,sum,metadata_id) VALUES (%s,%s, %s, %s, %s, %s, %s, %s, %s)"""
record=(q_created,q_start,q_mean,q_min,q_max,q_last_reset,q_state,q_sum, t_id_dict[s_statistic_id][0])
cur_t.execute(query,record)
#print(record)
conn2.commit()
count=count+1
else:
continue
print(count)
It looks, as all data is available now.
However, there is still one problem:
If I look at the energy-card I see one entry from September and one from May that shows huge energy outliers. But I don’t know how to delete the entries from the database.
If I look for the values of the sensors, there doesn’t seem to be any outlier within.
