def create_database():
if not os.path.exists(db_filename):
try:
conn = sqlite3.connect(db_filename)
cursor = conn.cursor()
cursor.execute(
'CREATE TABLE client ([client_id] INTEGER PRIMARY KEY AUTOINCREMENT, [client_name] text, [power_type] text, [power_level] integer, [last_reading_date] date)'
)
cursor.execute(
'CREATE TABLE sensor ([sensor_id] INTEGER PRIMARY KEY AUTOINCREMENT, [client_id] integer, [sensor_name] text, FOREIGN KEY(client_id) REFERENCES client(client_id))'
)
cursor.execute(
'CREATE TABLE sensor_data ([sensor_id] integer primary key, [sensor_value] numeric, [last_reading_date] date, FOREIGN KEY(sensor_id) REFERENCES sensor(sensor_id))'
)
cursor.execute(
'CREATE TABLE historical_data ([reading_date] date, [sensor_id] INTEGER, [sensor_value] numeric, FOREIGN KEY(sensor_id) REFERENCES sensor(sensor_id))'
)
conn.commit()
cursor.close()
except sqlite3.Error as error:
logFactory.GetLogger("app").error(error)
finally:
if (conn):
conn.close()
def get_client_history(client_name, sensor_name):
try:
conn = sqlite3.connect(db_filename)
cursor = conn.cursor()
values = []
query = cursor.execute(
"SELECT reading_date, sensor_value FROM historical_data WHERE client_name=? AND sensor_name=?",
[client_name, sensor_name])
rows = query.fetchall()
for row in rows:
values.append({'reading_date': row[0], 'sensor_value': row[1]})
cursor.close()
return {
'client_name': client_name,
'sensor_name': sensor_name,
'values': values
}
except sqlite3.Error as error:
logFactory.GetLogger("app").error(error)
finally:
if (conn):
conn.close()
def get_client_data(client_name):
try:
conn = sqlite3.connect(db_filename)
cursor = conn.cursor()
query = cursor.execute(
"SELECT client_id, power_type, power_level, last_reading_date FROM client WHERE client_name=?",
[client_name])
values = query.fetchone()
if values is None or len(values) == 0:
return None
cursor.close()
return {
'client_id': values[0],
'client_name': client_name,
'power_type': values[1],
'power_level': values[2]
}
except sqlite3.Error as error:
logFactory.GetLogger("app").error(error)
finally:
if (conn):
conn.close()
def get_all_clients():
try:
conn = sqlite3.connect(db_filename)
cursor = conn.cursor()
clients = []
query = cursor.execute(
"SELECT client_id, client_name, power_type, power_level, last_reading_date FROM client ORDER BY client_name"
)
rows = query.fetchall()
for row in rows:
clients.append({
'client_id': row[0],
'client_name': row[1],
'power_type': row[2],
'power_level': row[3],
'last_reading': row[4]
})
cursor.close()
return clients
except sqlite3.Error as error:
logFactory.GetLogger("app").error(error)
finally:
if (conn):
conn.close()
def record_client_data(client_name, power, powerlevel, sensors):
try:
conn = sqlite3.connect(db_filename)
cursor = conn.cursor()
dateNow = datetime.now(timezone.utc)
client_id = get_client_id(cursor, client_name)
if client_id is None:
client_id = create_client(cursor, dateNow, client_name, power,
powerlevel)
else:
update_client(cursor, dateNow, client_id, power, powerlevel)
if power == "DC":
power_level_id = get_sensor_id(cursor, client_id, "power_level")
if power_level_id is None:
power_level_id = create_sensor(cursor, client_id,
"power_level")
create_historical_data(cursor, dateNow, power_level_id, powerlevel)
for sensor in sensors:
sensor_id = get_sensor_id(cursor, client_id, sensor)
if sensor_id is None:
sensor_id = create_sensor(cursor, client_id, sensor)
if not sensor_data_exists(cursor, sensor_id):
create_sensor_data(cursor, dateNow, sensor_id, sensors[sensor])
else:
update_sensor_data(cursor, dateNow, sensor_id, sensors[sensor])
create_historical_data(cursor, dateNow, sensor_id, sensors[sensor])
conn.commit()
cursor.close()
except sqlite3.Error as error:
logFactory.GetLogger("app").error(error)
except Exception:
traceback.print_exc(file=sys.stdout)
finally:
if (conn):
conn.close()