-
Notifications
You must be signed in to change notification settings - Fork 0
/
db_setup.py
216 lines (190 loc) · 5.91 KB
/
db_setup.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
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
import sqlite3
def db_connect(db_name):
con = sqlite3.connect(db_name)
return con
class SaleSchema:
sales_table_cols = '''
sale_id TEXT NOT NULL PRIMARY KEY,
sale_date DATE NOT NULL,
store_id INTEGER NOT NULL,
item_id INTEGER NOT NULL,
bottles_sold INTEGER NOT NULL,
sale_value INTEGER,
sale_vol_ml INTEGER NOT NULL,
FOREIGN KEY (store_id) REFERENCES stores(store_number),
FOREIGN KEY (item_id) REFERENCES items(item_number)
'''
table_statement = 'CREATE TABLE IF NOT EXISTS sales'
def create_sales_table(self):
full_statement = self.table_statement + '(' + self.sales_table_cols + ')'
return full_statement
class CategorieSchema:
categories_table_cols = '''
category_id INTEGER NOT NULL PRIMARY KEY,
category_name TEXT'''
table_statement = 'CREATE TABLE IF NOT EXISTS categories'
def create_categories_table(self):
full_statement = self.table_statement + '(' + self.categories_table_cols + ')'
return full_statement
class VendorSchema:
vendors_table_cols = '''
vendor_id INTEGER NOT NULL PRIMARY KEY,
vendor_name TEXT'''
table_statement = 'CREATE TABLE IF NOT EXISTS vendors'
def create_vendors_table(self):
full_statement = self.table_statement + '(' + self.vendors_table_cols + ');'
return full_statement
class ItemSchema:
items_table_cols = '''
item_id INTEGER NOT NULL PRIMARY KEY,
category_id INTEGER,
vendor_id INTEGER,
item_description TEXT NOT NULL,
pack_qty INTEGER NOT NULL,
bottle_volume_ml INTEGER NOT NULL,
state_wholesale INTEGER NOT NULL,
state_retail INTEGER NOT NULL,
FOREIGN KEY (category_id) REFERENCES categories(category_number),
FOREIGN KEY (vendor_id) REFERENCES vendors(vendor_number)
'''
table_statement = 'CREATE TABLE IF NOT EXISTS items'
def create_items_table(self):
full_statement = self.table_statement + '(' + self.items_table_cols + ');'
return full_statement
class StoreSchema:
store_table_cols = '''
store_id INTEGER NOT NULL PRIMARY KEY,
store_name TEXT NOT NULL,
address TEXT,
city TEXT,
zip_code INTEGER,
store_lat REAL,
store_long REAL,
county_id INTEGER,
FOREIGN KEY (county_id) REFERENCES counties(county_id)'''
table_statement = 'CREATE TABLE IF NOT EXISTS stores'
def create_stores_table(self):
full_statement = self.table_statement + '(' + self.store_table_cols + ');'
return full_statement
class CountySchema:
counties = [
(0, None),
(1, 'Adair'),
(2, 'Adams'),
(3, 'Allamakee'),
(4, 'Appanoose'),
(5, 'Audubon'),
(6, 'Benton'),
(7, 'Black Hawk'),
(8, 'Boone'),
(9, 'Bremer'),
(10, 'Buchanan'),
(11, 'Buena Vista'),
(12, 'Butler'),
(13, 'Calhoun'),
(14, 'Carroll'),
(15, 'Cass'),
(16, 'Cedar'),
(17, 'Cerro Gordo'),
(18, 'Cherokee'),
(19, 'Chickasaw'),
(20, 'Clarke'),
(21, 'Clay'),
(22, 'Clayton'),
(23, 'Clinton'),
(24, 'Crawford'),
(25, 'Dallas'),
(26, 'Davis'),
(27, 'Decatur'),
(28, 'Delaware'),
(29, 'Des Moines'),
(30, 'Dickinson'),
(31, 'Dubuque'),
(32, 'Emmet'),
(33, 'Fayette'),
(34, 'Floyd'),
(35, 'Franklin'),
(36, 'Fremont'),
(37, 'Greene'),
(38, 'Grundy'),
(39, 'Guthrie'),
(40, 'Hamilton'),
(41, 'Hancock'),
(42, 'Hardin'),
(43, 'Harrison'),
(44, 'Henry'),
(45, 'Howard'),
(46, 'Humboldt'),
(47, 'Ida'),
(48, 'Iowa'),
(49, 'Jackson'),
(50, 'Jasper'),
(51, 'Jefferson'),
(52, 'Johnson'),
(53, 'Jones'),
(54, 'Keokuk'),
(55, 'Kossuth'),
(56, 'Lee'),
(57, 'Linn'),
(58, 'Louisa'),
(59, 'Lucas'),
(60, 'Lyon'),
(61, 'Madison'),
(62, 'Mahaska'),
(63, 'Marion'),
(64, 'Marshall'),
(65, 'Mills'),
(66, 'Mitchell'),
(67, 'Monona'),
(68, 'Monroe'),
(69, 'Montgomery'),
(70, 'Muscatine'),
(71, 'O\'Brien'),
(72, 'Osceola'),
(73, 'Page'),
(74, 'Palo Alto'),
(75, 'Plymouth'),
(76, 'Pocahontas'),
(77, 'Polk'),
(78, 'Pottawattamie'),
(79, 'Poweshiek'),
(80, 'Ringgold'),
(81, 'Sac'),
(82, 'Scott'),
(83, 'Shelby'),
(84, 'Sioux'),
(85, 'Story'),
(86, 'Tama'),
(87, 'Taylor'),
(88, 'Union'),
(89, 'Van Buren'),
(90, 'Wapello'),
(91, 'Warren'),
(92, 'Washington'),
(93, 'Wayne'),
(94, 'Webster'),
(95, 'Winnebago'),
(96, 'Winneshiek'),
(97, 'Woodbury'),
(98, 'Worth'),
(99, 'Wright')
]
county_table_cols = '''
county_id INTEGER NOT NULL PRIMARY KEY,
county_name TEXT UNIQUE'''
table_statement = 'CREATE TABLE IF NOT EXISTS counties'
def create_counties_table(self):
full_statement = self.table_statement + '(' + self.county_table_cols + ')'
return full_statement
def insert_counties(self, database):
try:
insert_statement = '''INSERT INTO counties(county_id, county_name)
VALUES(?,?)'''
database.executemany(insert_statement, self.counties)
except sqlite3.IntegrityError:
print('Counties already seeded')
return
if __name__ == "__main__":
New_ILS_DB = db_connect('sales.db')
store_schema = StoreSchema()
New_ILS_DB.execute(store_schema.create_stores_table())