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 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 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 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 index_temp(): if request.method == "GET": session.clear() return render_template("temp/index.html") if request.method == "POST": name = request.form['name'] email = request.form['email'] university = request.form['university'] cursor.execute("SELECT file FROM users_temp WHERE email='%s'" % (email)) data = cursor.fetchall() if (cursor.rowcount > 0): session['user_file'] = data[0][0] session['logged'] = True return jsonify({"status": "User already exists!"}) session['user_file'] = name.lower().replace(" ", "") + email.replace( ".", "") + ".txt" cursor.execute( "INSERT INTO users_temp(name, email, university, file) VALUES('%s', '%s', '%s', '%s')" % (name, email, university, name.lower().replace(" ", "") + email.replace(".", "") + ".txt")) db.commit() return jsonify({"status": "User added put into db!"})
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 reset_pass(): if request.method == "GET": return render_template("reset-pass.html", navbar=Markup(NAVBAR), footer=Markup(FOOTER)) if request.method == "POST": email = request.form['email'] password = request.form['password'] hash = hashlib.sha512(password.encode()) cursor.execute( "UPDATE users SET password='******' WHERE SHA2(email, 512)='%s'" % (hash.hexdigest(), email)) db.commit() return jsonify({ "status": "success", "title": "Success!", "message": "Password reset successfully!", "href": core_str + "/login" })
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 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 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 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 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 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 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 add_model(): if request.method == "GET": return render_template("add-model.html", logout=Markup(NAVLOGREG), navbar=Markup(NAVBARLOGGED), footer=Markup(FOOTER)) if request.method == "POST": model_name = request.form['model_name'] model_desc = request.form['model_desc'] dataset = request.form['dataset'] code = request.files['code'] model = request.files['model'] code_filename = secure_filename(code.filename) model_filename = secure_filename(model.filename) hash_id = hashlib.sha512(str(session['user_id']).encode()) if code_filename.split(".")[-1] != "zip" or model_filename.split( ".")[-1] != "zip": return jsonify({ "status": "error", "title": "Error!", "message": "Only zip files accepted!", "href": core_str + "/add-model" }) code_filename_hashed = ''.join( code_filename.split(".") [0:-1]) + hash_id.hexdigest() + "." + code_filename.split(".")[-1] model_filename_hashed = ''.join( model_filename.split(".") [0:-1]) + hash_id.hexdigest() + "." + model_filename.split(".")[-1] code.save("code/" + code_filename_hashed) model.save("model/" + model_filename_hashed) cursor.execute("SELECT name FROM users WHERE id=%d" % (session['user_id'])) data = cursor.fetchall() cursor.execute( "INSERT INTO model(uid, uname, name, des, dataset, code, model, approved) VALUES('%d', '%s', '%s', '%s', '%s', '%s', '%s', '%d')" % (session['user_id'], data[0][0], model_name, model_desc, dataset, code_filename_hashed, model_filename_hashed, 0)) db.commit() return jsonify({ "status": "success", "title": "Success!", "message": "Model added successfully!", "href": core_str + "/profile" })
def verify(): if request.method == "GET": email = request.args.get('q') cursor.execute("UPDATE users SET status=1 WHERE email='%s'" % (email)) db.commit() return render_template("login.html", verified="1")
def model(): if request.method == "GET": id = int(request.args.get('q')) cursor.execute("SELECT * FROM model WHERE id=%d" % (id)) data = cursor.fetchall() dataset_link = "Not given!" if (data[0][5] != ""): dataset_link = data[0][5] this_owner = False if (session['user_id'] == data[0][1]): this_owner = True return render_template("model.html", name=data[0][3], des=Markup(data[0][4]), dataset=dataset_link, code="code/" + data[0][6], model="model/" + data[0][7], owner=this_owner, logout=Markup(NAVLOGREG), navbar=Markup(NAVBARLOGGED), footer=Markup(FOOTER)) if request.method == "POST": id = int(request.form['id']) desc = request.form['desc'] cursor.execute("SELECT des FROM model where id=%d" % (id)) data = cursor.fetchone() if (data[0] == desc): return jsonify({ "status": "error", "title": "Error!", "message": "Nothing to update!", "href": core_str + "/model?q=" + str(id) }) cursor.execute("UPDATE model SET des='%s' WHERE id='%d'" % (desc, id)) db.commit() return jsonify({ "status": "success", "title": "Success!", "message": "Description updated successfully!", "href": core_str + "/model?q=" + str(id) })
def put(): question = request.form['question'].replace("'", r"\'") type = request.form['type'] ask_friend = int(request.form['ask_friend']) cursor.execute( "INSERT INTO questions(question, type, ask_friend) VALUES('%s', '%s', '%d')" % (question, type, ask_friend)) db.commit() return jsonify({"status": "Question successfully put into db!"})
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
def markRowsAsUploaded(table_name): cursor = db.cursor() sql = """UPDATE %s SET uploaded = 1""" % table_name try: cursor.execute(sql) db.commit() ret = True except (MySQLdb.Error, MySQLdb.Warning) as e: db.rollback() ret = False return ret
def add(): if request.method == "GET": return render_template("add.html") elif request.method == "POST": title = request.form['title'] desc = request.form['desc'] cursor.execute( "INSERT INTO learnt(title, description) VALUES('%s', '%s')" % (title, desc)) db.commit() return jsonify({"ret": "success"})
def saveSubject(data): alias = data['subject_alias'] alias = ''.join( e for e in alias if e.isalnum()) # Remove spaces and special characters from the alias data['subject_alias'] = alias data["deleted"] = "0" cursor = db.cursor() sql = """ INSERT INTO `subjects`(`subject_id`, `subject_name`, `subject_alias`, `compulsory`, `group_id`, `deleted`) VALUES (%s, %s, %s, %s, %s, %s) """ try: cursor.execute(sql, (0, data["subject_name"], data["subject_alias"], data["compulsory"], data["group"], data["deleted"])) db.commit() # Add subject as column to exam_results table data["subject_alias"] = data["subject_alias"].lower() sql = "ALTER TABLE exam_results ADD COLUMN %s DOUBLE DEFAULT NULL" % data[ "subject_alias"] try: cursor.execute(sql) db.commit() ret = True except (MySQLdb.Error, MySQLdb.Warning) as e: ret = False print e db.rollback() except (MySQLdb.Error, MySQLdb.Warning) as e: db.rollback() ret = False # db.close() return ret
def updateSubjectsTakenOneStudent(user_id, subjects_taken): cursor = db.cursor() sql = """UPDATE users SET subjects_taken = '%s' WHERE user_id = %s """ % (subjects_taken, user_id) try: cursor.execute(sql) db.commit() ret = True except(MySQLdb.Error, MySQLdb.Warning) as e: db.rollback() ret = False return ret
def saveTeacher(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"] = "teacher" 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`, `subject1`, `subject2`, `address`, `national_id`, `tsc_no`, `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: 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["subjectOneID"], data["subjectTwoID"], data["address"], data["national_id"], data["tsc_no"], data["status"], data["created_at"], data["deleted"])) db.commit() ret = True except(MySQLdb.Error, MySQLdb.Warning) as e: print(e) db.rollback() ret = False # # disconnect from server # db.close() return ret
def deleteEvent(id): cursor = db.cursor() sql = """ UPDATE events SET deleted = %s WHERE event_id = %s """ data = (1, id) try: cursor.execute(sql, data) db.commit() ret = True except (MySQLdb.Error, MySQLdb.Warning) as e: print e db.rollback() ret = False return ret
def saveOneForm(form_name, streams): cursor = db.cursor() sql = """INSERT INTO `forms`(`form_name`, `no_of_streams`) VALUES (%s, %s)""" try: cursor.execute(sql, (form_name, streams)) db.commit() ret = True except (MySQLdb.Error, MySQLdb.Warning) as e: print(e) db.rollback() ret = False return ret
def updateStudentsToNextClass(current_id, next_id): cursor = db.cursor() sql = """UPDATE users SET class_id = %s WHERE class_id = %s AND alumnus = 0 AND deleted = 0 AND role = 'student'""" % ( next_id, current_id) try: cursor.execute(sql) db.commit() ret = True except (MySQLdb.Error, MySQLdb.Warning) as e: print e db.rollback() ret = False return ret
def updateTotalForOneRowExamResults(result_id, stats): cursor = db.cursor() sql = """ UPDATE `exam_results` SET total = %s, points = %s, mean = %s WHERE exam_result_id = %s """ % ( stats['total'], stats['points'], stats['mean'], result_id) try: cursor.execute(sql) db.commit() ret = True except (MySQLdb.Error, MySQLdb.Warning) as e: # print e db.rollback() ret = False return ret
def updateClassPosition(data): cursor = db.cursor() sql = """ UPDATE `exam_results` SET class_pos = %s WHERE exam_result_id = %s """ % ( data['class_position'], data['exam_result_id']) try: cursor.execute(sql) db.commit() ret = True except (MySQLdb.Error, MySQLdb.Warning) as e: print e db.rollback() ret = False return ret
def deleteTeacher(id): cursor = db.cursor() sql = """ UPDATE users SET deleted = %s WHERE user_id = %s """ data = (1, id) try: cursor.execute(sql, data) db.commit() ret = True except(MySQLdb.Error, MySQLdb.Warning) as e: # print(e) db.rollback() ret = False return ret