· 9 years ago · Dec 04, 2016, 07:52 AM
1# Bradley Singer 997990414, Dimitar Vasilev 999307063
2
3import os
4import sys # command line arguement (specify excel file to read at command line)
5import psycopg2 # Operate on sql database
6import csv # needed to read csv files
7import time
8
9CUR_FILE = ''
10MID = 0
11user = os.environ['USER']
12conn = psycopg2.connect("dbname=fakeu user=%s" % user)
13cursor = conn.cursor()
14
15dbDict = {'Course': ('CID', 'TERM', 'SUBJ', 'CRSE', 'SEC', 'UNITSMIN', 'UNITSMAX'),
16 'Student': ('SID','SURNAME', 'PREFNAME', 'EMAIL'),
17 'Enroll': ('CID', 'TERM', 'SEAT', 'SID', 'LEVEL', 'UNITS', 'CLASS', 'MAJOR', 'GRADE', 'GP', 'STATUS'),
18 'Has': ('MID', 'CID', 'TERM', 'INSTRUCTORS', 'TYPE', 'DAYS', 'STARTTIME', 'ENDTIME', 'BUILD', 'ROOM') }
19
20fileDict = {'Course': ("CID", "TERM", "SUBJ", "CRSE", "SEC", "UNITS"),
21 'Meeting': ("INSTRUCTOR(S)", "TYPE", "DAYS", "TIME", "BUILD", "ROOM"),
22 'Student': ("SEAT","SID","SURNAME", "PREFNAME", "LEVEL", "UNITS", "CLASS", "MAJOR", "GRADE", "STATUS", "EMAIL") }
23
24key_loc = {'Course': [0, 1],
25 'Student': [0],
26 'Enroll':[0, 1, 3],
27 'Has': [0, 1, 2]}
28
29letter_to_gp = { 'A+': 4.0,
30 'A': 4.0,
31 'A-': 3.7,
32 'B+': 3.3,
33 'B': 3.0,
34 'B-': 2.7,
35 'C+': 2.3,
36 'C': 2.0,
37 'C-': 1.7,
38 'D+': 1.3,
39 'D': 1.0,
40 'D-': 0.7,
41 'F+': 0.3,
42 'F': 0.0,
43 'F-': 0.0
44 }
45
46course_table_tuples = []
47meeting_table_tuples = []
48student_table_tuples = []
49
50alldata = dict()
51summerCourses = dict()
52
53coursekeys = dict()
54studentkeys = dict()
55enrollkeys = dict()
56haskeys = dict()
57
58keys = {'Course': coursekeys, 'Student':studentkeys, 'Enroll':enrollkeys, 'Has':haskeys}
59
60##### create database tables #####
61def initialize():
62 cursor.execute('''CREATE TABLE IF NOT EXISTS Course (
63 CID VARCHAR(50),
64 TERM VARCHAR(50),
65 SUBJ VARCHAR(50),
66 CRSE INTEGER,
67 SEC SMALLINT,
68 UNITSMIN INTEGER,
69 UNITSMAX INTEGER,
70 PRIMARY KEY (CID, TERM)
71 );
72
73 CREATE TABLE IF NOT EXISTS Student (
74 SID INTEGER PRIMARY KEY,
75 SURNAME VARCHAR(50),
76 PREFNAME VARCHAR(50),
77 EMAIL VARCHAR(50)
78 );
79
80 CREATE TABLE IF NOT EXISTS Enroll (
81 CID VARCHAR(50),
82 TERM VARCHAR(50),
83 SEAT SMALLINT,
84 SID INTEGER,
85 LEVEL VARCHAR(50),
86 UNITS VARCHAR(50),
87 CLASS VARCHAR(50),
88 MAJOR VARCHAR(50),
89 GRADE VARCHAR(10),
90 GP NUMERIC(2,1),
91 STATUS VARCHAR(50),
92 PRIMARY KEY (CID, TERM, SID)
93 );
94
95 CREATE TABLE IF NOT EXISTS Has (
96 MID INTEGER,
97 CID VARCHAR(50),
98 TERM VARCHAR(50),
99 INSTRUCTORS VARCHAR(30),
100 TYPE VARCHAR(50),
101 DAYS VARCHAR(50),
102 STARTTIME VARCHAR(50),
103 ENDTIME VARCHAR(50),
104 BUILD VARCHAR(50),
105 ROOM VARCHAR(5),
106 PRIMARY KEY (MID, CID, TERM)
107 );
108
109 ''')
110 conn.commit()
111
112
113##### add tuples to database #####
114def addValue(table, tup):
115
116 attributes = dbDict[table]
117 query = '''INSERT INTO %s%s VALUES %s''' % (table, str(attributes).replace("'", ''), str(tup))
118
119 # strips the string of attribute single quotes but leaves values single quotes
120 query = query.replace('"', '')
121 cursor.execute(query)
122 conn.commit()
123
124
125##### check if a tuple has not null keys #####
126def checkKeys(table, tup):
127 check = False
128 for i in key_loc[table]:
129 if tup[i] != '':
130 check = True
131 return check
132
133##### check that a tuple doesn't have a duplicate key and its key attributes are not null #####
134def checkKeys(table, tup):
135 check = 0
136 global CUR_FILE
137 tupkeys = ()
138 quarter = CUR_FILE.split('_')[1]
139
140 for i in key_loc[table]:
141 tupkeys += (tup[i],) # get the key attr from our tuple
142 if tup[i] == '': # if one of the key attr is null, return False
143 return check
144 if tupkeys in keys[table]: # make sure we haven't added duplicate keys
145 if table == 'Course':
146 return 1
147 if '3' in quarter and table == 'Course':
148 newkey = ()
149 for i in tupkeys:
150 newkey += ((i + 'a'),)
151 keys[table][newkey] = True
152 check = 2
153 else:
154 check = 0
155 else:
156 keys[table][tupkeys] = True
157 check = 1
158
159 return check
160
161
162##### returns True if tuple not in dictionary #####
163def checkUnique(tup):
164 if tup in alldata:
165 return False
166 else:
167 alldata[tup] = 'true'
168 return True
169
170
171##### create tuples out of global arrays and prep for insertion into database #####
172def create_tuples():
173
174 staredpair = ()
175 conflictingCourse = False
176 # if the tables are not empty
177 if meeting_table_tuples and student_table_tuples and course_table_tuples:
178
179 course = course_table_tuples[0] # Course table should only have 1 value
180 check = checkKeys('Course', course)
181
182 # check if course tuple is unique in database and its keys are not null and its not a conflicting course
183 if checkUnique(str(course)) and check == 1:
184 addValue('Course', course)
185
186 elif check == 2: # if it is a conflicting course, create new key pair
187 print('conflicting course')
188 conflictingCourse = True
189 diffcourse = ()
190 staredpair = ((course[0] + 'a'),(course[1] + 'a')) # cid, term with 'a' appended to each
191 diffcourse += staredpair
192 for x in range(2, len(course)):
193 diffcourse += (course[x],) # subj, crse, sec, units
194 addValue('Course', diffcourse)
195
196 for meeting in meeting_table_tuples:
197
198 has_tup = ()
199 has_tup += (meeting[0],) # MID
200 if conflictingCourse:
201 has_tup += staredpair # cid*, term* from course if conflicting course
202 else:
203 has_tup += (course[0], course[1])
204 for x in range(1,len(meeting)): # everything else from meeting that we missed
205 has_tup += (meeting[x],) # instructor, type, days, starttime, endtime, build, room
206 # check if has tuple is unique in database
207 if checkUnique(str(has_tup)) and checkKeys('Has', has_tup):
208 addValue('Has', has_tup)
209
210
211 for student in student_table_tuples:
212
213 # Last tuple in student array will be empty because that is the line at which we call create_tuple()
214 if student != ('',):
215 student_tup = ()
216 student_tup += (student[1], student[2],student[3],student[10]) # sid, surname, prefname, email
217
218 # check if student tuple is unique in database
219 if checkUnique(str(student_tup)) and checkKeys('Student', student_tup):
220 addValue('Student', student_tup)
221
222 enroll_tup = ()
223 if conflictingCourse:
224 enroll_tup += staredpair # cid*, term* from course if conflicting course
225 else:
226 enroll_tup += (course[0], course[1])
227 enroll_tup += (student[0],student[1]) # seat, sid from student
228 for x in range(4,len(student) - 1): # everything else from student except email
229 enroll_tup += (student[x],) # level, units, class, major, grade, gp, status
230
231 # check if enroll tuple is unique in database
232 if checkUnique(str(enroll_tup)) and checkKeys('Enroll', enroll_tup):
233 addValue('Enroll', enroll_tup)
234
235
236 del course_table_tuples[:]
237 del meeting_table_tuples[:]
238 del student_table_tuples[:]
239
240#### convert time attribute to minutes #####
241def convert_time(time):
242 digit = time.split(' ')[0]
243 day = time.split(' ')[1]
244 hour = int(digit.split(':')[0])
245 minn = int(digit.split(':')[1])
246 if day == 'PM':
247 if hour == 12:
248 minutes = 60*(12) + minn
249 else:
250 minutes = 60*(hour + 12) + minn
251 elif day == 'AM':
252 if hour == 12:
253 minutes = minn
254 else:
255 minutes = 60*(hour) + minn
256
257 return minutes
258
259##### format each attribute of each row #####
260def parseAttr(attr):
261 attr = attr.replace('(', '')
262 attr = attr.replace(')', '')
263 attr = attr.strip('"')
264 attr = attr.strip("'")
265 attr = attr.replace("'", '')
266 return attr
267
268
269##### read in the file #####
270def readCSV(ifilepath):
271
272 # open the file
273 csvfile = open(ifilepath)
274 reader = csv.reader(csvfile)
275
276 # initial conditions
277 studentTableReached = False
278 meetingTableReached = False
279 courseTableReached = False
280 row_count = -1
281 empty_line_count = 0
282 reader = csv.reader(csvfile)
283 global MID
284
285 # read file line by line
286 for row in reader:
287 row_count += 1
288
289 # First line in file is empty, skip
290 if row_count == 0:
291 continue
292
293 insert_row = []
294 newrow = tuple(';'.join(row).strip("'").split(';')) # convert each line into tuple
295
296 # For every 3 empty lines you come across, corresponding tuples should be created
297 if newrow == ('',):
298 courseTableReached = False
299 meetingTableReached = False
300 studentTableReached = False
301 empty_line_count += 1
302 if empty_line_count == 3:
303 create_tuples()
304 empty_line_count = 0
305
306
307
308 # If course table reached, need to create unitsmin and unitsmax attributes for database
309 if courseTableReached == True:
310
311 attr_count = 0
312
313 for attr in newrow:
314
315 attr = parseAttr(attr)
316
317 # units attribute is specified as range, create min and max
318 if '.000' in attr:
319 attr = attr.replace('.000', '')
320 units_min = attr.split('-')[0].replace('"', '')
321 units_max = attr.split('-')[1].replace('"', '')
322 insert_row.append(units_min)
323 insert_row.append(units_max)
324
325 # units attribute is single integer, copy twice
326 elif attr_count == 5:
327 insert_row.append(attr)
328 insert_row.append(attr)
329
330 # attribute is not unit
331 else:
332 insert_row.append(attr)
333
334 attr_count += 1
335
336
337 insert_tup = tuple(insert_row)
338 course_table_tuples.append(insert_tup)
339
340
341 # meeting table has been reached
342 if meetingTableReached == True:
343 MID = MID + 1
344 insert_row.append(" \'" + str(MID) + "\'")
345
346 attr_count = 0
347 for attr in newrow:
348 attr = parseAttr(attr)
349
350 #split time into 2 attributes
351 if '-' in attr:
352 time_interval = attr.split(' - ')
353 for i in range(len(time_interval)):
354 time = convert_time(time_interval[i])
355 insert_row.append(time)
356
357 elif attr_count == 3:
358 insert_row.append(attr)
359 insert_row.append(attr)
360 else:
361 insert_row.append(attr)
362
363 attr_count += 1
364
365
366 insert_tup = tuple(insert_row)
367 meeting_table_tuples.append(insert_tup)
368
369
370
371 # student table has been reached
372 if studentTableReached == True:
373 insert_arr = []
374 attr_count = 0
375 for attr in newrow:
376 attr = parseAttr(attr)
377 if attr_count == 5 and attr == '':
378 attr = '-1'
379 insert_row.append(attr)
380 elif attr_count == 8: # Letter Grade
381 insert_row.append(attr)
382 if attr in letter_to_gp:
383 insert_row.append(letter_to_gp[attr])
384 else:
385 insert_row.append('5.0') # not in gp scale
386 else:
387 insert_row.append(attr)
388
389 insert_tup = tuple(insert_row)
390 attr_count += 1
391 student_table_tuples.append(insert_tup)
392
393 # set flags for eaech row
394 if newrow == fileDict['Course']:
395 courseTableReached = True
396
397 elif newrow == fileDict['Meeting']:
398 meetingTableReached = True
399
400 elif newrow == fileDict['Student']:
401 studentTableReached = True
402
403
404 create_tuples()
405
406##### for debugging purposes #####
407def destroy():
408
409 cursor.execute('''DROP TABLE IF EXISTS Course; DROP TABLE IF EXISTS Meeting; DROP TABLE IF EXISTS Student; DROP TABLE IF EXISTS Enroll; DROP TABLE IF EXISTS Has''')
410 conn.commit()
411
412
413def main():
414 global CUR_FILE
415 destroy()
416 initialize()
417
418 print("inserting into database")
419 tstart = time.time()
420
421 if len(sys.argv) == 2:
422 start_time = time.time()
423 ifilepath = str(sys.argv[1])
424 sys.stdout.write(ifilepath + ": ")
425 readCSV('Grades/' + ifilepath)
426 end_time = time.time()
427 total_time = end_time - start_time
428 print(total_time)
429
430 else:
431 for file in os.listdir('./Grades'):
432 if file.endswith('.csv'):
433 CUR_FILE = file
434 start_time = time.time()
435 sys.stdout.write(file + ": ")
436 readCSV('Grades/'+ file)
437 end_time = time.time()
438 total_time = end_time - start_time
439 print(total_time)
440
441
442 tend = time.time()
443 total = tend - tstart
444 print('')
445 print('total time: ' + str(total))
446 conn.close()
447
448
449if __name__ == '__main__': main()