def update_user():
conn = mysql.connect()
cursor = conn.cursor()
try:
_json = request.json
_fullName = _json['fullName']
_userName = _json['userName']
_tenantId = _json['tenantId']
_userPassword = generatePwd(12, string.letters)
_changedTime = datetime.now()
print _fullName
# validate the received values
if _fullName and _userName and _userPassword and _changedTime and _tenantId and request.method == 'POST':
sql = "UPDATE USER SET FULL_NAME=%s, USER_PASSWORD=%s, CHANGED_TIME=%s WHERE TENANT_ID=%s AND USER_NAME=%s"
data = (_fullName, _userPassword, _changedTime, _tenantId,
_userName)
cursor.execute(sql, data)
conn.commit()
resp = jsonify('User updated successfully!')
resp.status_code = 200
return resp
else:
return not_found()
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def update_user():
try:
usr_inp_name = request.form['inputName']
usr_inp_email = request.form['inputEmail']
usr_inp_pass = request.form['inputPassword']
usr_id = request.form['id']
pw_hash = bcrypt.generate_password_hash(usr_inp_pass).decode('utf-8')
if usr_inp_name and usr_inp_email and usr_inp_pass and usr_id and request.method == 'POST':
sql_query = "UPDATE users SET user_name=%s, user_email=%s, user_password=%s WHERE user_id=%s"
usr_inp = (
usr_inp_name,
usr_inp_email,
pw_hash,
usr_id,
)
cn = mysql.connect()
cursor = cn.cursor()
cursor.execute(sql_query, usr_inp)
cn.commit()
flash('Successfully updated user!')
return redirect('/overview')
else:
return 'An error occured while updating the user.'
except Exception as exc:
print(exc)
finally:
cursor.close()
cn.close()
def insertUser():
try:
_json = request.json
_fullName = _json['fullName']
_userName = _json['userName']
_tenantId = _json['tenantId']
_active = True
_userPassword = generatePwd(12, string.letters)
_changedTime = datetime.now()
# _changedTime = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
print _fullName
# validate the received values
if _fullName and _userName and _active and _changedTime and _tenantId and request.method == 'POST':
sql = "INSERT INTO USER(FULL_NAME, USER_NAME, USER_PASSWORD, ACTIVE, CHANGED_TIME, TENANT_ID) VALUES(%s, %s, %s, %s, %s, %s)"
data = (_fullName, _userName, _userPassword, _active, _changedTime,
_tenantId)
conn = mysql.connect()
cursor = conn.cursor()
cursor.execute(sql, data)
conn.commit()
resp = jsonify('User added successfully!')
resp.status_code = 200
return resp
else:
return not_found()
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def add_student(student_info):
try:
new_student_ID = student_info[0]
if (exist(new_student_ID)):
raise Exception()
sql = "INSERT INTO studentsdatabase(Student_ID, Student_name, Student_age) VALUES(%s, %s, %s)"
mysql_connector = mysql.connect()
cursor = mysql_connector.cursor(pymysql.cursors.DictCursor)
cursor.execute(sql, student_info)
mysql_connector.commit()
return student_info
except Exception:
print("Student exist")
def delete_user(id):
try:
cn = mysql.connect()
cursor = cn.cursor()
cursor.execute("DELETE FROM users WHERE user_id=%s", (id, ))
cn.commit()
flash('Successfully deleted user!')
return redirect('/overview')
except Exception as exc:
print(exc)
finally:
cursor.close()
cn.close()
def getUsers():
try:
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute("SELECT * FROM USER")
rows = cursor.fetchall()
resp = jsonify(rows)
resp.status_code = 200
return resp
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def overview():
try:
cn = mysql.connect()
cursor = cn.cursor(pymysql.cursors.DictCursor)
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
table = Results(rows)
table.border = True
return render_template('users.html', table=table)
except Exception as exc:
print(exc)
finally:
cursor.close()
cn.close()
def edit_view(id):
try:
cn = mysql.connect()
cursor = cn.cursor(pymysql.cursors.DictCursor)
cursor.execute("SELECT * FROM users WHERE user_id=%s", id)
row = cursor.fetchone()
if row:
return render_template('edit.html', row=row)
else:
return 'Error loading user with ID: #{id}'.format(id=id)
except Exception as exc:
print(exc)
finally:
cursor.close()
cn.close()
def getUser():
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
try:
_tenantId = request.args.get('_tenantId')
_userName = request.args.get('_userName')
sql = "SELECT * FROM USER WHERE TENANT_ID=%s AND USER_NAME=%s"
data = (int(_tenantId), _userName)
cursor.execute(sql, data)
row = cursor.fetchall()
resp = jsonify(row)
resp.status_code = 200
return resp
except Exception as e:
print(e)
finally:
cursor.close()
conn.close()
def add_user():
try:
usr_name = request.form['inputName']
usr_email = request.form['inputEmail']
usr_pass = request.form['inputPassword']
pw_hash = bcrypt.generate_password_hash(usr_pass).decode('utf-8')
if usr_name and usr_email and usr_pass and request.method == 'POST':
sql_query = "INSERT INTO users(user_name, user_email, user_password) VALUES(%s, %s, %s)"
usr_inp = (usr_name, usr_email, pw_hash)
cn = mysql.connect()
cursor = cn.cursor()
cursor.execute(sql_query, usr_inp)
cn.commit()
flash('User added successfully!')
return redirect('/overview')
else:
return 'An error occured while adding the new user.'
except Exception as exc:
print(exc)
finally:
cursor.close()
cn.close()
def login_submit():
usr_inp_em = request.form['inputEmail']
usr_inp_pw = request.form['inputPassword']
if usr_inp_em and usr_inp_pw and request.method == 'POST':
cn = mysql.connect()
cursor = cn.cursor()
sql_query = "SELECT * FROM users WHERE user_email = %s"
cursor.execute(sql_query, usr_inp_em)
row = cursor.fetchone()
if row:
if bcrypt.check_password_hash(row[3].encode('utf-8'),
usr_inp_pw.encode('utf-8')):
session['email'] = row[2]
session['logged_in'] = True
cursor.close()
cn.close()
return redirect('/overview')
else:
flash('Invalid email or password!')
return redirect('/login')
else:
flash('Invalid email or password!')
return redirect('/login')
def point_to_mysql():
mysql_connector = mysql.connect()
cursor = mysql_connector.cursor(pymysql.cursors.DictCursor)
return mysql_connector, cursor