Building a home energy monitoring dashboard for your clevrHome

timescaledb.py

from datetime import datetime
from sqlalchemy import create_engine, text

# Database connection string
db_conn = "postgresql://username:password@192.168.2.10:5432/tsdb"

# Write the data to the database
def WriteSQLToDB(timestamp: datetime, tag_name: str, tag_value: float) -> None:
    try:
        sqlEngine = create_engine(db_conn, pool_recycle=300, pool_pre_ping=True, connect_args={"connect_timeout": 20})
        
        sql = f"INSERT INTO sensors_data (time, tagname, tagvalue) VALUES ('{timestamp}', '{tag_name}', {tag_value})"

        with sqlEngine.connect() as connection:
            connection.execute(text(sql))
            connection.commit()
        print("-- DB: Records inserted.")
    except:
        print("** DB: Could not save to database for '{tag_name}'.")
    finally:
        sqlEngine.dispose()