Home Assistant Stats Fix using MySQL Connector (based on https://gist.github.com/rklomp/231c5cb0c7780019d544f77edc6878e5)

ha_stats_fix_mysql.py

#!/usr/bin/env python3

import argparse
import sys
import mysql.connector

def main(arguments):
    parser = argparse.ArgumentParser()

    parser.add_argument(
        "entity_id", help="The entity ID of the sensor to fix statistics for"
    )
    parser.add_argument(
        "--host", help="The database host", default="localhost"
    )
    parser.add_argument(
        "--user", help="The database user", required=True
    )
    parser.add_argument(
        "--password", help="The database password", required=True
    )
    parser.add_argument(
        "--db", help="The database name", default="home_assistant"
    )

    args = parser.parse_args(arguments)

    print(f"Connecting to database...")
    con = mysql.connector.connect(
        host = args.host,
        user = args.user,
        password = args.password,
        database = args.db
    )

    cur = con.cursor(buffered=True, dictionary=True)

    cur.execute(f"SELECT * FROM statistics_meta WHERE statistic_id = '{args.entity_id}'")

    result = cur.fetchone()

    meta_id = result["id"]
    print(f"Found metadata ID: '{meta_id}'")

    cur.execute(
        f"SELECT * FROM statistics WHERE metadata_id = '{meta_id}' ORDER BY start ASC"
    )

    first_row = cur.fetchone()
    first_state = first_row["state"]
    prev_state = first_state

    update_query = "UPDATE statistics SET state = %s, sum = %s WHERE id = %s"
    update_cur = con.cursor()

    print("Fixing statistics...")

    for row in cur:
        new_state = prev_state if row["state"] == 0 else row["state"]
        new_sum = new_state - first_state

        update_cur.execute(update_query, (new_state, new_sum, row["id"]))
        prev_state = new_state

    update_cur.execute("UPDATE `statistics_short_term` SET `sum` = `state`-%s WHERE `metadata_id`=%s", (first_state, meta_id))

    print("Commit to database...")

    con.commit()
    con.close()

    print("Done!")


if __name__ == "__main__":
    sys.exit(main(sys.argv[1:]))