Wasn’t going to post this bit of work. But figured why not. A potentially informative bit of coding. And, a free post so to speak. Plus, this blog really is primarily just for my own note taking.
Upon my release from hospital following my aortic valve replacement, I was less than interested in updating the dashboard database. My partner kept track of the morning temperatures and rainfall during my absence and for the first few weeks following my return home. On paper, not in my daily note file(s). I did eventually update the daily note files using their notes. And, started tracking temperatures and rainfall myself (in the monthly files). But, up until April 14th I had not updated the dashboard database tables since December 17th. On April 14th I decided it was time to do so. So I went through the daily notes and built a CSV file containing all the data since December 17 that needed to be added to the database.
Then I started writing a new module to use that CSV file to update the database. Didn’t get the script working on the 14th. But did manage to do so on April 15th. And managed after a fix or two in the CSV file and numerous bug fixes to get the database up-to-date. Well as of mid day on the 15th. Didn’t have the last temperature, if higher, for the day. Will try to get back to routine daily updates of the database.
But if I do not do so, I now have a script to allow me to process multiple days of data at one time to get things up-to-date.
CSV File
Here’s the first few lines, including header, in the CSV file for the time I was in hospital.
w_dt,time,weather,temp,humidity,rainfall
2025.12.17,08:00,,,,9
2025.12.18,08:00,light rain,7,81,
2025.12.19,08:04,shower,2,81,18
2025.12.20,08:00,light rain,5,85,1
2025.12.21,08:00,light rain,6,91,8
2025.12.22,08:00,cloudy,4,92,5
2025.12.23,08:00,cloudy,3,87,4
2025.12.24,08:00,rain,4,87,1
2025.12.25,08:00,light rain,2,91,3
2025.12.26, 08:21,rain,4,99,17
2025.12.27,08:05,cloudy,0,87,3
2025.12.28,08:00,cloudy,0,78,
2025.12.29,08:00,clear,1,86,
2025.12.30,08:00,foggy,0,89,
2025.12.31,08:00,clear,1,93,
2026.01.01,08:05,foggy,1,91,
2026.01.02,08:39,overcast,4,96,
2026.01.03,08:00,rain,7,99,8
Update Script
And here’s the code and some developmental/debug terminal output.
# catch_up.py: utility module to catch up on missing weather/rainfaill data
# and write it to the database. Data in CSV file. Contains both weather
# and rainfall data on multiple rows per date
# ver: 0.1.0, 2026.04.14, rek, init version
# Haven't been keeping database up-to-date since my surgery in Dec 2025.
# Decided to use script to parse CSV file to do so.
import argparse, calendar, csv, time
from datetime import datetime
from pathlib import Path
import pandas as pd
if __name__ == "__main__":
from weather_db import Weather_db
def get_rf_parser():
"""Create and return cmd line parser
"""
# determine default date for optional parameter
def_dt = time.strftime("%Y.%m.%d", time.gmtime())
# instantiate and set up command paramter parser
parser = argparse.ArgumentParser()
# if only name passed, location will default to data directory
parser.add_argument("-f", "--file", help="Supply name of or path to CSV file to be processed")
parser.add_argument("-d", "--date", help="Supply optional start date, yyyy.mm.dd",
default="")
return(parser)
if __name__ == "__main__":
do_dev = False
nbr_rw = 88
# instantiate database class
cwd = Path(__file__).cwd()
fl_pth = cwd/"../data"
fl_nm = "weather.db"
db_pth = fl_pth/fl_nm
wdb = Weather_db(db_pth)
rf_dt, rf_tm, rf_rf = "", "", 0.0
# get file name/path
rf_parse = get_rf_parser()
rf_args = rf_parse.parse_args()
if do_dev:
print(f"args: {rf_args}")
rf_fnm = rf_args.file
rf_wdt = rf_args.date
if not (("\\" in rf_fnm) or ("/" in rf_fnm)):
# mk pth
csv_pth = fl_pth/rf_fnm
else:
csv_pth = rf_fnm
if do_dev:
print(f"\nrf_fnm: {rf_fnm}, rf_wdt: {rf_wdt}\n\tcsv_pth: {csv_pth}")
new_mn = False
mon_rf = 0
in_tmpr_tbl = f"""INSERT INTO {wdb.tnms["tp_tnm"]} VALUES (NULL, ?, ?, ?, ?, ?, ?);"""
def dbg_prn():
print(f"\n{row}")
print(f"\tdates/mons: {p_dt}, p_mn: {p_mn}, {c_dt}, c_mn: {c_mn}, {r_dt}, r_mn: {r_mn}, new_mn: {r_mn != c_mn} ({new_mn})")
print(f"\tmon rf: {mon_rf}, dn_r15: {dn_r15}, dn_w15: {dn_w15}")
print(f"\twt_lst: {len(wt_lst)}, rf_lst: {len(rf_lst)}")
# open and process CSV file, one month at a time
with open(csv_pth, "r", encoding='utf8') as cu:
p_dt, p_mn, c_dt, c_mn = "", "", "", ""
wt_lst, rf_lst = [], []
if do_dev:
dn_w15, dn_r15 = False, False
csv_rdr = csv.reader(cu, delimiter=',', quotechar='"')
# skip header row
next(csv_rdr)
if do_dev:
rw_cnt = 0
for row in csv_rdr:
# are we going into a new month?
if c_dt == "":
c_dt, c_mn = row[0], row[0][5:7]
mon_rf = wdb.get_mon_todt(c_dt[:7])
r_dt, r_mn = row[0], row[0][5:7]
if r_mn != c_mn:
new_mn = True
if new_mn:
# write data to db
if do_dev:
dbg_prn()
if not do_dev:
if len(wt_lst) > 0:
wdb.qry_nsrt(in_tmpr_tbl, wt_lst, is_many=True)
if len(rf_lst) > 0:
n_rws = wdb.add_rainfall(rf_lst)
else:
print(f"\nEnd of {c_mn}")
print(f"\nwdb.qry_nsrt({in_tmpr_tbl},\n\t\t{wt_lst}")
print(f"\nwdb.add_rainfall(({rf_lst}))")
rf_lst, wt_lst = [], []
# reset variables as approp
mon_rf = 0
p_dt, p_mn = c_dt, c_mn
new_mn = False
# update current date and month
c_dt, c_mn = r_dt, r_mn
bad_rw = False
if do_dev:
rw_cnt += 1
# process row into sql insert statement or two
cu_dttm = f"{row[0]} {row[1]}"
cu_t, cu_l, cu_h, cu_u = 0, 0, 0, 0
is_temp, is_wthr, is_rain = False, False, False
is_wthr = row[2] != ""
if row[3] != "":
is_temp = True
cu_t = float(row[3])
if row[4] != "":
tw_u = int(row[4])
if row[5] != "":
is_rain = True
cu_r = float(row[5])
if is_temp or is_wthr:
d_data = (cu_dttm, cu_t, cu_l, cu_h, row[2], tw_u)
wt_lst.append(d_data)
if len(wt_lst) >= 15:
dn_w15 = True
if not do_dev:
wdb.qry_nsrt(in_tmpr_tbl, wt_lst, is_many=True)
else:
print(f"\ndn_w15 == {dn_w15}")
dbg_prn()
print(f"\nwdb.qry_nsrt({in_tmpr_tbl},\n\t\t{wt_lst}")
wt_lst = []
if is_rain:
# add data to database rainfall table, if appropriate
# zero_ok = allow_zero(rf_dt, rf_tm, mtd)
# print(f"('{cu_dttm}', rf_rf, mtd + rf_rf) -> zero_ok: {zero_ok}")
mon_rf += cu_r
rf_lst.append((cu_dttm, cu_r, mon_rf))
if len(rf_lst) >= 15:
dn_r15 = True
if not do_dev:
n_rws = wdb.add_rainfall(rf_lst)
else:
print(f"\ndn_r15 == {dn_r15}")
dbg_prn()
print(f"\nwdb.add_rainfall(({rf_lst}))")
rf_lst = []
if do_dev and rw_cnt == nbr_rw:
print(f"\nrw_cnt == nbr_rw: dn_r15 and dn_w15: {dn_r15 and dn_w15}")
dbg_prn()
break
# insert any remaining data
if not do_dev:
if len(wt_lst) > 0:
wdb.qry_nsrt(in_tmpr_tbl, wt_lst, is_many=True)
if len(rf_lst) > 0:
n_rws = wdb.add_rainfall(rf_lst)
else:
print(f"\noutside csv reader loop (rows proc: {rw_cnt})")
dbg_prn()
print(f"\nwdb.qry_nsrt({in_tmpr_tbl},\n\t\t{wt_lst}")
print(f"\nwdb.add_rainfall(({rf_lst}))")
During testing this is what I got in the terminal. I.E. do_dev = True.
(dbd-3.13) PS R:\learn\dashboard\utils> python catch_up.py -f ../data/catch_up.csv
args: Namespace(file='../data/catch_up.csv', date='')
rf_fnm: ../data/catch_up.csv, rf_wdt:
csv_pth: ../data/catch_up.csv
['2026.01.01', '08:05', 'foggy', '1', '91', '']
dates/mons: , p_mn: , 2025.12.31, c_mn: 12, 2026.01.01, r_mn: 01, new_mn: True (True)
mon rf: 251.0, dn_r15: False, dn_w15: False
wt_lst: 14, rf_lst: 10
End of 12
wdb.qry_nsrt(INSERT INTO temperature VALUES (NULL, ?, ?, ?, ?, ?, ?);,
[('2025.12.18 08:00', 7.0, 0, 0, 'light rain', 81), ('2025.12.19 08:04', 2.0, 0, 0, 'shower', 81), ('2025.12.20 08:00', 5.0, 0, 0, 'light rain', 85), ('2025.12.21 08:00', 6.0, 0, 0, 'light rain', 91), ('2025.12.22 08:00', 4.0, 0, 0, 'cloudy', 92), ('2025.12.23 08:00', 3.0, 0, 0, 'cloudy', 87), ('2025.12.24 08:00', 4.0, 0, 0, 'rain', 87), ('2025.12.25 08:00', 2.0, 0, 0, 'light rain', 91), ('2025.12.26 08:21', 4.0, 0, 0, 'rain', 99), ('2025.12.27 08:05', 0.0, 0, 0, 'cloudy', 87), ('2025.12.28 08:00', 0.0, 0, 0, 'cloudy', 78), ('2025.12.29 08:00', 1.0, 0, 0, 'clear', 86), ('2025.12.30 08:00', 0.0, 0, 0, 'foggy', 89), ('2025.12.31 08:00', 1.0, 0, 0, 'clear', 93)]
wdb.add_rainfall(([('2025.12.17 08:00', 9.0, 191.0), ('2025.12.19 08:04', 18.0, 209.0), ('2025.12.20 08:00', 1.0, 210.0), ('2025.12.21 08:00', 8.0, 218.0), ('2025.12.22 08:00', 5.0, 223.0), ('2025.12.23 08:00', 4.0, 227.0), ('2025.12.24 08:00', 1.0, 228.0), ('2025.12.25 08:00', 3.0, 231.0), ('2025.12.26 08:21', 17.0, 248.0), ('2025.12.27 08:05', 3.0, 251.0)]))
dn_w15 == True
['2026.01.10', '05:40', 'mostly cloudy', '5', '87', '']
dates/mons: 2025.12.31, p_mn: 12, 2026.01.10, c_mn: 01, 2026.01.10, r_mn: 01, new_mn: False (False)
mon rf: 75.0, dn_r15: False, dn_w15: True
wt_lst: 15, rf_lst: 7
wdb.qry_nsrt(INSERT INTO temperature VALUES (NULL, ?, ?, ?, ?, ?, ?);,
[('2026.01.01 08:05', 1.0, 0, 0, 'foggy', 91), ('2026.01.02 08:39', 4.0, 0, 0, 'overcast', 96), ('2026.01.03 08:00', 7.0, 0, 0, 'rain', 99), ('2026.01.04 08:00', 7.0, 0, 0, 'mostly cloudy', 99), ('2026.01.05 06:50', 4.0, 0, 0, 'mainly clear', 93), ('2026.01.05 14:05', 6.0, 0, 0, '', 80), ('2026.01.06 06:49', 5.0, 0, 0, 'light rain', 88), ('2026.01.06 18:53', 6.0, 0, 0, '', 96), ('2026.01.07 06:50', 3.0, 0, 0, 'mostly cloudy', 91), ('2026.01.07 16:05', 5.0, 0, 0, '', 92), ('2026.01.08 06:15', 3.0, 0, 0, 'partly cloudy', 89), ('2026.01.08 14:37', 5.0, 0, 0, '', 87), ('2026.01.09 07:33', 5.0, 0, 0, 'cloudy', 96), ('2026.01.09 10:41', 6.0, 0, 0, '', 99), ('2026.01.10 05:40', 5.0, 0, 0, 'mostly cloudy', 87)]
dn_w15 == True
['2026.01.18', '06:27', 'clear', '1', '89', '']
dates/mons: 2025.12.31, p_mn: 12, 2026.01.18, c_mn: 01, 2026.01.18, r_mn: 01, new_mn: False (False)
mon rf: 180.0, dn_r15: False, dn_w15: True
wt_lst: 15, rf_lst: 11
wdb.qry_nsrt(INSERT INTO temperature VALUES (NULL, ?, ?, ?, ?, ?, ?);,
[('2026.01.10 12:47', 7.0, 0, 0, '', 85), ('2026.01.11 06:57', 6.0, 0, 0, 'mostly cloudy', 99), ('2026.01.12 06:24', 9.0, 0, 0, 'light rain', 99), ('2026.01.12 16:05', 10.0, 0, 0, '', 99), ('2026.01.13 06:36', 9.0, 0, 0, 'cloudy', 99), ('2026.01.13 14:35', 12.0, 0, 0, '', 99), ('2026.01.14 06:49', 9.0, 0, 0, 'cloudy', 99), ('2026.01.14 15:18', 10.0, 0, 0, '', 99), ('2026.01.15 06:49', 8.0, 0, 0, 'mostly cloudy', 99), ('2026.01.15 12:23', 9.0, 0, 0, '', 93), ('2026.01.16 06:19', 2.0, 0, 0, 'clear', 90), ('2026.01.16 14:58', 9.0, 0, 0, '', 88), ('2026.01.17 06:36', 2.0, 0, 0, 'clear', 89), ('2026.01.17 14:45', 9.0, 0, 0, '', 80), ('2026.01.18 06:27', 1.0, 0, 0, 'clear', 89)]
dn_w15 == True
['2026.01.25', '12:56', '', '3', '78', '']
dates/mons: 2025.12.31, p_mn: 12, 2026.01.25, c_mn: 01, 2026.01.25, r_mn: 01, new_mn: False (False)
mon rf: 180.0, dn_r15: False, dn_w15: True
wt_lst: 15, rf_lst: 11
wdb.qry_nsrt(INSERT INTO temperature VALUES (NULL, ?, ?, ?, ?, ?, ?);,
[('2026.01.18 14:51', 9.0, 0, 0, '', 85), ('2026.01.19 06:32', 1.0, 0, 0, 'clear', 89), ('2026.01.19 14:24', 6.0, 0, 0, '', 91), ('2026.01.20 06:54', 0.0, 0, 0, 'partly cloudy', 88), ('2026.01.20 15:39', 9.0, 0, 0, '', 82), ('2026.01.21 06:15', 0.0, 0, 0, 'clear', 87), ('2026.01.21 13:42', 6.0, 0, 0, '', 85), ('2026.01.22 06:55', 0.0, 0, 0, 'mostly cloudy', 87), ('2026.01.22 13:40', 5.0, 0, 0, '', 81), ('2026.01.23 05:47', -1.0, 0, 0, 'clear', 85), ('2026.01.23 14:41', 6.0, 0, 0, '', 60), ('2026.01.24 06:42', -2.0, 0, 0, 'clear', 78), ('2026.01.24 15:08', 5.0, 0, 0, '', 70), ('2026.01.25 06:24', -2.0, 0, 0, 'clear', 81), ('2026.01.25 12:56', 3.0, 0, 0, '', 78)]
dn_r15 == True
['2026.01.31', '08:00', '', '', '', '30']
dates/mons: 2025.12.31, p_mn: 12, 2026.01.31, c_mn: 01, 2026.01.31, r_mn: 01, new_mn: False (False)
mon rf: 247.0, dn_r15: True, dn_w15: True
wt_lst: 11, rf_lst: 15
wdb.add_rainfall(([('2026.01.03 08:00', 8.0, 8.0), ('2026.01.04 08:00', 29.0, 37.0), ('2026.01.05 08:00', 8.0, 45.0), ('2026.01.06 08:00', 5.0, 50.0), ('2026.01.07 08:00', 12.0, 62.0), ('2026.01.08 08:00', 10.0, 72.0), ('2026.01.09 08:00', 3.0, 75.0), ('2026.01.11 08:00', 20.0, 95.0), ('2026.01.12 08:57', 51.0, 146.0), ('2026.01.13 08:00', 30.0, 176.0), ('2026.01.14 08:00', 4.0, 180.0), ('2026.01.28 08:00', 5.0, 185.0), ('2026.01.29 08:00', 18.5, 203.5), ('2026.01.30 08:00', 13.5, 217.0), ('2026.01.31 08:00', 30.0, 247.0)]))
['2026.02.01', '06:20', 'light rain', '8', '95', '']
dates/mons: 2025.12.31, p_mn: 12, 2026.01.31, c_mn: 01, 2026.02.01, r_mn: 02, new_mn: True (True)
mon rf: 253.5, dn_r15: True, dn_w15: True
wt_lst: 12, rf_lst: 1
End of 01
wdb.qry_nsrt(INSERT INTO temperature VALUES (NULL, ?, ?, ?, ?, ?, ?);,
[('2026.01.26 06:24', -1.0, 0, 0, 'mostly cloudy', 82), ('2026.01.26 15:33', 5.0, 0, 0, '', 71), ('2026.01.27 05:57', 4.0, 0, 0, 'mostly cloudy', 70), ('2026.01.27 13:57', 10.0, 0, 0, '', 60), ('2026.01.28 06:11', 6.0, 0, 0, 'mostly cloudy', 86), ('2026.01.28 10:23', 7.0, 0, 0, '', 91), ('2026.01.29 06:22', 7.0, 0, 0, 'cloudy', 96), ('2026.01.29 11:26', 9.0, 0, 0, '', 99), ('2026.01.30 05:26', 9.0, 0, 0, 'light rain', 96), ('2026.01.30 12:56', 8.0, 0, 0, '', 95), ('2026.01.31 06:16', 8.0, 0, 0, 'cloudy', 100), ('2026.01.31 11:54', 9.0, 0, 0, '', 100)]
wdb.add_rainfall(([('2026.01.31 24:00', 6.5, 253.5)]))
rw_cnt == nbr_rw: dn_r15 and dn_w15: True
['2026.02.01', '08:00', '', '', '', '10.5']
dates/mons: 2026.01.31, p_mn: 01, 2026.02.01, c_mn: 02, 2026.02.01, r_mn: 02, new_mn: False (False)
mon rf: 10.5, dn_r15: True, dn_w15: True
wt_lst: 1, rf_lst: 1
outside csv reader loop (rows proc: 88)
['2026.02.01', '08:00', '', '', '', '10.5']
dates/mons: 2026.01.31, p_mn: 01, 2026.02.01, c_mn: 02, 2026.02.01, r_mn: 02, new_mn: False (False)
mon rf: 10.5, dn_r15: True, dn_w15: True
wt_lst: 1, rf_lst: 1
wdb.qry_nsrt(INSERT INTO temperature VALUES (NULL, ?, ?, ?, ?, ?, ?);,
[('2026.02.01 06:20', 8.0, 0, 0, 'light rain', 95)]
wdb.add_rainfall(([('2026.02.01 08:00', 10.5, 10.5)]))
Confirm Update Worked
And, as confirmation it worked, here’s the output from another of my developmental/debug scripts.
(dbd-3.13) PS R:\learn\dashboard\utils> python chk_db_rek.py -d 2026.04.15
[(5791,)] rows currently in temperature
SELECT * FROM temperature WHERE substr(datetime, 1, 7)='2026.04'
row_id temperature dmin dmax condition humidity
datetime
2026.04.01 06:33 5769 6.0 0.0 0.0 cloudy 79.0
2026.04.02 05:41 5770 6.0 0.0 0.0 mostly cloudy 90.0
2026.04.02 15:23 5771 11.0 0.0 0.0 76.0
2026.04.03 06:44 5772 7.0 0.0 0.0 cloudy 87.0
2026.04.03 13:52 5773 10.0 0.0 0.0 79.0
2026.04.04 06:52 5774 7.0 0.0 0.0 mostly cloudy 84.0
2026.04.04 14:34 5775 17.0 0.0 0.0 57.0
2026.04.05 06:41 5776 6.0 0.0 0.0 mainly clear 75.0
2026.04.05 14:52 5777 19.0 0.0 0.0 47.0
2026.04.06 06:50 5778 8.0 0.0 0.0 partly cloudy 73.0
2026.04.06 14:21 5779 19.0 0.0 0.0 48.0
2026.04.07 07:07 5780 7.0 0.0 0.0 mostly cloudy 59.0
2026.04.07 15:50 5781 15.0 0.0 0.0 44.0
2026.04.08 07:13 5782 5.0 0.0 0.0 clear 72.0
2026.04.08 15:19 5783 16.0 0.0 0.0 44.0
2026.04.09 05:28 5784 6.0 0.0 0.0 clear 70.0
2026.04.09 14:01 5785 18.0 0.0 0.0 47.0
2026.04.10 06:36 5786 8.0 0.0 0.0 mainly clear 65.0
2026.04.10 16:07 5787 19.0 0.0 0.0 46.0
2026.04.11 07:13 5788 10.0 0.0 0.0 mostly cloudy 70.0
2026.04.11 13:53 5789 14.0 0.0 0.0 65.0
2026.04.12 06:19 5790 9.0 0.0 0.0 mostly cloudy 84.0
2026.04.12 16:28 5791 15.0 0.0 0.0 71.0
2026.04.13 06:48 5792 9.0 0.0 0.0 light rain 86.0
2026.04.13 14:24 5793 11.0 0.0 0.0 88.0
2026.04.14 06:59 5794 8.0 0.0 0.0 light rain 86.0
2026.04.15 06:33 5795 3.0 0.0 0.0 partly cloudy 84.0
SELECT * FROM rainfall WHERE substr(datetime, 1, 7)='2026.04'
row_id datetime daily monthly
0 1888 2026.04.01 08:00 4.5 4.5
1 1889 2026.04.02 08:00 28.5 33.0
2 1890 2026.04.03 08:00 2.0 35.0
3 1891 2026.04.13 08:00 8.5 43.5
4 1892 2026.04.14 08:00 13.0 56.5
5 1893 2026.04.15 08:00 21.0 77.5
SELECT datetime, condition
FROM temperature
WHERE SUBSTR(datetime, 6, 5)='04.15' AND condition!='' AND condition IS NOT Null
AND SUBSTR(datetime, 1, 4)!='2026';
datetime condition
0 2015.04.15 07:34 rain
1 2018.04.15 08:17 cloudy
2 2019.04.15 08:28 mostly clear
3 2022.04.15 06:00 cloudy
4 2023.04.15 06:00 mostly cloudy
5 2024.04.15 06:00 mostly cloudy
6 2025.04.15 06:00 mostly cloudy
I also ran the dashboard and played around looking at the weather data for that 4 months or so. Seemed to be okay. Won’t bother with any images of the dashboard.
Done
Well, that’s pretty much it for this project. Don’t know where I will go next.
I am still not quite ready to return to working on that transformer model. Not sure why. Perhaps just too much AI chatting in the world these days.
Hopefully I will have decided in time for the next tentative post publish date.