· 6 years ago · Jun 09, 2019, 02:16 PM
1import datetime
2import os
3import re
4
5import openpyxl
6import sqlite3
7
8database_filepath = os.path.normpath('C:/Users/henry/Dropbox/Henry/Programming/openpyxl test/db')
9
10## Spreadsheet cell map
11cells = {
12 'name': 'B1',
13 'age': 'B2',
14 'date_of_birth': 'B3',
15 'postcode': 'B4',
16 'report_id': 'B5',
17 'job': 'E1',
18 'toes': 'E2',
19 'attempts': 'E4',
20}
21
22body_rows = {
23 'title': 6,
24 'data_start': 7,
25}
26
27body_columns = {
28 'attempt_id': 'A',
29 'distance': 'B',
30 'height': 'C',
31 'fear': 'D',
32 'broken_limbs': 'E',
33 'score': 'F',
34}
35
36class Property():
37
38 def __init__(self, **kwargs):
39 if 'name' in kwargs:
40 self.name = kwargs['name']
41 else:
42 raise TypeError('Property requires name')
43
44 if 'sql_type' in kwargs:
45 self.sql_type = kwargs['sql_type']
46 else:
47 raise TypeError('Property requires sql_type')
48
49 if 'func' in kwargs:
50 self.func = kwargs['func']
51 else:
52 self.func = None
53
54 self.val = None
55
56
57def func_report_id(self, report):
58 try:
59 report_id = str(report.load_cell('report_id'))
60 except:
61 return
62
63 self.val = report_id.strip()
64
65def func_name(self, report):
66 try:
67 name = str(report.load_cell('name'))
68 except:
69 return
70
71 self.val = name.strip()
72
73def func_age(self, report):
74 try:
75 age = int(report.load_cell('age'))
76 except:
77 return
78
79 self.val = age
80
81def func_date_of_birth(self, report):
82 try:
83 date_of_birth = report.load_cell('date_of_birth').date()
84 except:
85 return
86
87 self.val = date_of_birth
88
89def func_shot_count(self, report):
90 try:
91 shot_count = int(report.load_cell('attempts'))
92 except:
93 return
94
95 self.val = shot_count
96
97report_properties = [
98 Property(
99 name='report_id',
100 sql_type='TEXT',
101 func=func_report_id,
102 ),
103 Property(
104 name='name',
105 sql_type='TEXT',
106 func=func_name,
107 ),
108 Property(
109 name='age',
110 sql_type = 'INTEGER',
111 func=func_age,
112 ),
113 Property(
114 name='date_of_birth',
115 sql_type = 'TEXT',
116 func=func_date_of_birth,
117 ),
118 Property(
119 name='shot_count',
120 sql_type = 'INTEGER',
121 func=func_shot_count,
122 )
123]
124
125
126def func_shot_id(self, shot):
127 try:
128 shot_id = int(shot.load_cell('shot_id'))
129 except:
130 return
131
132 self.val = shot_id
133
134def func_distance(self, shot):
135 try:
136 distance = int(shot.load_cell('distance'))
137 except:
138 return
139
140 self.val = distance
141
142def func_height(self, shot):
143 try:
144 height = int(shot.load_cell('height'))
145 except:
146 return
147
148 self.val = height
149
150def func_fear(self, shot):
151 try:
152 fear = int(shot.load_cell('fear'))
153 except:
154 return
155
156 self.val = fear
157
158def func_broken_limbs(self, shot):
159 try:
160 broken_limbs = int(shot.load_cell('broken_limbs'))
161 except:
162 return
163
164 self.val = broken_limbs
165
166def func_score(self, shot):
167 try:
168 score = int(shot.load_cell('score'))
169 except:
170 return
171
172 self.val = score
173
174shot_properties = [
175 Property(
176 name='report_id',
177 sql_type='TEXT',
178 ),
179 Property(
180 name='shot_id',
181 sql_type='INTEGER',
182 func=func_shot_id,
183 ),
184 Property(
185 name='distance',
186 sql_type='INTEGER',
187 func=func_distance,
188 ),
189 Property(
190 name='height',
191 sql_type = 'INTEGER',
192 func=func_height,
193 ),
194 Property(
195 name='fear',
196 sql_type = 'INTEGER',
197 func=func_fear,
198 ),
199 Property(
200 name='broken_limbs',
201 sql_type = 'INTEGER',
202 func=func_broken_limbs,
203 ),
204 Property(
205 name='score',
206 sql_type = 'INTEGER',
207 func=func_score,
208 )
209]
210
211
212def set_up_db():
213 conn = sqlite3.connect(database_filepath)
214 cur = conn.cursor()
215
216 create_table_reports = 'CREATE TABLE IF NOT EXISTS reports(' + ', '.join([prop.name + ' ' + prop.sql_type for prop in report_properties]) + ');'
217
218 create_table_results = 'CREATE TABLE IF NOT EXISTS results(' + ', '.join([prop.name + ' ' + prop.sql_type for prop in shot_properties]) + ');'
219
220 cur.execute(create_table_reports)
221 cur.execute(create_table_results)
222
223
224class Shot():
225
226 def __init__(self, sheet, row, properties):
227 self.sh = sheet
228 self.row = str(row)
229 self.properties = properties
230
231
232 self.load_from_spreadsheet()
233
234 def load_from_spreadsheet(self):
235 for prop in self.properties:
236 if prop.func is not None:
237 prop.func(prop, self)
238
239 def load_cell(self, col_id):
240 return self.sh[body_columns[col_id] + self.row].value
241
242class Report():
243
244 def __init__(self, **kwargs):
245 if 'file' in kwargs:
246 self.filepath = kwargs['file']
247 else:
248 raise TypeError('Report requires file')
249
250 if 'properties' in kwargs:
251 self.properties = kwargs['properties']
252 else:
253 raise TypeError('Report requires properties')
254
255 if 'shot_properties' in kwargs:
256 self.shot_properties = kwargs['shot_properties']
257 else:
258 raise TypeError('Report requires shot_properties')
259
260
261 self.load_spreadsheet()
262 self.load_from_spreadsheet()
263
264 def load_spreadsheet(self):
265 wb = openpyxl.load_workbook(self.filepath, data_only=True)
266 self.sh = wb['Sheet1']
267
268 def load_from_spreadsheet(self):
269 for property in self.properties:
270 property.func(property, self)
271
272 self.load_row_data()
273
274 def load_cell(self, cell_id):
275 return self.sh[cells[cell_id]].value
276
277 def save_to_database(self, conn):
278 cur = conn.cursor()
279
280 report_id = next(prop.val for prop in self.properties if prop.name == 'report_id')
281
282 count_existing_reports = 'SELECT COUNT(*) FROM reports WHERE report_id = ?'
283 save_report = 'INSERT INTO reports (' + ', '.join([prop.name for prop in self.properties]) + ') VALUES (' + ', '.join(['?' for prop in self.properties]) + ');'
284
285 cur.execute(count_existing_reports, (report_id,)) # counts number of entries for this report_id
286 if cur.fetchone()[0] == 0: # if there are no entries already
287 cur.execute(save_report, tuple([prop.val for prop in self.properties]))
288
289 for shot in self.shots:
290 shot_id = next(prop.val for prop in shot.properties if prop.name == 'shot_id')
291
292 count_existing_shots = 'SELECT COUNT(*) FROM results WHERE report_id = ? AND shot_id = ?'
293 save_results = 'INSERT INTO results (report_id, attempt_id, distance, height, fear, broken_limbs, score) VALUES (?, ?, ?, ?, ?, ?, ?);'
294 save_results = 'INSERT INTO results (' + ', '.join([prop.name for prop in shot.properties]) + ') VALUES (' + ', '.join(['?' for prop in shot.properties]) + ');'
295
296 cur.execute(count_existing_shots, (report_id, shot_id)) # counts entries with this report_id and attempt_id
297 if cur.fetchone()[0] == 0: # if there are no entries already
298 cur.execute(save_results, tuple([prop.val for prop in shot.properties]))
299
300 conn.commit()
301
302 ## Loading rows
303 def load_row_data(self):
304 shots = []
305 shot_count = next(prop.val for prop in self.properties if prop.name == 'shot_count')
306
307 for i in range(shot_count):
308 row = body_rows['data_start'] + i
309 shots.append(Shot(self.sh, row, self.shot_properties))
310
311 self.shots = shots
312
313
314set_up_db()
315
316data_folder_path = os.path.normpath('C:/Users/henry/Dropbox/Henry/Programming/openpyxl test')
317file_report_ids = ['001', '002', '003', '004',]
318
319try:
320 conn = sqlite3.connect(database_filepath)
321except:
322 raise ConnectionError('Could not connect to SQLite database.')
323
324for file_id in file_report_ids:
325 filepath = os.path.join(data_folder_path, 'report' + file_id + '.xlsx')
326
327 report = Report(file=filepath, properties=report_properties, shot_properties=shot_properties)
328 report.save_to_database(conn)