def auth():
name = request.form['name']
login = request.form['login']
password = request.form['password']
conn = DataBaseConnection(session['connection'])
obj = dict(json.loads(conn.openDB(name, login, password))['openDataBase'])
if not obj['Result']:
return redirect(url_for('/'))
tables = obj['Tables']
session['Tables'] = tables
session['connection'] = conn
session['db'] = conn.db
print('Session db obj', session['db'].toJson())
return redirect('../db?name=' + tables[0])
def defaulf(self, obj):
if isinstance(obj, DataBaseConnection):
e = DataBaseConnection()
e.fromJson(obj)
return e
elif isinstance(obj, DataBase):
return DataBase(obj)
elif isinstance(obj, DataTable):
return DataTable(obj)
elif isinstance(obj, DataRow):
return DataRow(obj)
elif isinstance(obj, set):
return set(obj)
else:
JSONEncoder.default(self, obj)
def delete_course(code):
try:
conn = db.connect()
cursor = conn.conn.cursor()
cursor.execute("SELECT grade FROM Student WHERE courseCodeStd= %s",
(code, ))
student = cursor.fetchone()
ret = AdmReturn()
if student is None:
cursor.execute("DELETE FROM Course WHERE courseCode = %s",
(code, ))
conn.conn.commit()
ret.result = ""
ret.msg = "Course successfully deleted!"
else:
ret.result = ""
ret.msg = "This course can't be deleted! Students have been graded."
return ret
except Error as e:
print("Error while connecting to MySQL", e)
ret = AdmReturn()
ret.result = ""
ret.msg = "An error occurred while deleting"
conn.conn.rollback()
return ret
finally:
if conn.conn.is_connected():
cursor.close()
conn.conn.close()
print("Connection is closed")
def handle(self):
hora = datetime.now()
print(f'Conectado por: {self.client_address} as {hora.hour}:{hora.minute}')
data = self.request.recv(1024).decode()
print(data)
try:
DataBase = DataBaseConnection.DataBaseConnection()
data = json.loads(data)
if data['ID'] == 'AppSendNewRequest':
DataBase.InsertNewRequest(json.dumps(data))
print(DataBase.getAllRequests())
elif data['ID'] == 'DesktopFinishRequest':
DataBase.setFinishRequest(data['phone'])
print(DataBase.getAllRequests())
elif data['ID'] == 'DesktopGetAllPendingRequests':
self.request.send(json.dumps(DataBase.getAllPendingRequests()).encode())
elif data['ID'] == 'DesktopGetAllRequests':
self.request.send(json.dumps(DataBase.getAllRequests()).encode())
except:
print("ALGUM ERRO ACONTECEU")
def find_student(idStd):
try:
conn = db.connect()
cursor = conn.conn.cursor()
cursor.execute("SELECT * FROM Student WHERE idStudents= %s", (idStd, ))
std = cursor.fetchone()
ret = AdmReturn()
if std is not None:
ret.result = std
ret.msg = ""
return ret
else:
ret.result = ""
ret.msg = "Student not found."
return ret
except Error as e:
print("Error while connecting to MySQL", e)
return "An error occurred while searching"
finally:
if conn.conn.is_connected():
cursor.close()
conn.conn.close()
print("Connection is closed")
def get_std_grades_by_user_id(userId, login_main):
try:
conn = db.connect()
cursor = conn.conn.cursor()
cursor.execute("SELECT * FROM Student WHERE userIdStd = %s",
(userId, ))
result = cursor.fetchall()
from GUI import StudentGUI as std
# close parent before open adm
login_main.withdraw()
win = tkinter.Toplevel(login_main)
std.StudentGUI(win, result)
return result
except Error as e:
print("Error while connecting to MySQL", e)
finally:
if conn.conn.is_connected():
cursor.close()
conn.conn.close()
print("Connection is closed")
def insert_update_user(id, username, password, type, update):
try:
user = (id, username, password, type)
user2 = (username, password, type, id)
conn = db.connect()
cursor = conn.conn.cursor()
if not update:
cursor.execute(
"INSERT INTO User (userId, username," +
" password, type) VALUES(%s,%s,%s,%s)", user)
conn.conn.commit()
else:
cursor.execute(
"UPDATE User SET username = %s, password = %s, type = %s WHERE userId = %s",
user2)
conn.conn.commit()
except Error as e:
ret = AdmReturn()
print("Error while connecting to MySQL", e)
ret.result = None
ret.msg = "An error occurred while searching user"
return ret
finally:
if conn.conn.is_connected():
cursor.close()
conn.conn.close()
print("Connection is closed")
def find_user(userId):
try:
conn = db.connect()
cursor = conn.conn.cursor()
cursor.execute("SELECT * FROM User WHERE userId = %s ", (userId, ))
result = cursor.fetchone()
ret = AdmReturn()
ret.result = result
ret.msg = ""
return ret
except Error as e:
ret = AdmReturn()
print("Error while connecting to MySQL", e)
ret.result = None
ret.msg = "An error occurred while searching user"
return ret
finally:
if conn.conn.is_connected():
cursor.close()
conn.conn.close()
print("Connection is closed")
def save(stdId, grade, course):
ret = ProfReturn()
data = (grade, stdId, course)
try:
conn = db.connect()
cursor = conn.conn.cursor()
if stdId and grade and course:
cursor.execute(
"UPDATE Student SET grade = %s WHERE profIdStd = %s AND courseCodeStd = %s",
data)
conn.conn.commit()
ret.msg = "Grade updated"
return ret
except Error as e:
print("Error while connecting to MySQL", e)
ret.msg = "Error while updating"
return ret
finally:
if conn.conn.is_connected():
cursor.close()
conn.conn.close()
print("Connection is closed")
def find_course(code):
ret = ProfReturn()
try:
conn = db.connect()
cursor = conn.conn.cursor()
if code is not None:
cursor.execute(
"SELECT courseName FROM Course WHERE courseCode= %s", (code, ))
course = cursor.fetchone()
cursor.execute("SELECT grade FROM Student WHERE courseCodeStd= %s",
(code, ))
std = cursor.fetchone()
if course is not None:
ret.std = std
ret.course = course
return ret
else:
ret.msg = "Course not found"
return ret
except Error as e:
print("Error while connecting to MySQL", e)
ret.msg = "Course not found"
return ret
finally:
if conn.conn.is_connected():
cursor.close()
conn.conn.close()
print("Connection is closed")
def find_professor(idProf):
try:
conn = db.connect()
cursor = conn.conn.cursor()
cursor.execute("SELECT * FROM Professor WHERE idProf= %s", (idProf, ))
prof = cursor.fetchone()
ret = AdmReturn()
if prof is not None:
ret.result = prof
ret.msg = ""
return ret
else:
ret.result = ""
ret.msg = "Professor not found."
return ret
except Error as e:
print("Error while connecting to MySQL", e)
return "An error occurred while searching"
finally:
if conn.conn.is_connected():
cursor.close()
conn.conn.close()
print("Connection is closed")
def delete_professor(idProf, codeCourse):
try:
conn = db.connect()
cursor = conn.conn.cursor()
cursor.execute("SELECT grade FROM Student WHERE courseCodeStd= %s",
(codeCourse, ))
student = cursor.fetchone()
ret = AdmReturn()
if student is not None:
cursor.execute(
"SELECT * FROM Professor WHERE courseCodeProf= %s and idProf = %s",
(codeCourse, idProf))
prof = cursor.fetchone()
if prof is None:
cursor.execute(
"DELETE FROM Professor WHERE idProf = %s and courseCodeProf= %s",
(idProf, codeCourse))
# delete User
cursor.execute("DELETE FROM User WHERE userId = %s",
(idProf, ))
conn.conn.commit()
ret.result = ""
ret.msg = "Professor successfully deleted!"
else:
ret.result = ""
ret.msg = "Can't be deleted! This professor is linked to a grade student."
else:
cursor.execute(
"DELETE FROM Professor WHERE idProf = %s and courseCodeProf= %s",
(idProf, codeCourse))
#delete User
cursor.execute("DELETE FROM User WHERE userId = %s", (idProf, ))
conn.conn.commit()
ret.result = ""
ret.msg = "Professor successfully deleted!"
return ret
except Error as e:
print("Error while connecting to MySQL", e)
ret = AdmReturn()
ret.result = ""
ret.msg = "An error occurred while deleting"
conn.conn.rollback()
return ret
finally:
if conn.conn.is_connected():
cursor.close()
conn.conn.close()
print("Connection is closed")
def save_student(idStudents, studentName, courseCodeStd, grade, userIdStd,
username, password, type, profId):
try:
if not grade:
grade = None
data = (idStudents, studentName, courseCodeStd, grade, userIdStd,
profId)
data2 = (studentName, courseCodeStd, grade, profId, idStudents)
conn = db.connect()
cursor = conn.conn.cursor()
cursor.execute("SELECT * FROM STUDENT WHERE idStudents= %s",
(idStudents, ))
student = cursor.fetchone()
if student is None:
# create user
insert_update_user(idStudents, username, password, type, False)
# create student
cursor.execute(
"INSERT INTO Student (idStudents, studentName, courseCodeStd,"
+ " grade, userIdStd, profIdStd) VALUES(%s,%s,%s,%s,%s,%s)",
data)
conn.conn.commit()
return "Student saved."
else:
if student[3] is None or student[3] == "":
# update user
insert_update_user(idStudents, username, password, type, True)
# update student
cursor.execute(
"UPDATE Student SET studentName = %s, " +
"courseCodeStd = %s, grade = %s, profIdStd = %s " +
"WHERE idStudents = %s", data2)
conn.conn.commit()
return "Student updated."
else:
return "Can't be updated. This student was graded."
except Error as e:
print("Error while connecting to MySQL", e)
return "An error occurred while saving"
finally:
if conn.conn.is_connected():
cursor.close()
conn.conn.close()
print("Connection is closed")
def find_course(code):
try:
conn = db.connect()
cursor = conn.conn.cursor()
if code is not None:
cursor.execute("SELECT * FROM COURSE WHERE courseCode= %s",
(code, ))
course = cursor.fetchone()
ret = AdmReturn()
if course is not None:
ret.result = course
ret.msg = ""
return ret
else:
ret.result = ""
ret.msg = "Course not found"
return ret
else:
cursor.execute("SELECT * FROM COURSE")
course = cursor.fetchall()
courses = []
ret = AdmReturn()
if course is not None:
for cor in course:
courses.append(cor[0])
ret.result = courses
ret.msg = ""
return ret
else:
ret = AdmReturn()
ret.result = ""
ret.msg = "Course not found"
return ret
except Error as e:
print("Error while connecting to MySQL", e)
ret = AdmReturn()
ret.result = ""
ret.msg = "Course not found"
return ret
finally:
if conn.conn.is_connected():
cursor.close()
conn.conn.close()
print("Connection is closed")
def login(username, password, type, login_main):
try:
data = (username, password, type)
conn = db.connect()
cursor = conn.conn.cursor()
cursor.execute("SELECT * FROM User WHERE username = %s " +
"AND password = %s " +
"AND type = %s", data)
result = cursor.fetchone()
if result is not None:
types = enum.UserTypes
if result[3] == "ADM":
from GUI import AdminGUI as adm
# close parent before open adm
login_main.withdraw()
win = tkinter.Toplevel(login_main)
adm.AdminGUI(win)
elif result[3] == types.STUDENT.name:
stdService.get_std_grades_by_user_id(result[0], login_main)
return
else:
from GUI import ProfessorGUI as prof
# close parent before open adm
login_main.withdraw()
win = tkinter.Toplevel(login_main)
prof.ProfessorGUI(win, result[0])
else:
return "User not found"
except Error as e:
print("Error while connecting to MySQL", e)
finally:
if conn.conn.is_connected():
cursor.close()
conn.conn.close()
print("Connection is closed")
def find_student(idStd, profId):
ret = ProfReturn()
try:
conn = db.connect()
cursor = conn.conn.cursor()
cursor.execute(
"SELECT * FROM Student WHERE idStudents= %s and profIdStd = %s",
(idStd, profId))
std = cursor.fetchall()
if std.__len__() > 0:
courseList = []
for c in std:
courseList.append(c[2])
param = ','.join(['%s'] * len(courseList))
sql = """SELECT * FROM Course WHERE courseCode IN (%s)"""
cursor.execute(
"SELECT * FROM Course WHERE courseCode IN (%s)" % param,
tuple(courseList))
courses = cursor.fetchall()
ret.std = std[0]
ret.course = courses
ret.msg = ""
return ret
else:
ret.std = ""
ret.course = ""
ret.msg = "Student not found."
return ret
except Error as e:
print("Error while connecting to MySQL", e)
ret.result = ""
ret.msg = "An error occurred while searching"
return
finally:
if conn.conn.is_connected():
cursor.close()
conn.conn.close()
print("Connection is closed")
def save_course(code, name):
try:
data = (code, name)
data2 = (code, name, code)
conn = db.connect()
cursor = conn.conn.cursor()
cursor.execute("SELECT grade FROM STUDENT WHERE courseCodeStd= %s",
(code, ))
student = cursor.fetchone()
cursor.execute("SELECT * FROM COURSE WHERE courseCode= %s", (code, ))
course = cursor.fetchone()
if course is not None:
if student is None:
cursor.execute(
"UPDATE Course SET courseCode = %s, courseName = %s WHERE courseCode = %s",
data2)
conn.conn.commit()
return "Course updated"
else:
return "This course cannot be updated because is linked to a student grade."
else:
cursor.execute(
"INSERT INTO Course (courseCode, courseName) VALUES(%s,%s)",
data)
conn.conn.commit()
return "Course saved"
except Error as e:
print("Error while connecting to MySQL", e)
return "An error occurred while saving"
finally:
if conn.conn.is_connected():
cursor.close()
conn.conn.close()
print("Connection is closed")
def delete_student(id):
try:
conn = db.connect()
cursor = conn.conn.cursor()
cursor.execute("SELECT grade FROM Student WHERE idStudents= %s",
(id, ))
student = cursor.fetchone()
ret = AdmReturn()
if student[0] is None:
cursor.execute("DELETE FROM Student WHERE idStudents = %s", (id, ))
# delete user
cursor.execute("DELETE FROM User WHERE userId = %s", (id, ))
conn.conn.commit()
ret.result = ""
ret.msg = "Student successfully deleted!"
else:
ret.result = ""
ret.msg = "Can't be deleted! This student was already graded."
return ret
except Error as e:
print("Error while connecting to MySQL", e)
ret = AdmReturn()
ret.result = ""
ret.msg = "An error occurred while deleting"
conn.conn.rollback()
return ret
finally:
if conn.conn.is_connected():
cursor.close()
conn.conn.close()
print("Connection is closed")
def get_table():
conn = DataBaseConnection(session['connection'])
conn.db = session['db']
name = request.args.get('name')
obj = conn.showTable(name)
return obj
if our_user.username == UGiven and our_user.password == PGiven:
return True
else:
return False
except Schema.DoesNotExist:
print("The User you are trying to access does not exist!")
return False
except Schema.MultipleObjectsReturned:
print(
"Somehow, Multiple Possible accounts with the same name exist! Please contact an administrator to fix this error!"
)
return False
#MongoDBTest(db)
db_connect.mongoengine_connect()
#Test_User = Acc.CreateAccount()
#AccIO.SaveDataBaseDocument(Test_User)
users = AccIO.GrabAllDBDocuments()
for i in users:
print(i.email + ", " + i.username + "," + i.password)
#AccIO.ChangeUserName("*****@*****.**")
#AccIO.DeleteUser("*****@*****.**")
#db_connect.mongoengine_db_test()
#if path.isfile('test.json'):
# account_dict = Acc.LoadAcccountFile('test')
sign_in()
#account_dict = Acc.CreateAccount(account_dict)
#accounts = Acc.CreateAccount(accounts)
#Acc.SaveAccountFile(account_dict,"test")
#account_dict = Acc.LoadAcccountFile('test')
def get_data_bases():
conn = DataBaseConnection(session['connection'])
return conn.getDataBases()
def index():
#if 'connection' not in session:
session['connection'] = DataBaseConnection()
return render_template('index.html', **locals())
def save_professor(idProf, nameProf, courseCodeProf, usename, password, type):
try:
data_prof = (idProf, nameProf, courseCodeProf, idProf)
data_prof2 = (nameProf, courseCodeProf, idProf)
conn = db.connect()
cursor = conn.conn.cursor()
# verify professor
cursor.execute(
"SELECT * FROM Professor WHERE idProf= %s and courseCodeProf = %s",
(idProf, courseCodeProf))
professor = cursor.fetchone()
ret = AdmReturn()
if professor is None:
# create user
insert_update_user(idProf, usename, password, type, False)
# create professor
cursor.execute(
"INSERT INTO Professor (idProf, nameProf, courseCodeProf," +
" userIdProf) VALUES(%s,%s,%s,%s)", data_prof)
conn.conn.commit()
ret.result = ""
ret.msg = "Professor saved"
return ret
else:
# verify if has graded student with that course linked to the teacher
cursor.execute("SELECT grade FROM Student WHERE courseCodeStd= %s",
(courseCodeProf, ))
student = cursor.fetchone()
if student is not None:
# Verify if the course is given by the professor
if professor[1] != courseCodeProf:
# update user
insert_update_user(idProf, usename, password, type, True)
# update professor
cursor.execute(
"UPDATE Professor SET nameProf = %s, " +
"courseCodeProf = %s" + "WHERE idProf = %s",
data_prof2)
conn.conn.commit()
ret.result = ""
ret.msg = "Professor updated"
return ret
else:
ret.result = ""
ret.msg = "Can't be updated. This professor is linked with a student grade."
return ret
else:
# update user
insert_update_user(idProf, usename, password, type, True)
cursor.execute(
"UPDATE Professor SET nameProf = %s, " +
"courseCodeProf = %s" + "WHERE idProf = %s", data_prof2)
conn.conn.commit()
ret.result = ""
ret.msg = "Professor updated"
return ret
except Error as e:
conn.conn.rollback()
print("Error while connecting to MySQL", e)
return "An error occurred while saving"
finally:
if conn.conn.is_connected():
cursor.close()
conn.conn.close()
print("Connection is closed")
