Parts Implemented by Mehmet Altuner¶
People¶
Note
All table creations exist in db_init.py file.
Note
All SQL operations in python files are wrapped with try-except statements in order to avoid and validate errors
1. Creation¶
CREATE TABLE IF NOT EXISTS PEOPLE (
P_ID SERIAL PRIMARY KEY,
NAME VARCHAR(100),
EMAIL VARCHAR(120) UNIQUE,
PHOTO VARCHAR(120),
PASSWORD VARCHAR(280),
TYPE VARCHAR(120)
)
2. Adding¶
Adding new person is handled in add_person() method in database.py file
def add_person(self, person):
try:
with dbapi2.connect(self.url) as connection:
cursor = connection.cursor()
statement = "INSERT INTO PEOPLE (NAME, EMAIL, PHOTO, PASSWORD, TYPE) VALUES (%s, %s, %s, %s, %s)"
data = [person.name, person.mail, person.photo, person.password, person.type]
cursor.execute(statement, data)
statement = "SELECT P_ID FROM PEOPLE WHERE EMAIL = %s"
data = [person.mail]
cursor.execute(statement, data)
value = cursor.fetchone()
person.id = value[0]
cursor.close()
except Exception as err:
print("Add Person Error : ", err)
return person
add_person() method takes a people object as a parameter named person. (Person object is defined in models/people.py). After inserting the new value into the table, its auto-incremented id instance is selected by another statement and written into the person instance to be used in the future.
Warning
The email instance is unique
3. Reading¶
Reading from person table is implemented in the method get_person() in database.py file.
def get_person(self, p_id):
try:
with dbapi2.connect(self.url) as connection:
cursor = connection.cursor()
statement = "SELECT * FROM PEOPLE WHERE P_ID = %s"
data = [p_id]
cursor.execute(statement, data)
value = cursor.fetchone()
cursor.close()
if not value:
return None
person = People(value[0], value[1], value[2], value[3])
return person
except Exception as err:
print("Error: ", err)
return None
get_person() method takes an integer named p_id and returns the data of the column having the id equal to*p_id*.
4. Updating¶
Deleting operation is handled in the method update_person() in database.py file.
def update_person(self, person_id, attrs, values):
person = self.get_person(person_id)
if not person:
return False
if person:
try:
with dbapi2.connect(self.url) as connection:
cursor = connection.cursor()
statement = "UPDATE PEOPLE SET "
for attr in attrs[:-1]:
statement += attr + " = %s , "
statement += attrs[-1] + " = %s "
statement += " WHERE p_id = %s"
values.append(person_id)
cursor.execute(statement, values)
cursor.close()
except Exception as err:
print("Update Person Error: ", err)
- The structure of this method is a bit different than other operations’ methods. It takes three parameters:
person_id: An integer that states the person column to be updated.
attrs: A list of names of the attributes that we want to update.
values: The new values of the person object.
The idea is that each instance of list attr must coincide with the each instance of the list values. UPDATE PEOPLE SET person.attrs[i] = values[i] where id = p_id
4. Deleting¶
By the nature of the table itself, there is no need to implement a deletion operation for this table. It is simply a base class. We have stated ON DELETE CASCADE on each table we reference people from.
Students¶
1. Creation¶
CREATE TABLE IF NOT EXISTS STUDENTS (
STU_ID INTEGER PRIMARY KEY ,
NUMBER INTEGER,
EARNED_CREDITS INTEGER,
DEPARTMENT INTEGER NOT NULL,
FACULTY INTEGER NOT NULL,
CLUB INTEGER,
LAB INTEGER,
FOREIGN KEY (STU_ID) REFERENCES PEOPLE ON DELETE CASCADE,
FOREIGN KEY (DEPARTMENT) REFERENCES DEPARTMENTS,
FOREIGN KEY (FACULTY) REFERENCES FACULTIES,
FOREIGN KEY (CLUB) REFERENCES CLUBS,
FOREIGN KEY (LAB) REFERENCES LABS
)
Warning
Student’s pkey is also the fkey for the People table since People can only be students if they are students.
2. Adding¶
Adding operation is handled in the method add_student() in database.py file.
def add_student(self, student):
person = self.add_person(student.get_person_obj())
try:
with dbapi2.connect(self.url) as connection:
cursor = connection.cursor()
statement = "INSERT INTO STUDENTS (STU_ID, NUMBER, EARNED_CREDITS, DEPARTMENT, FACULTY, CLUB, LAB) VALUES (%s, %s, %s, %s, %s, %s, %s)"
data = [person.id, student.number, student.cred, student.depart, student.facu, student.club,
student.lab]
cursor.execute(statement, data)
cursor.close()
except Exception as err:
print("Error: ", err)
3. Reading¶
Reading operation is handled in the method get_student() in database.py file.
def get_student(self, stu_id):
try:
with dbapi2.connect(self.url) as connection:
cursor = connection.cursor()
statement = "SELECT * FROM STUDENTS WHERE STU_ID = %s"
values = [stu_id]
cursor.execute(statement, values)
data = cursor.fetchone()
cursor.close()
return data
except Exception as err:
print("Get Student Error: ", err)
return None
Selects the student that has the id as same as the stu_id which are taken by a parameter.
4. Updating¶
Updating operation is handled in the method update_student() in database.py file.
def update_student(self, student_key, attrs, values):
student = self.get_student(student_key)
if not student:
return False
if student:
try:
with dbapi2.connect(self.url) as connection:
cursor = connection.cursor()
statement = "UPDATE STUDENTS SET "
for attr in attrs[:-1]:
statement += attr + " = %s , "
statement += attrs[-1] + " = %s "
statement += " WHERE stu_id = %s"
values.append(student_key)
cursor.execute(statement, values)
cursor.close()
except Exception as err:
print("Update Student Error: ", err)
The same approach is followed as the update operation of the People table. UPDATE STUDENTS SET students.attr[i] = values[i] where stu_id = student_key
5. Deleting¶
Updating operation is handled in the method delete_student() in database.py file.
def delete_student(self, student_key):
student = self.get_student(student_key)
if student:
try:
with dbapi2.connect(self.url) as connection:
cursor = connection.cursor()
statement = "DELETE FROM STUDENTS WHERE stu_id = %s"
values = [student_key]
cursor.execute(statement, values)
cursor.close()
except Exception as err:
print("Delete Student Error: ", err)
Warning
If you delete a student, the People instance it references to is also deleted.
Lessons¶
1. Creation¶
CREATE TABLE IF NOT EXISTS LESSONS (
LESSON_ID SERIAL PRIMARY KEY,
CAP INTEGER,
ENROLLED INTEGER,
DATE VARCHAR(280),
CRN INTEGER UNIQUE NOT NULL,
CODE VARCHAR(7),
INSTRUCTOR INTEGER,
LOCATION INTEGER,
ASSISTANT INTEGER,
CREDIT INTEGER,
FOREIGN KEY (INSTRUCTOR) REFERENCES INSTRUCTORS(INS_ID),
FOREIGN KEY (ASSISTANT) REFERENCES ASSISTANTS(AS_ID),
FOREIGN KEY (LOCATION) REFERENCES CLASSES(CL_ID)
)
2. Adding¶
Adding operation is handled in the method create_lesson() in database.py file.
def create_lesson(self, lesson):
try:
with dbapi2.connect(self.url) as connection:
cursor = connection.cursor()
statement = """INSERT INTO LESSONS (CRN, DATE, CODE, INSTRUCTOR, LOCATION, ASSISTANT, CREDIT, CAP, ENROLLED)
VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s)"""
values = [lesson.crn, lesson.date, lesson.code, lesson.instructor, lesson.location, lesson.assistant, lesson.credit, lesson.cap, lesson.enrolled]
cursor.execute(statement, values)
cursor.close()
return True
except Exception as err:
print("Create Lesson Error: ", err)
return False
3. Reading¶
Reading operation is handled in the methods search_lesson_by_crn() and search_lesson_by_instructor() in database.py file. The obvious difference between the methods is that one of them selects the lessons by the given CRN while the other does the same operation with the name of its instructor.
def search_lesson_by_crn(self, crn):
try:
with dbapi2.connect(self.url) as connection:
cursor = connection.cursor()
statement = """SELECT * FROM LESSONS
JOIN INSTRUCTORS ON (LESSONS.instructor = INSTRUCTORS.ins_id)
JOIN PEOPLE ON (INSTRUCTORS.ins_id = PEOPLE.p_id)
JOIN CLASSES ON (LESSONS.location = CLASSES.cl_id)
WHERE LESSONS.crn = %s
"""
values = [crn]
cursor.execute(statement, values)
data = cursor.fetchall()
cursor.close()
return data
except Exception as err:
print("Search Lesson Error: ", err)
return False
def search_lesson_by_instructor(self, instructor):
try:
with dbapi2.connect(self.url) as connection:
cursor = connection.cursor()
statement = """SELECT * FROM
LESSONS
JOIN INSTRUCTORS ON (LESSONS.instructor = INSTRUCTORS.ins_id)
JOIN PEOPLE ON (INSTRUCTORS.ins_id = PEOPLE.p_id)
JOIN CLASSES ON (LESSONS.location = CLASSES.cl_id)
WHERE PEOPLE.name = %s
"""
values = [instructor]
cursor.execute(statement, values)
data = cursor.fetchall()
cursor.close()
return data
except Exception as err:
print("Search Lesson Error: ", err)
Here in both of the methods. JOIN operations are used.