def user_exists(id):
cnx = create_database_connection()
resp = execute_query("SELECT * FROM users WHERE id = {};".format(id), cnx)
cnx.close()
if resp:
return True
else:
return False
def set_student_events(student_id, events_list, final):
cnx = create_database_connection()
cursor = cnx.cursor()
cursor.execute(
"""DELETE FROM student_events where student_id = {} and final = {};""".
format(student_id, final), cnx)
for event in events_list:
cursor.execute(
"""INSERT INTO student_events (student_id, event_id, final) VALUES ({}, {}, {});"""
.format(student_id, event.idx, final), cnx)
cnx.commit()
cnx.close()
def get_student_events(id, final):
cnx = create_database_connection()
if final == False:
resp = execute_query(
"SELECT * FROM events LEFT JOIN student_events se on events.id = se.event_id WHERE (student_id = {} and se.final = False) or study_group = '0';"
.format(id), cnx)
else:
resp = execute_query(
"SELECT * FROM events LEFT JOIN student_events se on events.id = se.event_id WHERE (student_id= {} and se.final = True) or study_group = '0';"
.format(id), cnx)
cnx.close()
return resp
def drop_and_create_joined_table():
cnx = create_database_connection()
cursor = cnx.cursor()
query = f"DROP TABLE IF EXISTS student_events;"
cursor.execute(query)
query = f"""
CREATE TABLE student_events (
student_id int REFERENCES users (id) ON UPDATE CASCADE ON DELETE CASCADE,
event_id int REFERENCES events (id) ON UPDATE CASCADE ON DELETE CASCADE,
final bool,
CONSTRAINT student_events_pkey PRIMARY KEY (student_id, event_id, final)
);"""
cursor.execute(query)
cnx.commit()
cnx.close()
def get_timetable_and_add_to_database():
cnx = create_database_connection()
resp = requests.get(
"https://planzajec.eaiib.agh.edu.pl/view/timetable/645/events?start=2020-03-02&end=2020-03-07&_=1585327741228"
)
dataArray = resp.json()
dateFormat = '%Y-%m-%dT%H:%M:%S%z'
for i, currentElement in enumerate(dataArray, start=0):
group = str(currentElement.get('group')).replace(".1", "a").replace(
".2", "b")
if group == '0':
title = currentElement.get('title').split(',')[0]
room = currentElement.get('title').split(',')[1].split('<br/>')[1]
teacher = currentElement.get('title').split(',')[2]
if len(currentElement.get('title').split(',')) > 3:
teacher += currentElement.get('title').split(',')[3].split(
'<br/>')[0]
else:
title = currentElement.get('title').split(',')[0]
room = currentElement.get('title').split(',')[2].split('<br/>')[1]
teacher = currentElement.get('title').split(',')[3]
if len(currentElement.get('title').split(',')) > 4:
teacher += currentElement.get('title').split(',')[4].split(
'<br/>')[0]
day = datetime.strptime(currentElement.get('start'),
dateFormat).weekday() + 1
start = datetime.strptime(currentElement.get('start'),
dateFormat).time()
end = datetime.strptime(currentElement.get('end'), dateFormat).time()
info = ""
if len(currentElement.get('title').split('<br/>')) == 3:
info = currentElement.get('title').split('<br/>')[2]
teacher = teacher.split(':')[1].strip()
room = room.split(':')[1].strip()
event = TimetableEvent(title, room, teacher, group, day, start, end,
info)
print(i, ": ", event)
add_to_database(event, cnx)
cnx.commit()
cnx.close()
def get_courses_for_form():
cnx = create_database_connection()
resp = execute_query(
"SELECT * FROM events WHERE study_group NOT LIKE '0' ORDER BY title, study_group;",
cnx)
cnx.close()
courses = []
prev_title = ""
course = []
for i in range(len(resp)):
event = databaseToModel(resp[i])
if event.title != prev_title:
if course:
courses.append(course.copy())
course = [event.title]
prev_title = event.title
course.append(event.group)
return courses
def drop_and_create_table():
cnx = create_database_connection()
cursor = cnx.cursor()
query = f"DROP TABLE IF EXISTS events;"
cursor.execute(query)
query = f"""
CREATE TABLE events (
id SERIAL PRIMARY KEY,
title VARCHAR (60) NOT NULL,
room VARCHAR (40),
teacher VARCHAR (50),
study_group VARCHAR(2),
day integer,
start_time time,
end_time time,
info VARCHAR(500)
)
"""
cursor.execute(query)
cnx.commit()
cnx.close()
def getCoursesList():
weekdays = ["pon.", "wt.", "sr.", "czw.", "pt."]
cnx = create_database_connection()
resp = execute_query("SELECT * FROM events WHERE study_group NOT LIKE '0' ORDER BY title;", cnx)
# print(resp)
cnx.close()
# json_dict = {}
courses = []
# events_list = []
# for i in range (len(resp)):
# # events_list.append(databaseToModel(resp[i]))
# event = databaseToModel(resp[i])
# if not event.title in json_dict:
# json_dict[event.title] = []
# single_event_dict = {}
# single_event_dict["id"] = event.idx
# single_event_dict["info"] = weekdays[event.day-1] + ", " + str(event.start) + "-" + str(event.end) + ", " + event.teacher
# json_dict[event.title].append(single_event_dict)
# return dumps({"courses": json_dict}, ensure_ascii=False)
course_title = ""
course = {"name": ""}
for i in range(len(resp)):
event = databaseToModel(resp[i])
# print(course)
if event.title != course["name"]:
if course["name"] != "":
courses.append(course.copy())
course["events"] = []
course["name"] = event.title
single_event_dict = {"id": event.idx, "info": "gr. "+ event.group +", "+weekdays[event.day - 1] + " " + str(event.start) + "-" + str(
event.end) + ", " + event.teacher}
course["events"].append(single_event_dict)
# print(courses)
return dumps({"courses": courses}, ensure_ascii=False)
def get_all_events():
cnx = create_database_connection()
resp = execute_query("SELECT * FROM events;", cnx)
cnx.close()
return resp
