def getAttendanceByDate(self, info_dict):
try:
mydb = MyDatabase()
conn = mydb.get_connection()
sql_select_query = '''select count(*) from attendance where
course_id = %s and roll_number= %s and semester_year= %s and semester_type=%s
and date_of_class=%s '''
cursor = conn.cursor()
record_tuple = (info_dict['course_id'], info_dict['roll_number'],
info_dict['semester_year'],
info_dict['semester_type'],
info_dict['date_of_class'])
cursor.execute(sql_select_query, record_tuple)
records = cursor.fetchall()
ret_val = ''
for row in records:
if (row[0] >= 1):
ret_val = 'True'
break
except Error as e:
print("Error reading data from MySQL table", e)
finally:
if (conn.is_connected()):
conn.close()
cursor.close()
return ret_val
def markAttendance(self):
try:
mydb = MyDatabase()
conn = mydb.get_connection()
insert_query = '''insert into attendance(course_id,roll_number,
full_name,semester_year,semester_type,date_of_class,time_of_class,
class_room) values (%s,%s,%s,%s,%s,%s,%s,%s)'''
cursor = conn.cursor()
##mysql date format is YYYYMMDD
##mysql time format is HH:MM:SS
now = datetime.now()
today_date = now.strftime("%Y/%m/%d")
today_time = now.strftime("%H:%M:%S")
record_tuple = (self.course_id, self.roll_number, self.full_name,
self.semester_year, self.semester_type, today_date,
today_time, self.class_room)
cursor.execute(insert_query, record_tuple)
conn.commit()
print('Student ', self.full_name,
' Attendance Marked Successfully!')
#conn.close()
#cursor.close()
except mysql.connector.Error as error:
print("Failed to insert into MySQL table {}".format(error))
finally:
if (conn.is_connected()):
conn.close()
cursor.close()
def register_course(self):
try:
mydb = MyDatabase()
conn = mydb.get_connection()
cursor = conn.cursor()
cursor.execute("insert into course_reg(course_id,roll_number,Semester_type,Semester_year) values(%s,%s,%s,%s);",(self.course_id,self.roll_number,self.Semester_type,self.Semester_year))
conn.commit()
print('student with roll_number',self.roll_number,'registered for Course ',self.course_id,'Successfully!')
except mysql.connector.Error as error:
print("Failed to insert into MySQL table {}".format(error))
finally:
if(conn.is_connected()):
conn.close()
cursor.close()
def get_course_id(self, course_name):
try:
mydb = MyDatabase()
conn = mydb.get_connection()
cursor = conn.cursor()
cursor.execute(
"select course_id from courses where course_name=(%s)",
(course_name, ))
record = cursor.fetchone()
except Error as e:
print("Error reading data from MySQL table", e)
finally:
if (conn.is_connected()):
conn.close()
cursor.close()
return record[0]
def getSleepCount(self, roll_number, course_id):
try:
mydb = MyDatabase()
conn = mydb.get_connection()
cursor = conn.cursor()
select_query = "select count(*) from drowsy_data where course_id=(%s) and roll_number=(%s)"
record_tuple = (course_id, roll_number)
cursor.execute(select_query, record_tuple)
record = cursor.fetchone()
except Error as e:
print("Error reading data from MySQL table", e)
finally:
if (conn.is_connected()):
conn.close()
cursor.close()
return record[0]
def getNextRollNumber(self):
try:
mydb=MyDatabase()
conn=mydb.get_connection()
sql_select_query='''SELECT AUTO_INCREMENT FROM information_schema.TABLES
WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s;'''
record_tuple=('VAMS','students')
cursor=conn.cursor()
cursor.execute(sql_select_query,record_tuple)
records = cursor.fetchone()
ret_val=records[0]
except Error as e:
print("Error reading data from MySQL table", e)
finally:
if(conn.is_connected()):
conn.close()
cursor.close()
return ret_val
def add_course(self):
try:
mydb = MyDatabase()
conn = mydb.get_connection()
cursor = conn.cursor()
cursor.execute(
"insert into courses(course_id,course_name) values(%s,%s);",
(self.course_id, self.course_name))
conn.commit()
print('Course ', self.course_id, ' Added Successfully!')
except mysql.connector.Error as error:
print("Failed to insert into MySQL table {}".format(error))
finally:
if (conn.is_connected()):
conn.close()
cursor.close()
def get_courseid(self,query_dict):
try:
mydb = MyDatabase()
conn = mydb.get_connection()
cursor = conn.cursor()
cursor.execute("select course_id from course_reg where roll_number=(%s) and Semester_type=(%s) and Semester_year=(%s);",(query_dict['roll_number'],query_dict['Semester_type'],query_dict['Semester_year']))
res=cursor.fetchall()
courses=[]
for course in res:
courses.append(course[0])
except Error as e:
print("Error reading data from MySQL table", e)
finally:
if(conn.is_connected()):
conn.close()
cursor.close()
return courses
def getAllStudentRecord(self):
try:
roll_number=[]
name=[]
mydb=MyDatabase()
conn=mydb.get_connection()
sql_select_query='''select roll_number,full_name from students;'''
cursor=conn.cursor()
cursor.execute(sql_select_query)
records = cursor.fetchall()
for row in records:
roll_number.append(row[0])
name.append(row[1])
except Error as e:
print("Error reading data from MySQL table", e)
finally:
if(conn.is_connected()):
conn.close()
cursor.close()
return roll_number,name
def markStudentSleepy(self, roll_list, course_id):
date = datetime.now().strftime("%Y/%m/%d")
if (len(roll_list) != 0):
try:
mydb = MyDatabase()
conn = mydb.get_connection()
cursor = conn.cursor()
for roll_numb in roll_list:
cursor.execute(
"insert into drowsy_data(course_id,roll_number,date_of_class) values(%s,%s,%s);",
(course_id, roll_numb, date))
conn.commit()
#print('Successfully recorded the details of students found sleepy')
except mysql.connector.Error as error:
print("Failed to insert into MySQL table {}".format(error))
finally:
if (conn.is_connected()):
conn.close()
cursor.close()
def getFacialFeatures(self,roll_number):
try:
mydb=MyDatabase()
conn=mydb.get_connection()
sql_select_query='''select facial_features from students where roll_number = %s'''
cursor=conn.cursor()
cursor.execute(sql_select_query,(roll_number,))
records = cursor.fetchall()
for row in records:
#print (row)
ret_val=row[0]
break
except Error as e:
print("Error reading data from MySQL table", e)
finally:
if(conn.is_connected()):
conn.close()
cursor.close()
return ret_val
def createStudent(self):
try:
mydb=MyDatabase()
conn=mydb.get_connection()
insert_query='''insert into students(full_name,facial_features) values (%s,%s)'''
cursor = conn.cursor()
record_tuple=(self.name,self.facial_features)
next_num=self.getNextRollNumber()
cursor.execute(insert_query, record_tuple)
conn.commit()
print('Student ',self.name,' Registered Successfully!')
print(self.name,' has roll number : ',next_num)
conn.close()
cursor.close()
except mysql.connector.Error as error:
print("Failed to insert into MySQL table {}".format(error))
finally:
if(conn.is_connected()):
conn.close()
cursor.close()
def getAttendanceByMonth(self, info_dict):
try:
mydb = MyDatabase()
conn = mydb.get_connection()
sql_select_query = '''select count(*) from attendance where
course_id = %s and roll_number= %s and semester_year= %s and semester_type=%s
and MONTH(date_of_class)=%s '''
cursor = conn.cursor()
record_tuple = (info_dict['course_id'], info_dict['roll_number'],
info_dict['semester_year'],
info_dict['semester_type'], info_dict['month'])
cursor.execute(sql_select_query, record_tuple)
records = cursor.fetchone()
ats_count = 0
if (len(records) >= 1):
ats_count = records[0]
except Error as e:
print("Error reading data from MySQL table", e)
finally:
if (conn.is_connected()):
conn.close()
cursor.close()
return ats_count