def __init__(self) -> None:
self.__headers = Youless.web("HEADERS") # acceptable html headers
self.__url = Youless.web("URL") # base url
self.__ele = Youless.web("ELE")
self.__gas = Youless.web("GAS")
self.__snul = Youless.web("S0")
self.__json = Youless.web("JSON")
self.__month = Youless.web("M")
self.__day = Youless.web("W")
self.__kwh = Youless.sql("valuenaming")[0]
self.__m3 = Youless.sql("valuenaming")[1]
self.__watt = Youless.sql("valuenaming")[2]
self.__ltr = Youless.sql("valuenaming")[3]
def plot_month_day(self, year, month, etype):
"""Reads from table yeardays_X and returns figure.
plot one month of the year into a graph with daily totals.
examples:
:plot_month_day(2021, 1, 'E')
:plot_month_day(2021, 'January', 'G')
args:
:year as integer
:month as integer or string
:etype as string ('E' for Electricity, 'G' for Gas)
"""
self.year = year
self.month = month
self.etype = etype
self.table = Youless.sql("dbtables")[self.etype][0]
self.columns = [
Youless.lang("D"),
Youless.lang("KH"),
Youless.lang("KWH")
] if self.etype == 'E' else [
Youless.lang("D"),
Youless.lang("KM"),
Youless.lang("M3")
] if self.etype == 'G' else None
if (type(self.month) == str):
self.mN = datetime.datetime.strptime(self.month, '%B')
self.month = int(self.mN.date().strftime('%m'))
self.monthName = datetime.date(1900, self.month, 1).strftime('%B')
lst = self.data.retrieve_month(
self.year, self.month, self.table) # retrieve data from database
if lst == 0:
logger.error("No Data")
return 0
data = {}
high = max(lst[2]) + max(lst[2]) / 3
totalUsage = 0
for n, elem in enumerate(lst[2]):
data[n +
1] = elem # Add kWh values to dictionary with the day as key
totalUsage += elem
df = pd.DataFrame(data.items(), columns=self.columns[:2])
fig = px.bar(
df,
x=df[self.columns[0]],
y=df[self.columns[1]],
range_y=(0, high),
title=(Youless.lang('yearmonthtitle') %
(self.monthName, self.year, self.columns[1], totalUsage,
self.columns[2])),
)
logger.debug("Plotting month %d of year %d" % (self.month, self.year))
return fig
def main():
if not youless_sql.isSqlite3Db(path):
logger.warning("Database {} non existant, creating database".format(Settings.dbname))
for v in Youless.sql("dbtables").values():
for i in v:
con = sl.connect(path)
with con:
con.execute(Youless.sql("queries")[i])
logger.warning("Table {} created".format(i))
else:
logger.warning("Database {} exists, checking and creating tables".format(Settings.dbname))
for v in Youless.sql("dbtables").values():
for i in v:
if (youless_sql.is_table_exists(i)):
logger.warning("Table {} already exists, doing nothing".format(i))
else:
logger.warning("Table {} does not exist, creating".format(i))
con = sl.connect(path)
with con: # CREATE TABLE
con.execute(Youless.sql("queries")[i])
def plot_year_month(self, year, etype):
"""Reads from table yeardays_X and returns figure.
plot the year with month totals into a graph.
example:
:plot_year(2021, 'E')
args:
:year as integer
:etype as string ('E' for Electricity, 'G' for Gas)
"""
self.year = year
self.etype = etype
self.table = Youless.sql("dbtables")[self.etype][0]
self.columns = [
Youless.lang("M"),
Youless.lang("KH"),
Youless.lang("KWH")
] if self.etype == 'E' else [
Youless.lang("M"),
Youless.lang("KM"),
Youless.lang("M3")
] if self.etype == 'G' else None
self.type = 1 # equals year with month totals
lst = self.data.retrieve_year(self.year, self.type, self.table)
if lst == 0:
logger.error("No Data")
return 0
data = {}
months = {}
totalUsage = 0.0
maxLst = []
for n, elem in enumerate(lst):
data[lst[n][1]] = lst[n][3]
months[lst[n][1]] = datetime.date(1900, int(lst[n][1]),
1).strftime('%B')
totalUsage += lst[n][3]
maxLst.append(lst[n][3])
high = max(maxLst) + max(maxLst) / 3
df = pd.DataFrame(data.items(), columns=(self.columns[:2]))
fig = px.bar(
df,
x=df[self.columns[0]],
y=df[self.columns[1]],
range_y=(0, high),
title=(Youless.lang('yeartitle') %
(self.year, self.columns[1], totalUsage, self.columns[2])),
)
fig.update_layout(xaxis=dict(tickmode='array',
tickvals=list(months.keys()),
ticktext=list(months.values())))
logger.debug("Plotting year %d" % (self.year))
return fig
def is_table_exists(table):
"""youless_sql.is_table_exists(table_name) -> True if exists, False if not"""
con = sl.connect(path)
with con:
check = Youless.sql("queries")["table_exist"]
table = (table,)
run = con.execute(check, table)
e = int(run.fetchone()[0]) # returns 1 if exists
rtn = True if (e != 0) else False # 1 == True else False
logger.debug("table {} existence is {}".format(table, rtn))
return rtn
def plot_year_day(self, year, etype):
"""Reads from table yeardays_X and returns figure.
plot the year with daily totals into a graph.
example:
:plot_year_day(2021, 'E')
args:
:year as integer
:etype as string ('E' for Electricity, 'G' for Gas)
"""
self.year = year
self.etype = etype
self.table = Youless.sql("dbtables")[self.etype][0]
self.columns = [
Youless.lang("D"),
Youless.lang("KH"),
Youless.lang("KWH")
] if self.etype == 'E' else [
Youless.lang("D"),
Youless.lang("KM"),
Youless.lang("M3")
] if self.etype == 'G' else None
self.type = 2 # equals year with month totals
lst = self.data.retrieve_year(self.year, self.type, self.table)
if lst == 0:
logger.error("No Data")
return 0
data = {}
totalUsage = 0.0
maxLst = []
for n, elem in enumerate(lst):
self.month = int(lst[n][1])
for y, value in enumerate(lst[n][3]):
tempkey = ("%d-%d-%d" % (self.year, self.month, y + 1)
) # year-month-day
data[tempkey] = value
totalUsage += value
maxLst.append(value)
high = max(maxLst) + max(maxLst) / 3
df = pd.DataFrame(data.items(), columns=self.columns[:2])
fig = px.bar(
df,
x=df[self.columns[0]],
y=df[self.columns[1]],
range_y=(0, high),
title=(Youless.lang('yeardaytitle') %
(self.year, self.columns[1], totalUsage, self.columns[2])),
)
logger.debug("Plotting year %d" % (self.year))
return fig
def retrieve_year(self, year, totals, table):
"""Retrieve data from table yeardays_X and return list with items.
Retrieves available data for given year per month and day in values.
Returns either month totals or day totals for the entire year.
example:
retrieve_year(2021, 2, 'yeardays_g')
args:
:year as integer
:totals as integer (1 is month totals, 2 is day per month totals)
:table as string
"""
self.year = year
self.totals = totals
self.table = table
if (self.totals != 1 and self.totals != 2):
logger.error("error: type can only be 1 or 2")
return "error: type can only be 1 or 2"
logger.debug("Starting year retrieval %d" % (self.year))
self.cur = self.conn.cursor()
with self.conn:
self.query = (Youless.sql("queries")["so_yeardays"] %
(self.table, str(self.year)))
data = self.conn.execute(self.query)
rows = len(data.fetchall())
logger.debug("Rows retrieved: %d" % rows)
if (rows >= 1):
data = self.conn.execute(self.query)
self.lst = []
for row in data:
self.values = row[4] # x is string representation of list
self.values = ast.literal_eval(
self.values) # convert x to real list
total = 0
for v, n in enumerate(self.values):
self.values[v] = float(self.values[v])
total += float(self.values[v])
if (self.totals == 1): # month totals
self.tmplst = [[row[1], row[2], row[3], int(total)]]
elif (self.totals == 2): # day totals
self.tmplst = [[row[1], row[2], row[3], self.values]]
self.lst.extend(self.tmplst)
self.tmplst.clear()
else:
return 0
self.conn.close()
logger.debug("Retrieved list:")
logger.debug(self.lst)
return self.lst
def retrieve_day(self, year, month, day, table):
"""Retrieve data from table dayhours_X and return list with items.
Retrieves day data for given year, month and day in a list with hours and values.
example:
retrieve_day(2021, 1, 19, 'dayhours_e')
args:
:year as integer
:month as integer
:day as integer
:table as string
"""
self.year = year
self.month = month
self.day = day
self.table = table
logger.debug("Starting day retrieval %d %d %d from table %s" %
(self.year, self.month, self.day, self.table))
self.cur = self.conn.cursor()
with self.conn:
self.query = (
Youless.sql("queries")["s_dayhours"] %
(self.table, str(self.year), str(self.month), str(self.day)))
data = self.cur.execute(self.query)
rows = len(self.cur.fetchall())
logger.debug("Rows retrieved: %d" % rows)
if (rows >= 1):
data = self.cur.execute(
self.query
) # execute query again because fetchall() mangles the data to a list of tuples with strings
for row in data:
self.values = row[7] # x is string representation of list
self.values = ast.literal_eval(
self.values) # convert x to real list
for v, n in enumerate(self.values):
self.values[v] = float(self.values[v])
else:
return 0
self.conn.close()
self.lst = [self.year, self.month, self.day, self.values]
logger.debug("Retrieved list:")
logger.debug(self.lst)
return self.lst
def parse_tenminutes(self, etype, data):
"""Function to parse the information retrieved by read_ten_minutes
example:
parse_tenminutes(etype, data)
args:
:etype is 'E' or 'G' for Electricity or Gas
:data is a list with tuples retrieved by read_ten_minutes
"""
self.__table = Youless.sql("dbtables")[etype][2]
self.__type = self.__watt if etype == 'E' and etype != 'G' else self.__ltr
logger.debug("Connected to table {}".format(self.__table))
for key in data.keys():
write_data().write_tenminutes(self.conn, (key, data[key]),
self.__table, self.__type)
self.conn.close()
logger.debug("Database connection closed...")
def parse_months(self, etype, data):
"""Function to parse the information retrieved by retrieve_months
example:
parse_months(etype, data)
args:
:etype is 'E' or 'G' for Electricity or Gas
:data is a list with tuples retrieved by retrieve_months
"""
self.__table = Youless.sql("dbtables")[etype][0]
self.__type = self.__kwh if etype == 'E' and etype != 'G' else self.__m3
logger.debug("Connected to table {}".format(self.__table))
for item in data:
write_data().write_yeardays(self.conn, item, self.__table,
self.__type)
self.conn.close()
logger.debug("Database connection closed...")
def check_existence(self, **kwargs):
"""function to check if item exists in sqlite database.\n
returns 1 if row exists and 0 if not.
:table= str
:column= str
:item= str/int (int gets transformed to str in process)
"""
if self.conn is None:
return None
self.table = kwargs.get('table')
self.column = kwargs.get('column')
self.item = kwargs.get('item')
self.q = Youless.sql('queries')['if_exists']
self.query = self.q.format(self.table, self.column, self.item)
self.cur = self.conn.cursor()
with self.conn:
self.data = self.cur.execute(self.query)
self.outcome = [row for row in self.data]
self.conn.close()
return int(self.outcome[0][0])
def retrieve_month(self, year, month, table):
"""Retrieve data from table yeardays_X and return list.
Retrieves monthdata for given month of given year in a list with days and values
example:
retrieve_month(2021, 1, 'yeardays_e')
args:
:year as integer
:month as integer
:table as string
"""
self.year = year
self.month = month
self.table = table
logger.debug("Starting month retrieval %d %d from table %s" %
(self.year, self.month, self.table))
self.cur = self.conn.cursor()
with self.conn:
self.query = (Youless.sql("queries")["s_yeardays"] %
(self.table, str(self.year), str(self.month)))
data = self.conn.execute(self.query)
rows = len(data.fetchall())
logger.debug("Rows retrieved: %d" % rows)
if (rows >= 1):
data = self.conn.execute(self.query)
for row in data:
self.values = row[4] # x is string representation of list
self.values = ast.literal_eval(
self.values) # convert x to real list
for v, n in enumerate(self.values):
self.values[v] = float(self.values[v])
else:
return 0
self.conn.close()
self.lst = [self.year, self.month, self.values]
logger.debug("Retrieved list:")
logger.debug(self.lst)
return self.lst
def get_item(self, **kwargs):
"""returns item from database corresponding to the given query and kwargs
examples:
retrieve_custom_data().get_item(query='select_one_and',
select=('*'),
table='dayhours_e',
id='year',
item='2021',
id2='yearday',
item2='44')
retrieve_custom_data().get_item(query='select_one',
select='yearday,watt',
table='dayhours_e',
id='year',
item='2021')
kwargs:
:query= str
:select= str
:table= str
:id= str
:item= str
:id2= str (optional)
:item2= str (optional)
"""
if self.conn is None:
return None
self.q = Youless.sql('queries')[kwargs.get('query')]
self.query = self.q % tuple(kwargs.values())[1:]
self.cur = self.conn.cursor()
with self.conn:
self.data = self.cur.execute(self.query)
self.outcome = [row for row in self.data]
self.conn.close()
return self.outcome
def get_dayhours_average(self, **kwargs):
"""gets data from table dayhours_X based on yearday numbers and return average for:
:specified weekday in a year,
:specified week in a year,
:specified month in a year,
:specified weekday in a month in a year.
kwargs:
:year= int: 2021
:day= dayname
:month= monthname
:etype= 'E' or 'G'
"""
if 'year' in kwargs: # check if year is passed as keyword argument
self.year = kwargs.get('year')
self.start = parser.parse(
f'{self.year} 1 1'
) # a full year is always from january 1st to december 31st
self.end = parser.parse(
f'{self.year} 12 31'
) # a full year is always from january 1st to december 31st
# self.start = datetime(self.year, 1, 1) # a full year is always from january 1st to december 31st
# self.end = datetime(self.year, 12, 31) # a full year is always from january 1st to december 31st
if 'year' in kwargs and 'month' in kwargs:
self.month = kwargs.get('month').lower().capitalize()
self.start = parser.parse(
f'{self.year} {self.month} 1'
) # a month always starts at day 1, it is always the same
self.end = parser.parse(
f'{self.year} {self.month} {calendar.monthrange(self.year, self.start.month)[1]}'
) # get last day of month from calendar
# self.start = datetime(self.year, datetime.strptime(self.month, '%B').month, 1) # a month always starts at day 1, it is always the same
# self.end = datetime(self.year,
# datetime.strptime(self.month, '%B').month,
# calendar.monthrange(self.year, datetime.strptime(self.month, '%B').month)[1]) # get last day of month from calendar
if 'day' in kwargs:
self.day = kwargs.get('day').lower().capitalize()
if 'week' in kwargs:
self.week = kwargs.get('week')
self.start = retrieve_custom_data().get_date_range_from_week(
year=self.year, week=self.week)[2]
self.end = retrieve_custom_data().get_date_range_from_week(
year=self.year, week=self.week)[3]
if 'etype' in kwargs:
self.table = Youless.sql('dbtables')[kwargs.get('etype')][1]
self.select = Youless.sql('av_select')[kwargs.get('etype')]
if 'day' not in kwargs:
self.get_yeardays = retrieve_custom_data().get_yeardays(
start=self.start, end=self.end)
else:
self.get_yeardays = retrieve_custom_data().get_yeardays(
start=self.start, end=self.end, day=self.day)
logger.debug("get_yeardays: %s" % self.get_yeardays)
check_exist = [
yearday.lstrip('0') for yearday in self.get_yeardays
if retrieve_custom_data().check_existence(
table=self.table, column='yearday', item=yearday) == 1
]
logger.debug("exists yearday check: %s" % check_exist)
if check_exist == []:
return None
self.get_item = [[
i for i in retrieve_custom_data().get_item(query='select_one_and',
select=self.select,
table=self.table,
id='year',
item=self.year,
id2='yearday',
item2=n)
] for n in check_exist]
logger.debug("get_item: %s" % self.get_item)
lists = [list(elem[0]) for elem in self.get_item
] # remove nested tuple and change to list
lists = [
[elem[0], ast.literal_eval(elem[1])] for elem in lists
] # convert every 2nd item in the list to a real list (was a string)
lists = [[elem[0], int(sum(elem[1]) / len(elem[1]))] for elem in lists
] # get the sum of all values of retrieved day
average = int(sum(elem[1] for elem in lists) /
len(lists)) # get the average over all said days
# return the average and the amount of days the average is based on including the provided kwargs
if 'day' in dir(self):
if 'month' in dir(self):
return self.year, self.day, self.month, self.table, average, len(
lists)
else:
return self.year, self.day, self.table, average, len(lists)
elif 'month' in dir(self):
return self.year, self.month, self.table, average, len(lists)
else:
return self.year, self.week, self.table, average, len(lists)
def retrieve_hours(self, table, year, month, startday, starthour, *args):
"""Retrieve data from table dayhours_X and return list with items.
Retrieve hour data for given year, month and days with a minimum of 1 hour and a maximum of 24 hours in a list with hours and values.
Since we dont know if the end day/hour is the same as the start day/hour we use *args to accept this.
When only startday and starthour is given, only that single hour will be retrieved.
Examples:
retrieve_hours('dayhours_g', 2021, 3, 2, 3, 3, 6) # year: 2021, month: 3, startday: 2, starthour: 5, endday: 3, endhour: 6\n
retrieve_hours('dayhours_e', 2020, 10, 1, 12) # year: 2020, month: 10, startday: 1, starthour: 12\n
retrieve_hours('dayhours_e', 2020, 11, 2, 11, 18) # year: 2020, month: 11, startday: 2, starthour: 11, endhour: 18\n
args:
:table as string
:year as integer
:month as integer (min is 1, max is 12)
:startday as integer (min is 1, max is 30)
:endday as integer (min is 2, max is 31)
:starthour as integer (min is 0 (00:00), max is 23 (23:00))
:endhour as integer (min is 1 (01:00), max is 24 (24:00))
"""
self.table = table
self.year = year
self.month = month
self.startday = startday
self.starthour = starthour
self.hourlist = []
if (len(args) == 2):
self.endday = args[0]
self.endhour = args[1]
self.query = (Youless.sql("queries")["s_dayhours2"] %
(self.table, self.year, self.month, self.startday,
self.endday))
self.lst = [
self.year, self.month, self.startday, self.starthour,
self.endday, self.endhour, self.hourlist
]
elif (len(args) == 1):
self.endhour = args[0]
self.query = (Youless.sql("queries")["s_dayhours"] %
(self.table, self.year, self.month, self.startday))
self.lst = [
self.year, self.month, self.startday, self.starthour,
self.endhour, self.hourlist
]
else:
self.query = (Youless.sql("queries")["s_dayhours"] %
(self.table, self.year, self.month, self.startday))
self.lst = [
self.year, self.month, self.startday, self.starthour,
self.hourlist
]
logger.debug("Starting hour retrieval from table %s" % self.table)
self.cur = self.conn.cursor()
with self.conn:
data = self.cur.execute(self.query)
rows = (len(self.cur.fetchall()))
logger.debug("Rows retrieved: %d" % rows)
if (rows >= 1):
data = self.cur.execute(
self.query
) # execute query again because fetchall() mangles the data to a list of tuples with strings
rowcount = 0
for row in data:
rowcount += 1
self.values = row[7] # x is string representation of list
self.values = ast.literal_eval(
self.values) # convert x to real list
for n, v in enumerate(self.values):
self.values[n] = int(self.values[n])
if (rowcount == 1) and (n >= self.starthour):
self.hourlist.append((n, v))
if (len(args) == 1) and (n == self.endhour):
break
elif (rowcount == 2) and (n <= self.endhour):
self.hourlist.append((n, v))
else:
return 0
self.conn.close()
logger.debug("Retrieved list:")
logger.debug(self.lst)
return self.lst
def __init__(self) -> None:
global update_db
update_db = -1
self.elist = Youless.sql('energytypes')[
'list'] # retrieve energy types
def plot_hours(self, *args):
"""Reads from table dayhours_X and return figure.
Plot hours from given starthour up to endhour.
Plot spans a max of 2 days with a minimum of 1 hour and a maximum of 24 hours.
Minimum arguments is 5, maximum is 7 in this order:
:energytype: 'E', year: 2021, month: 3, startday: 2, starthour: 5, endday: 3, endhour: 6
:energytype: 'E', year: 2021, month: 3, startday: 2, starthour: 5, endhour: 6
:energytype: 'E', year: 2021, month: 3, startday: 2, starthour: 5
plot_hours(arguments) examples:
:plot_hours("E", 2021, 3, 2, 12, 3, 11)
:plot_hours("E", 2021, 3, 2, 12, 11)
:plot_hours("E", 2021, 3, 2, 12)
"""
if (len(args) > 7):
return logger.warning("Maximum arguments of 7 exceeded")
elif (len(args) < 5):
return logger.warning("Minimum arguments is 5")
self.etype = args[0]
self.table = Youless.sql("dbtables")[self.etype][1]
self.year = args[1]
self.month = args[2]
self.startday = args[3]
self.starthour = args[4]
if (len(args) == 7):
self.endday = args[5]
self.endhour = args[6]
lst = self.data.retrieve_hours(
self.table, self.year, self.month, self.startday,
self.starthour, self.endday,
self.endhour) # retrieve data from database
elif (len(args) == 6):
self.endhour = args[5]
lst = self.data.retrieve_hours(
self.table, self.year, self.month, self.startday,
self.starthour, self.endhour) # retrieve data from database
else:
lst = self.data.retrieve_hours(
self.table, self.year, self.month, self.startday,
self.starthour) # retrieve data from database
if lst == 0:
logger.error("No Data")
return 0
self.columns = [
Youless.lang("U"),
Youless.lang("W"),
Youless.lang("KWH")
] if self.etype == 'E' else [
Youless.lang("U"),
Youless.lang("L"),
Youless.lang("M3")
] if self.etype == 'G' else None
self.hours = lst[-1]
data = {}
highlst = []
total = 0
for n, v in enumerate(self.hours):
data[self.hours[n][0]] = int(self.hours[n][1])
highlst.append(self.hours[n][1])
total += int(self.hours[n][1])
high = int(max(highlst) + max(highlst) / 3)
df = pd.DataFrame(data.items(), columns=self.columns[:2])
logger.debug(df)
self.month = datetime.date(1900, int(self.month), 1).strftime('%B')
self.title = (Youless.lang('dayhourtitle') %
(self.month, self.startday, self.year, self.columns[1],
int(total / 1000), self.columns[2]) if
('endday'
not in dir(self)) else Youless.lang('customhourtitle') %
(self.startday, self.endday, self.month, self.year,
self.columns[1], int(total / 1000), self.columns[2]))
fig = px.bar(
df,
x=df[self.columns[0]],
y=df[self.columns[1]],
range_y=(0, high),
title=(self.title),
height=500,
)
fig.update_layout(
xaxis_type=
'category' # change x axis type so that plotly does not arrange overlapping day hours
)
logger.debug(fig)
return fig
def write_dayhours(self, conn, insertion, table, type):
"""Internal function to store values in sqlite3 database in the following format (all strings):
date, year, week, month, monthname, day, yearday, values per hour
example insertion:
('2021-04-03', 2021, 13, 4, 'April', 3, 93, '[428.0, 385.0, 400.0, 391.0, 386.0, 398.0, 403.0, 485.0, 759.0, 611.0, 650.0, 1225.0, 626.0, 940.0, 534.0, 630.0, 751.0, 630.0, 1194.0, 951.0, 934.0, 893.0, 628.0, 581.0]')
"""
self.table = table
self.type = type
self.sql = Youless.sql("queries")["i_dayhours"]
self.query = Youless.sql("queries")["s_table"]
self.datequery = (insertion[0].strip(), ) # create primary key check
self.date = insertion[0] # create primary key check
cur = conn.cursor()
self.check = cur.execute(
(self.query % self.table),
self.datequery) # check the database for existing primary keys
x = insertion[7] # x is string representation of list
x = ast.literal_eval(x) # convert x to real list
lenX = len(x)
existing_entry = self.check.fetchall(
) # fetch the complete entry from the database
try:
existing_key = existing_entry[0][0] # assign existing key
existing_values = existing_entry[0][
7] # assign existing value string
existing_values = ast.literal_eval(
existing_values
) # convert string representation of list to real list
first_set = set(existing_values) # create set from existing values
sec_set = set(x) # create set from new values
differences = (first_set - sec_set).union(
sec_set - first_set) # compare differences between two sets
differences = len(differences)
except Exception:
logger.debug("no existing Primary Key for {}".format(insertion[0]))
if ('existing_key' not in locals()):
logger.debug("double check for existing_key, no existing_key.")
existing_key = None
if (existing_key is not None) and (
differences == 0
): # check if the primary key exists and list has 24 hour values
logger.debug(
"Primary key %s exists, has %i entries and %i differences, skipping!"
% (self.date, lenX, differences))
return
else:
try:
logger.debug(
"Primarykey %s has %i entries and/or %i differences. Overwriting and appending data!"
% (self.date, lenX, differences))
except Exception:
logger.debug(
"existing_key variable was not created, assigning None")
try:
cur.execute((self.sql % (self.table, self.type)), insertion)
logger.debug("Updating the database with: {}".format(insertion))
except Exception as E:
logger.error(
"An error occured, skipping the update.\n Error: {}\n sql: {}".
format(E, insertion))
pass
conn.commit()
return cur.lastrowid
def write_tenminutes(self, conn, insertion, table, type):
"""Internal function to store values in sqlite3 database in the following format (all strings):
date, year, week, month, monthname, day, yearday, values per hour
example insertion:
('2021-07-16', [('2021', '28', '07', 'July', '16', '197'), [{'23:50': 264}, {'23:40': 432}, {'23:30': 600}, {'23:20': 648}, {'23:10': 540}]])
example converted_insertion:
('2021-07-16', '2021', '28', '07', 'July', '16', '197', "[{'23:50': 264}, {'23:40': 432}]")
"""
self.table = table
self.type = type
self.sql = Youless.sql("queries")["i_daytenminutes"]
self.query = Youless.sql("queries")["s_table"]
self.datequery = (insertion[0].strip(), ) # create primary key check
self.date = insertion[0] # create primary key check
converted_insertion = (insertion[0], insertion[1][0][0],
insertion[1][0][1], insertion[1][0][2],
insertion[1][0][3], insertion[1][0][4],
insertion[1][0][5], f'{insertion[1][1]}')
cur = conn.cursor()
self.check = cur.execute(
(self.query % self.table),
self.datequery) # check the database for existing primary keys
new_list = insertion[1][1] # x list of key:value dictionaries
lenX = len(
new_list
) # amount of key:value items (144 items of 10 minutes for a 24 hour day)
existing_entry = self.check.fetchall(
) # fetch the complete entry from the database
try:
existing_key = existing_entry[0][0] # assign existing key
existing_values = ast.literal_eval(
existing_entry[0]
[7]) # convert string representation of list to real list
existing_list = existing_values
# source: https://stackoverflow.com/questions/9845369/comparing-2-lists-consisting-of-dictionaries-with-unique-keys-in-python
list_pair = zip(existing_list, new_list) # create zipped list pair
if any(
x != y for x, y in list_pair
) is True: # compare the lists with dictionaries for differences
differences = [(x, y) for x, y in list_pair if x != y
] # if there are any then get the differences
differences = len(differences) # get the amount of differences
else:
differences = 0
except Exception as E:
logger.debug("{}, no existing Primary Key for {}".format(
E, insertion[0]))
if ('existing_key' not in locals()):
logger.debug(
"double check for existing_key {}, no existing_key.".
format(insertion[0]))
differences = 0
existing_key = None
if (existing_key is not None) and (
differences == 0
): # check if the primary key exists and list has 24 hour values
logger.debug(
"Primary key %s exists, has %i entries and %i differences, skipping!"
% (self.date, lenX, differences))
return
else:
try:
logger.debug(
"Creating primary key %s with %i entries and/or %i differences. Overwriting and appending data!"
% (self.date, lenX, differences))
except Exception as E:
logger.debug(
"existing_key variable was not created, assigning None")
logger.error(E)
try:
cur.execute((self.sql % (self.table, self.type)),
converted_insertion)
logger.debug(
"Updating the database with: {}".format(converted_insertion))
except Exception as E:
logger.error(
"An error occured, skipping the update.\n Error: {}\n sql: {}".
format(E, converted_insertion))
pass
conn.commit()
return cur.lastrowid
def write_yeardays(self, conn, insertion, table, type):
"""Internal function to store values in sqlite3 database in the following format (all strings):
date, year, month, monthname, values per day
example insertion:
('2020-12-01', 2020, 12, 'December',
'[18.85, 15.12, 19.72, 13.76, 13.93, 20.7, 17.66, 18.57, 14.14, 13.23, 12.72, 15.38, 16.89, 16.06,
15.39, 22.16, 15.0, 15.34, 12.61, 17.17, 18.85, 15.25, 20.22, 13.51, 15.35, 13.49, 12.99, 21.87, 14.2, 16.7, 15.45]')
"""
self.table = table
self.type = type
self.sql = Youless.sql("queries")["i_yeardays"]
self.query = Youless.sql("queries")["s_table"]
self.datequery = (insertion[0].strip(), ) # create primary key check
self.date = insertion[0] # create primary key check
cur = conn.cursor()
self.check = cur.execute(
(self.query % self.table),
self.datequery) # check the database for existing primary keys
x = insertion[4] # x is string representation of list
x = ast.literal_eval(x) # convert x to real list
lenX = len(x)
m = int(insertion[2])
existing_entry = self.check.fetchall(
) # fetch the complete entry from the database
try:
existing_key = existing_entry[0][0] # assign existing key
existing_values = existing_entry[0][
4] # assign existing value string
existing_values = ast.literal_eval(
existing_values
) # convert string representation of list to real list
first_set = set(existing_values) # create set from existing values
sec_set = set(x) # create set from new values
differences = (first_set - sec_set).union(
sec_set - first_set) # compare differences between two sets
differences = len(differences)
except Exception:
logger.debug("no existing Primary Key for {}".format(insertion[0]))
if ('existing_key' not in locals()):
logger.debug(
"existing_key variable was not created, assigning None")
existing_key = None
if (existing_key is not None) and (differences == 0):
logger.debug(
"Primary key %s exists and has %i differences, skipping!" %
(insertion[0], differences))
return
else:
try:
logger.debug(
"Primary key is %s and has %i differences. Overwriting and appending data!"
% (insertion[0], differences))
except Exception:
logger.debug("Primarykey did not exist. Creating new entry")
try:
cur.execute((self.sql % (self.table, self.type)), insertion)
logger.debug("Updating the database with: {}".format(insertion))
except Exception as E:
logger.error(
"An error occured, skipping the update.\n Error: {}\n sql: {}".
format(E, insertion))
pass
conn.commit()
return cur.lastrowid
def plot_day_hour(self, year, month, day, etype):
"""Reads from table dayhours_X and return figure.
Plot one day of the year into a graph with hourly totals.
examples:
:plot_day_hour(2021, 1, 19, 'E')
:plot_day_hour(2021, January, 19, 'G')
args:
:year as integer
:month as integer or string
:day as integer
:etype as string ('E' for Electricity, 'G' for Gas)
"""
self.year = year
self.month = month
self.day = day
self.etype = etype
self.table = Youless.sql("dbtables")[self.etype][1]
self.columns = [
Youless.lang("U"),
Youless.lang("W"),
Youless.lang("KWH")
] if self.etype == 'E' else [
Youless.lang("U"),
Youless.lang("L"),
Youless.lang("M3")
] if self.etype == 'G' else None
if (type(self.month) == str):
self.mN = datetime.datetime.strptime(self.month, '%B')
self.month = int(self.mN.date().strftime('%m'))
self.monthName = datetime.date(1900, self.month, 1).strftime('%B')
lst = self.data.retrieve_day(self.year, self.month, self.day,
self.table) # retrieve data from database
if lst == 0:
logger.error("No Data")
return 0
data = {}
high = max(lst[3]) + max(lst[3]) / 3
totalWatt = 0
for n, elem in enumerate(lst[3]):
data[
n +
1] = elem # Add watt values to dictionary with the hour as key
totalWatt += elem
df = pd.DataFrame(data.items(), columns=self.columns[:2])
fig = px.bar(
df,
x=df[self.columns[0]],
y=df[self.columns[1]],
range_y=(0, high),
title=(Youless.lang('dayhourtitle') %
(self.monthName, self.day, self.year, self.columns[1],
float(totalWatt / 1000), self.columns[2])),
)
logger.debug("Plotting month %d day %d of year %d from table %s" %
(self.month, self.day, self.year, self.table))
return fig
def __init__(self) -> None:
self.elist = Youless.sql('energytypes')[
'list'] # retrieve energy types