-
Notifications
You must be signed in to change notification settings - Fork 1
/
storage.py
142 lines (123 loc) · 5.35 KB
/
storage.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
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
#!/usr/bin/env python3
import os
import sqlite3
db = None
def init(download_path):
global db
db_file = download_path + "/data.sqlite3"
if not os.path.exists(db_file):
db = sqlite3.connect(db_file)
db.execute("create table address(" +
"id integer primary key autoincrement, " +
"street_1 varchar(255) not null, " +
"street_2 varchar(255) not null, " +
"city varchar(255) not null, " +
"state varchar(255) not null, " +
"zipcode varchar(255) not null, " +
"unique(street_1, street_2, city, state, zipcode) " +
")")
db.execute("create table account (" +
"id integer primary key autoincrement, " +
"provider varchar(255) not null, " +
"account_number varchar(255) not null, " +
"service_address_id integer references address(id), " +
"unique(provider, account_number) " +
")")
db.execute("create table usage (" +
"id integer primary key autoincrement, " +
"account_id integer references account(id), " +
"reading real not null, " +
"uom varchar(255) not null, " +
"date datetime not null, " +
"meter_number varchar(255), " +
"service_type varchar(255), " +
"unique(account_id, uom, date) " +
")")
db.execute("create table bill (" +
"id integer primary key autoincrement, " +
"account_id integer references account(id), " +
"amount real not null, " +
"balance real not null, " +
"date datetime not null, " +
"due_date datetime not null, " +
"pdf_url text, " +
"important_information text, " +
"unique(account_id, date)" +
")")
db.commit()
else:
db = sqlite3.connect(db_file)
def insert_or_find_record(insert_sql, insert_tuple, select_sql, select_tuple):
cur = db.execute(insert_sql, insert_tuple)
db.commit()
# cur.lastrowid gives very odd results with insert or ignore
result = db.execute(select_sql, select_tuple)
for row in result:
return row[0]
def insert_address(address):
address_tuple = (
address.street_1,
# this is not null because then otherwise it'll allow multiple
# otherwise identicle rows with a null value here
address.street_2 or '',
address.city,
address.state,
address.zipcode
)
insert_sql = ("insert or ignore into address (street_1, street_2, city, state, zipcode) " +
"values (? , ? , ? , ? , ?)");
select_sql = ("select id from address where " +
" street_1 = ? and street_2 = ? and " +
" city = ? and state = ? and zipcode = ?")
return insert_or_find_record(insert_sql, address_tuple, select_sql, address_tuple)
def insert_account(account):
address_id = insert_address(account.service_address)
insert_sql = ("insert or ignore into account (provider, account_number, service_address_id) " +
"values (? , ? , ?)")
select_sql = ("select id from account where provider = ? and account_number = ?")
account_tuple = (
account.provider,
account.account_number,
address_id
)
select_tuple = (
account.provider,
account.account_number
)
return insert_or_find_record(insert_sql, account_tuple, select_sql, select_tuple)
def insert_usage(account_dbid, usage):
insert_sql = ("insert or ignore into usage(account_id, reading, uom, date, meter_number, service_type) " +
"values (? , ? , ? , ? , ? , ?)")
select_sql = "select id from usage where account_id = ? and uom = ? and date = ?"
usage_tuple = (
account_dbid,
usage.usage,
usage.uom,
usage.date,
usage.meter_number,
usage.service_type
)
select_tuple = (
account_dbid,
usage.uom,
usage.date
)
return insert_or_find_record(insert_sql, usage_tuple, select_sql, select_tuple)
def insert_bill(account_dbid, bill):
insert_sql = ("insert or ignore into bill(account_id, amount, balance, date, due_date, pdf_url, important_information) " +
"values (? , ? , ? , ? , ? , ? , ?)")
select_sql = "select id from bill where account_id = ? and date = ?"
usage_tuple = (
account_dbid,
bill.amount,
bill.balance,
bill.bill_date,
bill.due_date,
bill.pdf_url,
bill.important_information
)
select_tuple = (
account_dbid,
bill.bill_date
)
return insert_or_find_record(insert_sql, usage_tuple, select_sql, select_tuple)