[Flask] Create new project for add user,edit,delete,view users in python with mysql connector

[chandranmanikandan]

Hi Friends,

Am new in Python.
I am installing python 3.7 with flask in my windows 8.1
I am trying to create new signup,login,view,delete,edit users page in python script using mysql connector.
I am unable to edit and delete user with the below script.
Could anyone help me.

Myscript below.

from flask import Flask, session, redirect, url_for, escape, request, render_template
from tables import Results
import hashlib
import mysql.connector

app = Flask(__name__)

#######################
#   DATABASE CONFIG   #
#######################

db = mysql.connector.connect(host="localhost", user="root", passwd="mani321", db="python_test")
cur = db.cursor()

@app.route('/')
def index():
    if 'username' in session:
        username_session = escape(session['username']).capitalize()
        return render_template('index.html', session_user_name=username_session)
    return redirect(url_for('login'))


@app.route('/login', methods=['GET', 'POST'])
def login():
    db.connect()
    error = None
    if 'username' in session:
        return redirect(url_for('index'))
    if request.method == 'POST':
        username_form  = request.form['username']
        password_form  = request.form['password']
        hash_pass = hashlib.md5(password_form.encode('utf8')).hexdigest()
#Do not save password as a plain text
        cur.execute("SELECT COUNT(1) FROM submusers WHERE username = %s;", [username_form]) # CHECKS IF USERNAME EXSIST
        if cur.fetchone()[0]:
            cur.execute("SELECT password FROM submusers WHERE username = %s;", [username_form]) # FETCH THE HASHED PASSWORD
            for row in cur.fetchall():
                if hash_pass == row[0]:
                    session['username'] = request.form['username']
                    return redirect(url_for('index'))
                else:
                    print('Invalid Credential')
        else:
            error = "Invalid Credential"
    return render_template('login.html', error=error)

@app.route('/signup')
def signup():
    return render_template('signup.html')

@app.route('/register',methods=['GET','POST'])
def get_data():
  if request.method=='POST':
    username=request.form['username']
    password=request.form['password']
#Validate the received VALUES
    if username and password and request.method == 'POST':
#Do not save password as a plain text
        _hashed_pass =  hashlib.md5(password.encode('utf8')).hexdigest()
        cur.execute("SELECT COUNT(1) FROM submusers WHERE username = %s;", [username]) # CHECKS IF USERNAME EXSIST
        if cur.fetchone()[0]:
            return '<a href="/signup">User already exist. Please add another userrname</a>'
        else:
#Save edits
            query="INSERT INTO submusers(username,password) VALUES(%s,%s)"
        cur.execute(query,(username, _hashed_pass))
        db.commit()
        return render_template('index.html', username=username)
    else:
        return 'Error while adding user'
    cur.close()
    db.close()

@app.route('/viewuser')
def viewuser():
    db.connect()
    cur.execute ("select * from submusers")
    items = cur.fetchall()
    for item in items:
     db.close()
    return render_template('viewuser.html', item=items)

@app.route('/edit', methods=['GET', 'POST'])
#def edit_view(id):
def edit_view():
    db.connect()
    if request.method=='POST':
      ID=request.form['id']
      rows = []
      cur.execute("SELECT * FROM submusers WHERE ID = %s;", [ID])
      rows.append = cur.fetchone()
      #if rows:
    return render_template('edit.html')
      #else:
       #return 'Error loading #{id}'.format(id=id)
    cur.close()
    db.close()

@app.route('/update', methods=['POST'])
def update_user():
 try:
  _name = request.form['username']
  _password = request.form['password']
  _id = request.form['id']
  # validate the received values
  if _name and _password and _id and request.method == 'POST':
   #do not save password as a plain text
   _hashed_password = hashlib.md5(password.encode('utf8')).hexdigest()
   # save edits
   sql = "UPDATE submusers SET username=%s, password=%s WHERE ID=%s"
   data = (_name, _hashed_password, _id,)
   #db.connect()
   #cur = db.cursor()
   cur.execute(sql, data)
   db.commit()
   print('User updated successfully!')
   return redirect('/')
  else:
   return 'Error while updating user'
 except Exception as e:
  print(e)
 finally:
  cur.close()
  db.close()

@app.route('/delete', methods=['GET', 'POST'])
def delete_user():
 try:
  db.connect()
  _id = request.form['id']
  if _id and request.method == 'GET':
   #cur.execute("DELETE FROM submusers WHERE ID=%s", (_id,))
    sql = "DELETE FROM submusers WHERE ID=%s"
    data = (_id,)
    cur.execute(sql, data)
    db.commit()
  print('User deleted successfully!')
  return redirect('/')
 except Exception as e:
  print(e)
 finally:
  cur.close()
  db.close()

@app.route('/logout')
def logout():
    session.pop('username', None)
    return redirect(url_for('index'))

app.secret_key = 'A0Zr98j/3yX R~XHH!jmN]LWX/,?RT'

if __name__ == '__main__':
    app.run(debug=True)

View user.html

Output:
<!DOCTYPE html>
<html>
<head>
    <title>View Users</title>
 </head>
<body>
    <div class="container" style="margin-top:50px;">
        <div class="row">
            <div class="col-md-6 col-md-offset-3 text-center">

										<table border="1" cellpadding="5" cellspacing="5">
										<tr>
												<td colspan="2">
												<a href="{{ url_for('signup') }}">Sign up</a>
				                <a href="{{ url_for('viewuser') }}">View Users</a>
				                <a href="{{ url_for('logout') }}">Logout</a>
												</td>
										</tr>
										<tr>
										<td> USERNAME</td>
										<td>PASSWORD</td>
										<td>ACTION</td>
										</tr>
										{% for d in item %}
										<tr>
												<td>{{ d[1] }}</td>
												<td>{{ d[2] }}</td>
												<!--<td><a href="{{ url_for('edit_view', id = d[0]) }}">Edit</a></td>-->
                        <td><a href="{{ url_for('edit_view', id = d[0]) }}">Edit</a>/<a href="{{ url_for('delete_user', id = d[0]) }}">Delete</a></td>
										</tr>
										{% endfor %}
										</table>
            </div>
        </div>
    </div>
</body>
</html>