def save_data(self, weather):
"""Method that inserts a dictionary of weather into the DB"""
try:
sql = """INSERT OR REPLACE INTO samples (sample_date,location,max_temp,min_temp,avg_temp)
values (?,?,?,?,?)"""
myArray = []
for date, value in weather.items():
try:
myArray.append(date)
myArray.append('Winnipeg, MB')
except Exception as e:
loggin.error("Error appending the data.", e)
for k, v in value.items():
try:
myArray.append(v)
except Exception as e:
logging.error("Error appending values to the array.", e)
with DBCM("weather.sqlite") as openDB:
openDB.execute(sql, myArray)
myArray.clear()
except Exception as e:
logging.error("Error inserting sample.", e)
def save_data(self, data, month, year):
""" This method is used to store the data that is collected into the db """
try:
sql = """insert into samples (sample_date, location, max_temp, min_temp, avg_temp)values (?,?,?,?,?)"""
location = "Winnipeg, MB"
for day, temps in data.items():
""" Iterates through each day in data adding that days data to a list. """
try:
set_data = list()
set_data.append(f"{str(year)}-{month:02d}-{str(day)}")
set_data.append(location)
for key, value in temps.items():
""" Iterates through the days data adding it to a list that is used with the insert statement. """
try:
set_data.append(value)
except Exception as e:
logging.error(
f"dboperations:save_data:loop:2, {e}")
with DBCM(self.database) as cursor:
cursor.execute(sql, set_data)
except Exception as e:
logging.error(f"dboperations:save_data:loop, {e}")
except Exception as e:
logging.error(f"dboperations:save_data, {e}")
def purge_data(self):
""" This method is used to purge the table in the database """
try:
if self.is_table_exist():
with DBCM(self.database) as cursor:
cursor.execute("drop table samples")
except Exception as e:
logging.error(f"dboperations:purge_data, {e}")
def fetch_last(self):
""" This method is used to get the most recent date from the db """
try:
with DBCM(self.database) as cursor:
cursor.execute(
"select sample_date from samples order by DATE(sample_date) desc limit 1"
)
return [dict(row) for row in cursor.fetchall()]
except Exception as e:
logging.error(f"dboperations:fetch_last, {e}")
def fetch_months(self, year):
""" This method is used to return a dictionary of months given in a year of data """
try:
with DBCM(self.database) as cursor:
cursor.execute(
f"select distinct substr(sample_date, 0, 8) from samples where sample_date like '{year}%' order by DATE(sample_date) desc"
)
return [dict(row) for row in cursor.fetchall()]
except Exception as e:
logging.error(f"dboperations:fetch_months, {e}")
def purge_data(self):
"""Method that purges all the data from the DB for when the program fetches all new weather data"""
try:
with DBCM("weather.sqlite") as openDB:
openDB.execute("""DROP TABLE samples;""")
except Exception as e:
logging.error("Error purging data: ", e)
try:
self.initialize_db()
except Exception as e:
logging.error("Error creating table:", e)
def initialize_db(self):
""" This method is used to initialize the db """
try:
with DBCM(self.database) as cursor:
cursor.execute("""create table if not exists samples
(id integer primary key autoincrement not null,
sample_date text not null,
location text not null,
max_temp real not null,
min_temp real not null,
avg_temp real not null);""")
except Exception as e:
logging.error(f"dboperations:initialize_db, {e}")
def is_table_exist(self):
""" This method is used to check if we have a db table named samples """
try:
with DBCM(self.database) as cursor:
cursor.execute(
"select COUNT(*) from sqlite_master where name = 'samples'"
)
if cursor.fetchone()[0] == 1:
pub.sendMessage('change_accessibility', enabled=True)
return True
else:
pub.sendMessage('change_accessibility', enabled=False)
return False
except Exception as e:
logging.error(f"dboperations:is_table_exist, {e}")
def initialize_db(self):
"""Method that initializes the database and creates the table sample"""
try:
with DBCM('weather.sqlite') as openDB:
openDB.execute("""create table if not exists samples
(id integer primary key autoincrement not null,
sample_date text not null,
location text not null,
max_temp real not null,
min_temp real not null,
avg_temp real not null,
UNIQUE(sample_date));""")
except Exception as e:
logging.error("Error creating table:", e)
def validation(self):
"""Method that returns the oldest sample date from the database"""
try:
min_value = ""
with DBCM("weather.sqlite") as openDB:
results = openDB.execute("select min(sample_date) from samples")
for row in results:
min_value = row[0]
return min_value
except Exception as e:
logging.error("Error retrieving oldest date: ", e)
def fetch_data_boxplot(self, year_one, year_two):
"""Function that returns a dictionary of dates and average temperatures between years that the user enters"""
try:
plotting = {}
with DBCM("weather.sqlite") as openDB:
results = openDB.execute(f"select sample_date, avg_temp from samples WHERE sample_date BETWEEN '{year_one}' AND '{year_two}'")
for row in results:
try:
key = row[0]
plotting[key] = row[1]
except Exception as e:
logging.error("Error fetching data for box plot: ", e)
return plotting
except Exception as e:
logging.error("Error fetching samples.", e)
def fetch_data_lineplot(self, year, month):
"""Function that returns a dictionary of dates and average temperatures based on the year and month the years enters"""
try:
plotting = {}
with DBCM("weather.sqlite") as openDB:
results = openDB.execute(f"select sample_date, avg_temp from samples WHERE substr(sample_date, 1, 7) = '{year}'||'-'||'{month}'")
for row in results:
try:
key = row[0]
plotting[key] = row[1]
except Exception as e:
logging.error("Error fetching data for line plot: ", e)
return plotting
except Exception as e:
logging.error("Error fetching samples.", e)
def fetch_data(self, start, end, is_month):
""" This function fetchs the range of data from the db as requested by the user. """
try:
with DBCM(self.database) as cursor:
if is_month:
start_date = f"{str(start)}-{str(end)}-01"
end_date = f"{str(start)}-{str(end)}-31"
else:
start_date = start
end_date = str(end)
cursor.execute(
f"select sample_date, avg_temp from samples where sample_date between '{start_date}' and '{end_date}' order by DATE(sample_date) asc"
)
return [dict(row) for row in cursor.fetchall()]
except Exception as e:
logging.error(f"dboperations:fetch_data, {e}")
def update_data(self):
"""Method that returns the latest sample date from the database"""
try:
max_value = ""
with DBCM("weather.sqlite") as openDB:
results = openDB.execute("select max(sample_date) from samples")
for row in results:
try:
max_value = row[0]
except Exception as e:
logging.error("Data failed to update.", e)
return max_value
except Exception as e:
logging.error("Error updating data: ", e)