def insert_course_data(db, url, year):
# ===================================================================================
# test environment
# ===================================================================================
# content = open("temp_professor.json", "rb").read()
# professor = json.loads(content.decode("utf-8"))
# professor = json.loads(professor)
# ===================================================================================
# productive evvironment
# ===================================================================================
professor, _ = helper.get_professor(db, url)
DB_NAME = db.get_name()
tables = db.show_tables(DB_NAME)
for table in tables:
# to check whether the table is course table
if not helper.check_table(table, year):
continue
db_data = db.describe_table(table)
depart = db_data[3][0][0:4]
web_data = professor[depart]
insert_data = dict()
check_new_course(db, table, web_data, year)
time_data = "'" + str(datetime.now()).split(".")[0] + "'"
values = [["time", time_data]]
for cour_prof, number in web_data.items():
depart_cour_prof = (depart + cour_prof).replace(" ", "").replace(
"/", "_").replace("'", "").replace("-", "_")
values.append([depart_cour_prof, number])
db.insert_data(values, table)
def create_course_table(db,url, year):
# test environment
# content = open("temp_professor.json", "rb").read()
# professor = json.loads(content.decode("utf-8"))
# professor = json.loads(professor)
# productive evvironment
professor, _ = helper.get_professor(db, url)
global_key_list = []
course_key_list = []
num_count = 0
letter_index = 0
for depart, courses in sorted(professor.items()):
global_key_list.append(["id", "MEDIUMINT NOT NULL AUTO_INCREMENT"])
global_key_list.append(["time", "DATETIME"])
global_key_list.append(["comments", "varchar(11)"])
for course, number in sorted(courses.items()):
course_key = string.ascii_lowercase[letter_index] + str(num_count)
letter_index += 1
if letter_index == 26:
num_count += 1
letter_index = 0
keyInfo = (depart + course).replace(" ", "").replace("/", "_").replace("'", "").replace("-", "_")
global_key_list.append([keyInfo, "int(4)"])
prof = course[4:]
course_key_list.append([keyInfo, course_key, prof, depart])
# leave some empty slot for some late added course.
for index in range(8):
colName = "empty{}".format(str(index))
global_key_list.append([colName, "int(4)"])
db.create_tables(depart+year, global_key_list, "id")
global_key_list.clear()
def insert_course_info(db, year, url):
# db.drop_table("courseInfo")
if "courseInfo" not in db.show_tables(DB_NAME):
db.create_tables("courseInfo",
[("id", "MEDIUMINT NOT NULL AUTO_INCREMENT"),
("key1", "varchar(40)"),
("courseName", "VARCHAR(70)"),
("department", "VARCHAR(5)"),
("courseCode", "VARCHAR(5)"),
("professor", "VARCHAR(40)"), ("max", "VARCHAR(5)"),
("time", "VARCHAR(5)"), ("syllabus", "VARCHAR(100)"),
("comment1", "VARCHAR(100)")], ["key1", "id"])
# check whether the courseInfo table is created or not.
courseNum = len(db.queryColsData("courseInfo", [("time", "'S19'")]))
if courseNum == 0:
_, courses = helper.get_professor(db, url)
helper.storeCourseInfoTable(courses, db, "courseInfo", year)