Flask-Profile
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)