-
Notifications
You must be signed in to change notification settings - Fork 0
/
populateDB.py
82 lines (65 loc) · 2.69 KB
/
populateDB.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
import psycopg2
import codecs
import json
import os
def importFile(f):
jsonFile = open(f,"r").read()
return json.loads(jsonFile)
def formatTables(cur):
cur.execute("DELETE FROM course_requirement;")
cur.execute("DELETE FROM course;")
def popCourse(cur,courseDict):
if courseDict["description"] != None:
d = courseDict["description"].replace("'","''")
executeStr = "INSERT INTO course (num, title, department, description) VALUES ('{}', '{}', '{}', '{}');"\
.format(courseDict["number"],
courseDict["title"].replace("'","''"),
courseDict["subject"],
d)
# if the description is None, then do not include the description so the value is set to null
else:
executeStr = "INSERT INTO course (num, title, department) VALUES ('{}', '{}', '{}');"\
.format(courseDict["number"],
courseDict["title"].replace("'","''"),
courseDict["subject"])
cur.execute(executeStr)
def popCourseReq(cur,courseDict):
executeStr = ""
if len(courseDict["fulfills"]) > 0:
for i in range(len(courseDict["fulfills"])):
cur.execute("""
SELECT course.id, req.id
FROM (SELECT id FROM course WHERE num = %s AND title = %s) AS course,
(SELECT id FROM requirement WHERE name = %s) AS req
""",(courseDict["number"],courseDict["title"],courseDict["fulfills"][i]))
temp = cur.fetchall()
cur.execute("INSERT INTO course_requirement (course, requirement) VALUES ('{}','{}')\n"\
.format(temp[0][0],temp[0][1]))
else:
cur.execute("""
SELECT course.id, req.id
FROM (SELECT id FROM course WHERE num = '{}') AS course,
(SELECT id FROM requirement WHERE name = 'None') AS req;
""".format(courseDict["number"],"None"))
temp = cur.fetchall()
cur.execute("INSERT INTO course_requirement (course, requirement) VALUES (%s,%s);",(temp[0][0],temp[0][1]))
def popDB(conn,obj):
cur = conn.cursor()
formatTables(cur)
c = 0
for item in obj:
c+=1
print("course_id",c,item["title"],item["subject"],item["fulfills"])
popCourse(cur, item)
popCourseReq(cur, item)
def run(f):
conn = psycopg2.connect(os.environ["DATABASE_URL"])
#conn = psycopg2.connect(dbname="gened", user="conzty01")
jsonObj = importFile(f)
print("populating database")
popDB(conn,jsonObj)
print("commiting changes")
conn.commit()
print("completed changes")
if __name__ == "__main__":
run("lcCourses.json")