def getAllExams(search=""):
cursor = db.cursor()
if search == "":
sql = """SELECT `exam_id`, `exam_name`, `form`, `term`, `year`, `created_at`, `deleted` FROM `exams`
WHERE deleted = 0 ORDER BY year DESC"""
else:
sql = """SELECT `exam_id`, `exam_name`, `form`, `term`, `year`, `created_at`, `deleted` FROM `exams`
WHERE deleted = 0 AND (exam_name LIKE %s OR form LIKE %s) ORDER BY year DESCgetPreviousExam"""
try:
if search == "":
cursor.execute(sql)
else:
cursor.execute(sql, (
'%' + search + '%',
'%' + search + '%',
))
data = [{
'exam_id': row[0],
'exam_name': row[1],
'form': row[2],
'term': row[3],
'year': row[4],
'created_at': row[5]
} for row in cursor.fetchall()]
ret = data
except (MySQLdb.Error, MySQLdb.Warning) as e:
# ret = e
ret = False
return ret
def changePassword(data):
m = hashlib.md5()
m.update(data['password'])
data['password'] = m.hexdigest()
cursor = db.cursor()
sql = """ UPDATE users SET password = %s WHERE user_id = %s """
data = (data["password"], data["user_id"])
try:
cursor.execute(sql, data)
db.commit()
ret = True
except (MySQLdb.Error, MySQLdb.Warning) as e:
print(e)
db.rollback()
# ret = e
ret = False
return ret
def getSubjectByID(id):
cursor = db.cursor()
sql = """SELECT `subject_id`, `subject_name`, `subject_alias`, `compulsory`, s.group_id, `group_name`
FROM subjects s
JOIN subject_groups g ON g.group_id = s.group_id
WHERE deleted = 0 AND subject_id = %s""" % id
try:
cursor.execute(sql)
data = [{
'subject_id': row[0],
'subject_name': row[1],
'subject_alias': row[2],
'compulsory': "Yes" if row[3] == 1 else "No",
'group_id': row[4],
'group_name': row[5],
} for row in cursor.fetchall()]
ret = data[0]
except (MySQLdb.Error, MySQLdb.Warning) as e:
print e
ret = False
return ret
def getSubjectsByTeacher(user_id):
cursor = db.cursor()
sql = """SELECT u.user_id, s1.subject_id AS subject_id1, s1.subject_name AS subject_name1, s1.subject_alias AS subject_alias1, s1.compulsory AS subject1compulsory,
s2.subject_id AS subject_id2, s2.subject_name As subject_name2, s2.subject_alias AS subject_alias2, s2.compulsory AS subject2compulsory
FROM users u
JOIN subjects s1 ON s1.subject_id = u.subject1 AND s1.deleted = 0
LEFT JOIN subjects s2 ON s2.subject_id = u.subject2 AND s2.deleted = 0
WHERE u.user_id = '%s'""" % user_id
try:
cursor.execute(sql)
data = {}
for row in cursor:
data['user_id'] = row[0]
data['subject_id1'] = row[1]
data['subject_name1'] = row[2]
data['subject_alias1'] = row[3]
data['subject1compulsory'] = row[4]
data['subject_id2'] = row[5]
data['subject_name2'] = row[6]
data['subject_alias2'] = row[7]
data['subject2compulsory'] = row[8]
ret = data
except (MySQLdb.Error, MySQLdb.Warning) as e:
print e
ret = False
return ret
def getOptionalSubjects(): # used in query for subject selection
cursor = db.cursor()
sql = """SELECT `subject_alias`, `subject_name`, `subject_id`
FROM `subjects`
WHERE compulsory NOT IN (1)
AND deleted = 0 """
try:
cursor.execute(sql)
data = [item[0].lower() for item in cursor.fetchall()]
aliases = []
names = []
ids = []
for row in cursor:
aliases.append(row[0].lower().capitalize())
names.append(row[1])
ids.append(row[2])
data = {"aliases": aliases, "names": names, "ids": ids}
ret = data
except (MySQLdb.Error, MySQLdb.Warning) as e:
print e
ret = False
return ret
def checkOldPassword(user_id, old_password):
m = hashlib.md5()
m.update(old_password)
old_password = m.hexdigest()
cursor = db.cursor()
# binary keyword to perform case sensitive search
sql = """SELECT user_id, first_name, last_name, surname, dob, gender, username, password
FROM `users`
WHERE user_id = %s AND binary password = %s"""
try:
cursor.execute(sql, (
user_id,
old_password,
))
count = cursor.rowcount
if count < 1:
ret = False
else:
ret = True
except (MySQLdb.Error, MySQLdb.Warning) as e:
ret = False
db.rollback()
return ret
def getEvents(search=""):
cursor = db.cursor()
if search == "":
sql = """SELECT `event_id`, `event_name`, `event_date`, `event_desc`
FROM `events` WHERE deleted = 0"""
else:
sql = """SELECT `event_id`, `event_name`, `event_date`, `event_desc`
FROM `events` WHERE deleted = 0 AND (event_name LIKE %s OR event_date LIKE %s OR event_desc LIKE %s)"""
try:
if search == "":
cursor.execute(sql)
else:
cursor.execute(sql, (
'%' + search + '%',
'%' + search + '%',
'%' + search + '%',
))
data = [{
'event_id': row[0],
'event_name': row[1],
'event_date': row[2],
'event_desc': row[3]
} for row in cursor.fetchall()]
ret = data
except (MySQLdb.Error, MySQLdb.Warning) as e:
print e
# ret = e
ret = False
return ret
def saveOneExam(data):
c = datetime.now()
data["created_at"] = c.strftime('%Y-%m-%d')
cursor = db.cursor()
sql = """
INSERT INTO `exams`(`exam_id`, `exam_name`, `form`, `term`, `year`, `created_at`, `deleted`)
VALUES (%s, %s, %s, %s, %s, %s, %s)
"""
try:
cursor.execute(sql, (0, data["exam_name"], data["form"], data["term"], data["year"], data["created_at"], 0))
db.commit()
ret = True
except(MySQLdb.Error, MySQLdb.Warning) as e:
print(e)
db.rollback()
ret = False
# db.close()
return ret
def saveOneClass(class_name, form_name):
class_id = 0
deleted = "0"
cursor = db.cursor()
sql = """
INSERT INTO `classes`(`class_id`, `class_name`, `form_name`, `deleted`)
VALUES (%s, %s, %s, %s)
"""
try:
cursor.execute(sql, (class_id, class_name, form_name, deleted))
db.commit()
ret = True
except (MySQLdb.Error, MySQLdb.Warning) as e:
# print(e)
# print cursor._last_executed
db.rollback()
ret = False
return ret
def getUploadData(table_name):
cursor = db.cursor()
sql = """SELECT * FROM %s WHERE uploaded = 0""" % table_name
try:
cursor.execute(sql)
# cursor.description gives a tuple of tuples where [0] for each is the column name.
field_names = [i[0] for i in cursor.description]
dataArray = []
for row in cursor:
data = {}
for field_index, field_name in enumerate(field_names):
data[field_name] = row[field_index]
dataArray.append(data)
ret = dataArray
except (MySQLdb.Error, MySQLdb.Warning) as e:
# ret = e
ret = False
return ret
def saveClass(data):
data["class_id"] = 0
data["deleted"] = "0"
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to INSERT a record into the database.
sql = """
INSERT INTO `classes`(`class_id`, `class_name`, `form_name`, `deleted`)
VALUES (%s, %s, %s, %s)
"""
try:
# Execute the SQL command
cursor.execute(sql, (data["class_id"], data["class_name"],
data["form_name"], data["deleted"]))
# Commit your changes in the database
db.commit()
ret = cursor.lastrowid
except (MySQLdb.Error, MySQLdb.Warning) as e:
print(e)
# Rollback in case there is any error
db.rollback()
ret = False
# # disconnect from server
# db.close()
return ret
def editEvent(data):
dte = str(data["event_date"])
dte = dte[:-9]
data["event_date"] = datetime.strptime(dte, "%d/%m/%Y").date()
cursor = db.cursor()
sql = """ UPDATE events SET
event_name = %s,
event_desc = %s,
event_date = %s
WHERE event_id = %s """
data = (data["event_name"], data["event_desc"], data["event_date"],
data["event_id"])
try:
cursor.execute(sql, data)
db.commit()
ret = True
except (MySQLdb.Error, MySQLdb.Warning) as e:
db.rollback()
ret = False
return ret
def saveEvent(data):
data["event_id"] = 0
dte = str(data["event_date"])
dte = dte[:-9]
data["event_date"] = datetime.strptime(dte, "%d/%m/%Y").date()
cursor = db.cursor()
sql = """INSERT INTO `events`(`event_id`, `event_name`, `event_date`, `event_desc`)
VALUES (%s, %s, %s, %s)"""
try:
cursor.execute(sql, (data["event_id"], data["event_name"],
data["event_date"], data["event_desc"]))
db.commit()
ret = cursor.lastrowid
except (MySQLdb.Error, MySQLdb.Warning) as e:
print(e)
# Rollback in case there is any error
db.rollback()
ret = False
return ret
def getSubjectsInGroup(group_name):
cursor = db.cursor()
sql = """SELECT `subject_id`, `subject_name`, `subject_alias`, `compulsory`, group_name
FROM subjects s
JOIN subject_groups g ON g.group_id = s.group_id
WHERE deleted = 0 and group_name = '%s'""" % group_name
try:
cursor.execute(sql)
dataArray = []
for row in cursor:
data = {
'id': row[0],
'name': row[1],
'alias': row[2],
'compulsory': row[1],
}
dataArray.append(data)
ret = dataArray
except (MySQLdb.Error, MySQLdb.Warning) as e:
print e
ret = False
return ret
def getClassMean(data, columns):
columnStr = ''
for x in columns:
columnStr = columnStr + x + ', '
schDets = getSchoolDetails()
subjects_lower_forms = schDets['lower_subjects']
if data['form'] == 1 or 2:
no_of_subjects = subjects_lower_forms
else:
no_of_subjects = 8
cursor = db.cursor()
# to create dynamic sum of all subjects eg IFNULL(Eng, 0)+IFNULL(Kis, 0)+IFNULL(Mat, 0)+...
sum_cols = ''
indexes = len(columns) - 1
for key, val in enumerate(columns):
if key == indexes: # To avoid adding + after the last subject
sum_cols = sum_cols + 'IFNULL(' + val + ', 0)'
else:
sum_cols = sum_cols + 'IFNULL(' + val + ', 0)' + '+'
# To get all classes ie if class_id =0, exam_id > 0 ie exam has been selected
if data["class_id"] == 0 and data["exam_id"] > 0:
sql = "SELECT SUM(%s)/%s AS mean \
FROM exam_results er \
JOIN exams e ON e.exam_id = er.exam_id \
JOIN users u ON u.user_id = er.student_id AND u.deleted = %d \
JOIN classes c ON c.class_id = u.class_id AND c.form_name = %d AND u.deleted = %d \
WHERE e.exam_id = %d GROUP BY er.exam_result_id ORDER BY mean DESC" % (sum_cols, no_of_subjects, 0, int(data['form']), 0, data['exam_id'])
else:
sql = "SELECT SUM(%s)/%s AS mean \
FROM exam_results er \
JOIN exams e ON e.exam_id = er.exam_id \
JOIN users u ON u.user_id = er.student_id AND u.deleted = %d \
JOIN classes c ON c.class_id = u.class_id AND c.class_id = %d AND u.deleted = %d \
WHERE e.exam_id = %d GROUP BY er.exam_result_id ORDER BY mean DESC" % (sum_cols, no_of_subjects, 0, int(data['class_id']), 0, data['exam_id'])
try:
cursor.execute(sql)
data = [item[0] for item in cursor.fetchall()]
if data[0] is not None:
mean = calculateMean(data)
# mean = getGrade(data[0])
else:
mean = ""
ret = mean
except(MySQLdb.Error, MySQLdb.Warning) as e:
ret = False
return ret
def getSubjectMean(data):
cursor = db.cursor()
# if class_id == 0 means all classes in form, exam_id == 0 to make sure there's data
if data['class_id'] == 0 and data["exam_id"] > 0:
sql = """SELECT AVG(%s)
FROM exam_results er
JOIN exams e ON e.exam_id = er.exam_id
JOIN users u ON u.user_id = er.student_id AND u.deleted = %d
JOIN classes c ON c.class_id = u.class_id AND c.form_name = %d AND u.deleted = %d
WHERE e.exam_id = %d """ % (data['subject_alias'], 0, int(data['form']), 0, data['exam_id'])
else:
sql = "SELECT AVG(%s) \
FROM exam_results er \
JOIN exams e ON e.exam_id = er.exam_id \
JOIN users u ON u.user_id = er.student_id AND u.deleted = %d \
JOIN classes c ON c.class_id = u.class_id AND c.class_id = %d AND u.deleted = %d \
WHERE e.exam_id = %d" % (data['subject_alias'], 0, data['class_id'], 0, data['exam_id'])
try:
cursor.execute(sql)
mean = [item[0] for item in cursor.fetchall()]
mean = mean[0]
if mean is None:
mean = 0
ret = round(mean, 3)
except(MySQLdb.Error, MySQLdb.Warning) as e:
ret = False
return ret
def updateFormFoursToAlumnus(formFours):
cursor = db.cursor()
user_ids = ""
no_of_ids = len(formFours) - 1
for key, val in enumerate(formFours):
if key == no_of_ids: # To avoid adding a comma after the last id
user_ids = user_ids + str(val)
else:
user_ids = user_ids + str(val) + ', '
sql = """UPDATE users SET alumnus = 1 WHERE user_id IN (%s) AND deleted = 0""" % user_ids
try:
cursor.execute(sql)
db.commit()
ret = True
except (MySQLdb.Error, MySQLdb.Warning) as e:
print e
db.rollback()
ret = False
return ret
def editSubject(data):
alias = data['subject_alias']
# Remove spaces and special characters from the alias
alias = ''.join(e for e in alias if e.isalnum())
data['subject_alias'] = alias
cursor = db.cursor()
sql = """ UPDATE subjects SET
subject_name = %s,
subject_alias = %s,
compulsory = %s,
group_id = %s
WHERE subject_id = %s """
data = (data["subject_name"], data["subject_alias"], data["compulsory"],
data["group"], data["subject_id"])
try:
cursor.execute(sql, data)
db.commit()
ret = True
except (MySQLdb.Error, MySQLdb.Warning) as e:
db.rollback()
ret = False
return ret
def editStudent(data):
cursor = db.cursor()
sql = """ UPDATE users SET
reg_no = %s,
first_name = %s,
last_name = %s,
surname = %s,
dob = %s,
gender = %s,
class_id = %s,
kcpe_marks = %s,
birth_cert_no = %s,
next_of_kin_name = %s,
next_of_kin_phone = %s
WHERE user_id = %s """
data = (data["reg_no"], data["first_name"], data["last_name"], data["surname"], data["dob"], data["gender"], data["class_id"],
data["kcpe_marks"], data["birth_cert_no"], data["kin_names"], data["kin_phone"], data["user_id"])
try:
cursor.execute(sql, data)
db.commit()
ret = True
except(MySQLdb.Error, MySQLdb.Warning) as e:
print(e)
db.rollback()
# ret = e
ret = False
return ret
def editTeacher(data):
cursor = db.cursor()
sql = """ UPDATE users SET
first_name = %s,
last_name = %s,
surname = %s,
email = %s,
username = %s,
dob = %s,
gender = %s,
subject1 = %s,
subject2 = %s
WHERE user_id = %s """
data = (data["first_name"], data["last_name"], data["surname"], data["email"], data["username"], data["dob"], data["gender"], data["subjectOneID"], data["subjectTwoID"], data["user_id"])
try:
cursor.execute(sql, data)
db.commit()
ret = True
except(MySQLdb.Error, MySQLdb.Warning) as e:
print(e)
db.rollback()
# ret = e
ret = False
return ret
def getStudentByID(id):
# prepare a cursor object using cursor() method
cursor = db.cursor()
sql = """SELECT first_name, last_name, surname, class_name, form_name, reg_no
FROM `users` u
JOIN classes c
ON c.class_id = u.class_id
WHERE u.deleted = 0 AND user_id = %s AND role='%s'""" % (
id, 'student')
try:
cursor.execute(sql)
data = [{
'full_names': row[2] + " " + row[0] + " " + row[1],
'class': row[3],
'form': row[4],
'reg_no': row[5]
} for row in cursor.fetchall()]
ret = data[0]
except (MySQLdb.Error, MySQLdb.Warning) as e:
print e
# ret = e
ret = False
return ret
def getFormClasses(form):
cursor = db.cursor()
sql = "SELECT class_id, class_name FROM classes \
WHERE form_name = '%d'" % form
try:
cursor.execute(sql)
ids = []
names = []
for row in cursor:
ids.append(row[0])
names.append(row[1])
data = {
"ids": ids,
"names": names
}
ret = data
except(MySQLdb.Error, MySQLdb.Warning) as e:
ret = False
return ret
def getClassNamesWithForm():
# prepare a cursor object using cursor() method
cursor = db.cursor()
sql = "SELECT class_id, class_name, form_name FROM classes WHERE deleted = 0 ORDER BY form_name"
try:
# Execute the SQL command
cursor.execute(sql)
ids = []
names = []
forms = []
for row in cursor:
ids.append(row[0])
names.append(row[1] if 'Form' in row[1] else str(row[2]) + " " + row[1]) # To avoid having '1 Form 1'
forms.append(row[2])
data = {
"ids": ids,
"names": names,
"forms": forms,
}
ret = data
except(MySQLdb.Error, MySQLdb.Warning) as e:
print e
print cursor._last_executed
ret = False
return ret
def updateTotalledFieldManyRows(result_ids):
result_ids_string = ''
indexes = len(result_ids) - 1 # -1 because first index is 0
for key, val in enumerate(result_ids):
if key == indexes: # To avoid adding + or , after the last subject
result_ids_string = result_ids_string + str(val)
else:
result_ids_string = result_ids_string + str(val) + ', '
cursor = db.cursor()
sql = """ UPDATE `exam_results` SET `totalled`= 1 WHERE exam_result_id IN (%s) """ % result_ids_string
try:
cursor.execute(sql)
db.commit()
ret = True
except (MySQLdb.Error, MySQLdb.Warning) as e:
print e
db.rollback()
ret = False
return ret
def getPositionsOneClass(exam_id, class_id):
cursor = db.cursor()
# Order by firstname in case totals are equal
sql = """SELECT `exam_result_id`, `total`
FROM exam_results e
JOIN users u ON u.user_id = e.student_id
JOIN classes c ON c.class_id = u.class_id AND c.class_id = %s
WHERE exam_id = %s ORDER BY total DESC, first_name""" % (
class_id, exam_id)
try:
cursor.execute(sql)
dataArray = []
count = 1
for row in cursor.fetchall():
data = {'exam_result_id': row[0], 'class_position': count}
count += 1
dataArray.append(data)
ret = dataArray
except (MySQLdb.Error, MySQLdb.Warning) as e:
print e
ret = False
return ret
def getMarksAndStudentIDOneRow(
result_id, subjects): # returns the sum of all marks in that row
subjects_string = ''
indexes = len(subjects) - 1 # -1 because first index is 0
for key, val in enumerate(subjects):
if key == indexes: # To avoid adding + after the last subject
subjects_string = subjects_string + val
else:
subjects_string = subjects_string + val + ', '
cursor = db.cursor()
sql = """SELECT %s, student_id FROM `exam_results` WHERE exam_result_id = %s""" % (
subjects_string, result_id)
try:
cursor.execute(sql)
marks = {}
for row in cursor.fetchall():
for key, value in enumerate(subjects):
marks[value.lower()] = 0 if row[key] is None else row[key]
student_id = row[len(subjects)]
data = {'marks': marks, 'student_id': student_id}
ret = data
except (MySQLdb.Error, MySQLdb.Warning) as e:
print e
ret = False
return ret
def getExamsinTerm(term, year):
cursor = db.cursor()
sql = "SELECT exam_id, exam_name, form FROM exams \
WHERE deleted = '%d' \
AND year = '%s' \
AND term = '%s'" % (0, year, term)
try:
cursor.execute(sql)
ids = []
names = []
forms = []
names_n_form = []
for row in cursor:
ids.append(row[0])
names.append(row[1])
forms.append(row[2])
names_n_form.append("Form " + row[2] + " " + row[1])
data = {
"ids": ids,
"names": names,
"forms": forms,
"names_n_form": names_n_form
}
ret = data
except(MySQLdb.Error, MySQLdb.Warning) as e:
ret = False
return ret
def saveStudent(data):
data["user_id"] = 0
dob = str(data["dob"])
dob = dob[:-9]
data["dob"] = datetime.strptime(dob, "%d/%m/%Y").date()
# student username = their regno@schoolname eg, 7276@kangangu.
# student password = next of kin phone number
data["username"] = str(data['reg_no'])+"@kangangu"
data["password"] = data["kin_phone"]
m = hashlib.md5()
m.update(data["password"])
data["password"] = m.hexdigest()
data["role"] = "student"
data["status"] = "Active"
c = datetime.now()
data["created_at"] = c.strftime('%Y-%m-%d %H:%M:%S')
data["deleted"] = "0"
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to INSERT a record into the database.
sql = """
INSERT INTO `users`(`user_id`, `reg_no`, `first_name`, `last_name`, `surname`, `dob`, `gender`, `username`,
`password`, `role`, `class_id`, `kcpe_marks`, `birth_cert_no`, `next_of_kin_name`, `next_of_kin_phone`,
`address`, `status`, `created_at`, `deleted`)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
"""
try:
# Execute the SQL command
cursor.execute(sql, (data["user_id"], data["reg_no"], data["first_name"], data["last_name"], data["surname"], data["dob"], data["gender"],
data["username"], data["password"], data["role"], data["class_id"], data["kcpe_marks"], data["birth_cert_no"],
data["kin_names"], data["kin_phone"], data["address"], data["status"], data["created_at"], data["deleted"] ))
# Commit your changes in the database
db.commit()
ret = True
except(MySQLdb.Error, MySQLdb.Warning) as e:
print(e)
# Rollback in case there is any error
db.rollback()
ret = False
# # disconnect from server
# db.close()
return ret
def getStudentList(class_id, subject_id):
cursor = db.cursor()
sql = """SELECT user_id, first_name, last_name, surname, subjects_taken
FROM `users` u
JOIN classes c
ON c.class_id = u.class_id AND c.class_id='%s'
WHERE u.deleted = 0 AND c.deleted = 0 AND role='%s'""" % (class_id, 'student')
try:
cursor.execute(sql)
students_in_subject = [] # Store students that already selected subject before
dataArray = []
for row in cursor:
data = {}
data['user_id'] = row[0]
data['student_name'] = row[1] + " " + row[2] + " " + row[3]
data['subjects'] = row[4].split(",") if row[4] is not None else ""
# Check if student already selected subject in question
if data['subjects'] != "":
if data['subjects'].count(str(subject_id)) == 0: # Returns number of times item occurs in list
data['subject_ticked'] = False
else:
data['subject_ticked'] = True
stud = {}
stud["user_id"] = data['user_id']
stud["student_name"] = data['student_name']
students_in_subject.append(stud)
else:
data['subject_ticked'] = False
dataArray.append(data)
data = {
'student_list': dataArray,
'students_present': students_in_subject
}
ret = data
except(MySQLdb.Error, MySQLdb.Warning) as e:
print e
ret = False
return ret
def saveAdminDetails(data):
data["user_id"] = 0
dob = str(data["dob"])
dob = dob[:-9]
data["dob"] = datetime.strptime(dob, "%d/%m/%Y").date()
m = hashlib.md5()
m.update(data['password'])
data['password'] = m.hexdigest()
data["role"] = "admin"
data["status"] = "Active"
c = datetime.now()
data["created_at"] = c.strftime('%Y-%m-%d %H:%M:%S')
data["deleted"] = "0"
cursor = db.cursor()
sql = """
INSERT INTO `users`(`user_id`, `first_name`, `last_name`, `surname`, `email`, `phone_number`, `dob`, `gender`,
`username`, `password`, `role`, `status`, `created_at`, `deleted`)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
"""
try:
cursor.execute(
sql,
(data["user_id"], data["first_name"], data["last_name"],
data["surname"], data["email"], data["phone"], data["dob"],
data["gender"], data["username"], data["password"], data["role"],
data["status"], data["created_at"], data["deleted"]))
db.commit()
data['user_id'] = cursor.lastrowid
data['class_id'] = 0
data['reg_no'] = 0
ret = data
except (MySQLdb.Error, MySQLdb.Warning) as e:
print(e)
db.rollback()
ret = False
# disconnect from server
cursor.close()
return ret