-
Notifications
You must be signed in to change notification settings - Fork 0
/
monthly_readings.py
69 lines (56 loc) · 1.96 KB
/
monthly_readings.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
import sqlite3
from lib import utils
import logbook
def main():
logger = logbook.Logger("readings")
database = "well.sqlite"
db = sqlite3.connect(database)
db.row_factory = sqlite3.Row
cur = db.cursor()
reading_date: str = utils.prompt_for_current_date("Reading date")
exec_str = "INSERT INTO reading_date (date) VALUES (?)"
params = (reading_date,)
logger.trace(f"{exec_str}{params}")
cur.execute(exec_str, params)
last_inserted_row_id = cur.lastrowid
logger.trace(f"attempting to backup the database file now")
backup_file_name = utils.backup_file(database)
logger.trace(f"database backed up to: {backup_file_name}")
exec_str = "SELECT * FROM account"
cur.execute(exec_str)
rows = cur.fetchall()
for r in rows:
if r["active"] == "no":
logger.trace(f"Account {r['acct_id']} currently INACTIVE")
continue
# fetch last month's reading as a sanity check
exec_str = f"""
SELECT reading
FROM reading
WHERE account_id = (?)
ORDER BY reading_id
DESC
"""
params = (r["acct_id"],)
cur.execute(exec_str, params)
last_reading = cur.fetchone()
print(f"Last month's reading: {last_reading['reading']}")
# grab current reading and then insert it into the DB
reading = input(f"{r['acct_id']} - {r['address']}: ")
# this should allow empty input .... in case of inactive account
if not reading:
continue
exec_str = f"""
INSERT INTO reading (reading_id, account_id, reading)
VALUES (?, ?, ?)
"""
params = (last_inserted_row_id, r["acct_id"], reading)
logger.trace(params)
cur.execute(exec_str, params)
# save, then close the cursor and db
db.commit()
cur.close()
db.close()
if __name__ == "__main__":
utils.init_logging("logs/monthly_readings.log")
main()