-
Notifications
You must be signed in to change notification settings - Fork 0
/
createDB.py
83 lines (69 loc) · 2.33 KB
/
createDB.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
import psycopg2
import json
import os
def createCourse(cursor):
cursor.execute("DROP TABLE IF EXISTS course CASCADE;")
cursor.execute("""
CREATE TABLE course (
id serial,
num varchar(50),
description text,
title varchar(200),
department varchar(100),
PRIMARY KEY (id)
);
""")
def createRequirement(cursor,fName):
cursor.execute("DROP TABLE IF EXISTS requirement CASCADE;")
cursor.execute("""
CREATE TABLE requirement (
id serial,
name varchar(100),
description text,
PRIMARY KEY (id)
);
""")
genEds = getGenEdSet(fName)
#print(genEds)
for i in genEds:
cursor.execute("INSERT INTO requirement (name) VALUES ('{}')".format(i))
# Adds a 'None' requirement that will connect to courses that satisfy none
cursor.execute("INSERT INTO requirement (name) VALUES ('None')")
def createCourseReq(cursor):
cursor.execute("DROP TABLE IF EXISTS course_requirement")
cursor.execute("""
CREATE TABLE course_requirement (
course int,
requirement int,
PRIMARY KEY (course, requirement),
FOREIGN KEY (course) REFERENCES course(id),
FOREIGN KEY (requirement) REFERENCES requirement(id)
);
""")
def getGenEdSet(fName):
# genEds = set()
# f = open(fName,"r").read()
#
# jsonFile = json.loads(f)
#
# for course in jsonFile:
# for ge in course["fulfills"]:
# genEds.add(ge)
#
# return genEds
return {'Intercultural', 'Human Behavior', 'Quantitative', 'Human Expression', 'Religion', 'Natural World--Lab', 'Human Behavior--Social Science Methods', 'Wellness', 'Skills', 'Human Expression--Primary Texts', 'Natural World--Nonlab', 'Historical', 'Biblical Studies'}
def run(f):
conn = psycopg2.connect(os.environ["DATABASE_URL"])
#conn = psycopg2.connect(dbname="gened", user="conzty01")
cur = conn.cursor()
print("creating 'course' table")
createCourse(cur)
print("creating 'requirement' table")
createRequirement(cur,f)
print("creating 'courseReq' table")
createCourseReq(cur)
print("commiting tables")
conn.commit()
print("finished creation")
if __name__ == "__main__":
run("lcCourses.json")