def update(dt: datetime, host_name: str, column: str, value: float):
if column not in TABLE_NAMES:
logger.error(f"Invalid column name specified: {column}")
else:
cmd = "INSERT IGNORE INTO {0}(datetime, host_name, value) VALUES (%s, %s, %s)".format(
column)
db_execute(cmd,
(dt.strftime("%Y-%m-%d %H-%M-%S.%f"), host_name, value))
logger.info(f"Updated {column} stats with {value} for {host_name}")
def host_update(date_time: str, host_name: str, column: str, data):
cmd = "INSERT IGNORE INTO hosts(last_updated, name) VALUES(%s, %s)"
db_execute(cmd, (
date_time,
host_name,
))
if column is not None:
cmd = "UPDATE hosts SET last_updated=%s, {}=%s WHERE name=%s".format(
column)
db_execute(cmd, (date_time, data, host_name))
logger.info(f"Updated {column} to {data} stats for {host_name}")
def get_last_state(host_name):
cmd = "SELECT state FROM outlet_stats WHERE host_name=%s AND state IS NOT NULL ORDER BY datetime desc LIMIT 1"
res = db_execute(cmd, (host_name, ))
res = res.fetchone()
if res is not None and len(res) == 1:
return res[0]
else:
return None
def update_stat(dt: datetime, host_name: str, column: str, data):
if column not in get_supported_columns():
logger.debug("Ignoring unsupported stat '{}'".format(column))
return
latest = get_latest_row(host_name)
logger.debug("Latest row in DB for host_name {} is {}".format(
host_name, latest))
if latest is None or latest[0] < dt - timedelta(
seconds=3) or latest[0] > dt + timedelta(seconds=3):
cmd = "INSERT IGNORE INTO outlet_stats({}, host_name, datetime) VALUES(%s, %s, %s)".format(
column)
sql_data = (data, host_name,
dt.strftime(config.General.DateTimeFormat))
logger.debug("Adding a new row, {}:{} {}={}".format(
dt, host_name, column, data))
else:
cmd = "UPDATE outlet_stats SET {}=%s WHERE host_name=%s AND datetime=%s".format(
column)
sql_data = (data, host_name,
latest[0].strftime(config.General.DateTimeFormat))
logger.debug("Updating existing row, {}:{} {}={}".format(
latest[0], host_name, column, data))
logger.info(f"Updated {column} stat to {data} for {host_name}")
try:
db_execute(cmd, sql_data)
except mysql.connector.errors.IntegrityError as e:
logger.warn("Ingegrity error")
if str(e).startswith("1062"):
logger.warn("Tried to add this entry twice?", e, "\n", cmd,
sql_data)
else:
host_update(host_name, None, None)
db_execute(cmd, sql_data)
if column != "state":
carry_last_state(host_name, sql_data[-1])
def init():
for create_table in CREATE_TABLES:
db_execute(create_table)
def carry_last_state(host_name: str, dt: str):
last_state = get_last_state(host_name)
cmd = "UPDATE outlet_stats SET state=%s WHERE host_name=%s AND datetime=%s"
db_execute(cmd, (last_state, host_name, dt))
logger.debug("Carried last state")
def get_latest_row(host_name):
cmd = "SELECT datetime, state, current, voltage, power, energy FROM outlet_stats WHERE host_name=%s ORDER BY datetime desc LIMIT 1"
res = db_execute(cmd, (host_name, ))
res = res.fetchone()
return res
def get_supported_columns():
cursor = db_execute(
"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'outlet_stats'"
)
stats = tuple(c[0] for c in cursor.fetchall())
return stats
def init():
"""This is run once the database is connected. Do anything that needs to be done only once per script execution here."""
for create_table in CREATE_TABLES:
db_execute(create_table)