Flask-Profile

6 minute read

Published:

This post covers user profile management system using MySQL.

User Profile Management with Flask and MySQL

mysql.server start
mysql -u root -p
CREATE DATABASE IF NOT EXISTS `userprofile` 
DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `userprofile`
CREATE TABLE IF NOT EXISTS `accounts`(
	`id` INT(10) NOT NULL AUTO_INCREMENT,
	`username` VARCHAR(20) NOT NULL,
	`password` VARCHAR(255) NOT NULL,
	`email` VARCHAR(100) NOT NULL,
	`org` VARCHAR(100) NOT NULL,
	`address` VARCHAR(100) NOT NULL,
	`city` VARCHAR(100) NOT NULL,
	`state` VARCHAR(100) NOT NULL,
	`country` VARCHAR(100) NOT NULL,
	`postcode` VARCHAR(100) NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
SHOW TABLES;
/* static/style.css
/* https://www.w3schools.com/howto/howto_js_topnav.asp */

body {
  margin: 0;
  font-family: Arial, Helvetica, sans-serif;
}

.topnav {
  overflow: hidden;
  background-color: #333;
}

.topnav a {
  float: left;
  color: #f2f2f2;
  text-align: center;
  padding: 14px 16px;
  text-decoration: none;
  font-size: 17px;
}

.topnav a:hover {
  background-color: #ddd;
  color: black;
}

.topnav a.active {
  background-color: #4CAF50;
  color: white;
}
<!-- templates/login.html -->
<!doctype html>
<html lang="en">
    <head>
        <title> User Profile </title>
      
        <link rel="stylesheet" href="{{ url_for('static', filename='style.css') }}">
      
    </head>

    <body align="center">
        <div class="topnav">
            <a class="active" href="index">Home</a>
            <a href="display">Display</a>
            <a href="update">Update</a>
            <a href="logout">Logout</a>
        </div>


        <h1>Login</h1>

      
        <form action="{{ url_for('login') }}" method="post">      
            <p>{{msg}}</p>
						
            <input type="text" name="username" placeholder="Enter your username" size="30" required> <br><br>

            <input type="password" name="password" placeholder="Enter your password" size="30" required> <br><br>

            <input type="submit" value="Login"> 
            <input type="reset" value="Reset">

            <p>
              
                <a href="{{ url_for('register')}}">Register here</a>
              
            </p>

        </form>




    </body>


</html>
<!-- templates/register.html -->

<!doctype html>
<html lang="en">
    <head>
        <title> User Profile </title>
	      
        <link rel="stylesheet" href="{{ url_for('static', filename='style.css') }}">
      
    </head>

    <body align="center">
        <div class="topnav">
            <a class="active" href="index">Home</a>
            <a href="display">Display</a>
            <a href="update">Update</a>
            <a href="logout">Logout</a>
        </div>


        <h1>Login</h1>
				
        <form action="{{ url_for('register') }}" method="post">
         
            <p>{{msg}}</p>
					
            <input type="text" name="username" placeholder="Enter your username" size="30" required> <br><br>

            <input type="password" name="password" placeholder="Enter your password" size="30" required> <br><br>

            <input type="email" name="email" placeholder="Enter your email" size="30" required> <br><br>


            <input type="text" name="org" placeholder="Enter your Organization" size="30" required> <br><br>

            <input type="text" name="address" placeholder="Enter your Address" size="30" required> <br><br>

            <input type="text" name="city" placeholder="Enter your City" size="30" required> <br><br>

            <input type="text" name="state" placeholder="Enter your State" size="30" required> <br><br>

            <input type="text" name="country" placeholder="Enter your Country" size="30" required> <br><br>

            <input type="text" name="postcode" placeholder="Enter your Postcode" size="30" required> <br><br>

            <input type="submit" value="Register"> 
            <input type="reset" value="Reset">

            <p>
              
                <a href="{{ url_for('login')}}">Login here</a>
              
            </p>            

        </form>




    </body>


</html>
<!-- templates/index.html -->
<!doctype html>

<html lang='en'>
    <head>
        <title> User Profile </title>
        <meta name="viewport" content="width=device-width, initial-scale=1">
      
        <link rel="stylesheet" href="{{ url_for('static', filename='style.css') }}">
      
    </head>

    <body>
        <div class="topnav">
            <a class="active" href="login">Home</a>
            <a href="display">Display</a>
            <a href="update">Update</a>
            <a href="logout">Logout</a>
        </div>
      
        <div style="padding-left:16px">
            <h2>Welcocme to Profile Management</h2>
          
            <p>{{msg}}</p>
         
        </div>



    </body>
</html>
<!-- templates/display.html -->
<!doctype html>

<html lang='en'>
    <head>
        <title> User Profile </title>
        <meta name="viewport" content="width=device-width, initial-scale=1">
      
        <link rel="stylesheet" href="{{ url_for('static', filename='style.css') }}">
      
    </head>

    <body align="center">
        <div class="topnav">
            <a class="active" href="index">Home</a>
            <a href="display">Display</a>
            <a href="update">Update</a>
            <a href="logout">Logout</a>
        </div>
      
        <div style="padding-left:16px">
            <h2>Welcocme to Profile Management</h2>
          
            <p>{{msg}}</p>
          
        </div>

      
        
        <table align="center">
            <tr>
                <td> Username: </td>
                <td> {{account['username']}} </td>
            </tr>
            <tr>
                <td> Password: </td>
                <td> {{account['password']}} </td>
            </tr>
            <tr>
                <td> Email: </td>
                <td> {{account['email']}} </td>
            </tr>
            <tr>
                <td> Organisation: </td>
                <td> {{account['org']}} </td>
            </tr>
            <tr>
                <td> Address: </td>
                <td> {{account['address']}} </td>
            </tr>
            <tr>
                <td> City: </td>
                <td> {{account['city']}} </td>
            </tr>
            <tr>
                <td> State: </td>
                <td> {{account['state']}} </td>
            </tr>
            <tr>
                <td> Country: </td>
                <td> {{account['country']}} </td>
            </tr>
            <tr>
                <td> Postcode: </td>
                <td> {{account['postcode']}} </td>
            </tr>
        </table>

          

    </body>
</html>
<!-- templates/update.html -->

<!doctype html>
<html lang="en">
    <head>
        <title> User Profile </title>
      
        <link rel="stylesheet" href="{{ url_for('static', filename='style.css') }}">
      
    </head>

    <body align="center">
        <div class="topnav">
            <a class="active" href="index">Home</a>
            <a href="display">Display</a>
            <a href="update">Update</a>
            <a href="logout">Logout</a>
        </div>


        <h1>Welcome to update page</h1>

      	
        <form action="{{ url_for('update') }}" method="post">
            
            <p>{{msg}}</p>
          
            <!--
            <input type="text" name="username" placeholder="Enter your username" size="30" required> <br><br>
            -->
            <input type="password" name="password" placeholder="Enter your password" size="30" required> <br><br>
            <!--
            <input type="email" name="email" placeholder="Enter your email" size="30" required> <br><br>
            -->

            <input type="text" name="org" placeholder="Enter your Organization" size="30" required> <br><br>

            <input type="text" name="address" placeholder="Enter your Address" size="30" required> <br><br>

            <input type="text" name="city" placeholder="Enter your City" size="30" required> <br><br>

            <input type="text" name="state" placeholder="Enter your State" size="30" required> <br><br>

            <input type="text" name="country" placeholder="Enter your Country" size="30" required> <br><br>

            <input type="text" name="postcode" placeholder="Enter your Postcode" size="30" required> <br><br>

            <input type="submit" value="Update"> 
            
        </form>

    </body>


</html>
# app.py
from flask import Flask, render_template, request, redirect, url_for, session
from flask_mysqldb import MySQL
import MySQLdb.cursors
import re

app = Flask(__name__)

app.secret_key = 'secret key' # for session

app.config['MYSQL_HOST'] = 'localhost'
app.config['MYSQL_USER'] = 'root'
app.config['MYSQL_PASSWORD'] = 'mysqlmysql'
app.config['MYSQL_DB'] = 'userprofile'

mysql = MySQL(app)


@app.route('/')
@app.route('/login', methods=['GET', 'POST'])
def login():
    msg = ''

    check = request.form['username'] and  request.form['password']

    if request.method == 'POST' and check:
        username = request.form['username']
        password = request.form['password']

        cursor = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
        sql = f"SELECT * from accounts WHERE username = '{username}' and password='{password}'"
        cursor.execute(sql)
        account = cursor.fetchone()
        if account:
            session['loggedin'] = True
            session['id'] = account['id']
            session['username'] = account['username']
            msg = 'Hello, ' + session['username']
            return render_template('index.html', msg=msg)
        else:
            msg = 'Incorrect username/password'
    return render_template('login.html', msg=msg)


@app.route('/register', methods=['GET', 'POST'])
def register():
    msg = ''

    check =  request.form['username'] and  request.form['password'] and request.form['email'] and request.form['address'] and request.form['city'] and request.form['country'] and request.form['postcode'] and request.form['org']

    if request.method == 'POST' and not check:
        msg = 'Please fill the form'
        return render_template('register.html', msg=msg)

    if request.method == 'POST' and check:
        username = request.form['username']
        password = request.form['password']
        email = request.form['email']
        org = request.form['org']
        address = request.form['address']
        city = request.form['city']
        state = request.form['state']
        country = request.form['country']
        postcode = request.form['postcode']

        
        cursor = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
        sql = f"SELECT * from accounts WHERE username = '{username}'"
        cursor.execute(sql)
        account = cursor.fetchone()

        if account:
            msg = 'Account already exists'
        else:
            sql = f"INSERT INTO accounts VALUES (NULL, '{username}', '{password}', '{email}', '{org}', '{address}', '{city}', '{state}', '{country}', '{postcode}' )"
            cursor.execute(sql)
            mysql.connection.commit()

            msg = 'You have been registered'

    return render_template('register.html', msg=msg)


@app.route('/update', methods=['GET', 'POST'])
def update():

    msg = ''

    if not 'loggedin' in session:
        return redirect(url_for('login'))

    if request.method != 'POST':
        msg = 'Please update your details'
        return render_template('update.html', msg=msg)

    check =  request.form['address'] and request.form['city'] and request.form['country'] and request.form['postcode'] and request.form['org']

    if request.method == 'POST' and not check:
        msg = 'Please fill the form'
        return render_template('update.html', msg=msg)

    if request.method == 'POST' and check:
        password = request.form['password']
        org = request.form['org']
        address = request.form['address']
        city = request.form['city']
        state = request.form['state']
        country = request.form['country']
        postcode = request.form['postcode']
        id_ = session['id']
        cursor = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
        
        sql = f"UPDATE accounts SET password='{password}', org='{org}', address='{address}', city='{city}', state='{state}', country='{country}', postcode='{postcode}' WHERE id='{id_}'"
        cursor.execute(sql)
        mysql.connection.commit()

        msg = 'Your details have been updated.'
        return render_template('update.html', msg=msg)
    
    return render_template('update.html', msg=msg)

@app.route('/index')
def index():
    if 'loggedin' in session:
        msg = 'Hello, ' + session['username']
        return render_template('index.html', msg=msg)
    return render_template('login.html')


@app.route('/display')
def display():
    if 'loggedin' in session:
        cursor = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
        sql = f"SELECT * FROM accounts WHERE id = '{session['id']}'"
        cursor.execute(sql)
        account = cursor.fetchone()
        return render_template('display.html', account=account)
    return redirect(url_for('login'))
    

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


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