install psycopg2 https://pypi.org/project/psycopg2/
Psycopg is the most popular PostgreSQL database adapter for the Python programming language.
(venv) PS C:\flaskmyproject> pip install psycopg2
Create TABLE
CREATE TABLE users (
id serial PRIMARY KEY,
fullname VARCHAR ( 100 ) NOT NULL,
username VARCHAR ( 50 ) NOT NULL,
password VARCHAR ( 255 ) NOT NULL,
email VARCHAR ( 50 ) NOT NULL
);
PostgreSQL SERIAL To Create Auto-increment Column
CREATE TABLE users (
id serial PRIMARY KEY
);
SMALLSERIAL = 2 bytes = 1 to 32,767
SERIAL = 4 bytes = 1 to 2,147,483,647
BIGSERIAL = 8 bytes = 1 to 9,223,372,036,854,775,807
#app.py from flask import Flask, request, session, redirect, url_for, render_template, flash import psycopg2 #pip install psycopg2 import psycopg2.extras import re from werkzeug.security import generate_password_hash, check_password_hash app = Flask(__name__) app.secret_key = 'cairocoders-ednalan' DB_HOST = "localhost" DB_NAME = "sampledb" DB_USER = "postgres" DB_PASS = "admin" conn = psycopg2.connect(dbname=DB_NAME, user=DB_USER, password=DB_PASS, host=DB_HOST) @app.route('/') def home(): # Check if user is loggedin if 'loggedin' in session: # User is loggedin show them the home page return render_template('home.html', username=session['username']) # User is not loggedin redirect to login page return redirect(url_for('login')) @app.route('/login/', methods=['GET', 'POST']) def login(): cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor) # Check if "username" and "password" POST requests exist (user submitted form) if request.method == 'POST' and 'username' in request.form and 'password' in request.form: username = request.form['username'] password = request.form['password'] print(password) # Check if account exists using MySQL cursor.execute('SELECT * FROM users WHERE username = %s', (username,)) # Fetch one record and return result account = cursor.fetchone() if account: password_rs = account['password'] print(password_rs) # If account exists in users table in out database if check_password_hash(password_rs, password): # Create session data, we can access this data in other routes session['loggedin'] = True session['id'] = account['id'] session['username'] = account['username'] # Redirect to home page return redirect(url_for('home')) else: # Account doesnt exist or username/password incorrect flash('Incorrect username/password') else: # Account doesnt exist or username/password incorrect flash('Incorrect username/password') return render_template('login.html') @app.route('/register', methods=['GET', 'POST']) def register(): cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor) # Check if "username", "password" and "email" POST requests exist (user submitted form) if request.method == 'POST' and 'username' in request.form and 'password' in request.form and 'email' in request.form: # Create variables for easy access fullname = request.form['fullname'] username = request.form['username'] password = request.form['password'] email = request.form['email'] _hashed_password = generate_password_hash(password) #Check if account exists using MySQL cursor.execute('SELECT * FROM users WHERE username = %s', (username,)) account = cursor.fetchone() print(account) # If account exists show error and validation checks if account: flash('Account already exists!') elif not re.match(r'[^@]+@[^@]+\.[^@]+', email): flash('Invalid email address!') elif not re.match(r'[A-Za-z0-9]+', username): flash('Username must contain only characters and numbers!') elif not username or not password or not email: flash('Please fill out the form!') else: # Account doesnt exists and the form data is valid, now insert new account into users table cursor.execute("INSERT INTO users (fullname, username, password, email) VALUES (%s,%s,%s,%s)", (fullname, username, _hashed_password, email)) conn.commit() flash('You have successfully registered!') elif request.method == 'POST': # Form is empty... (no POST data) flash('Please fill out the form!') # Show registration form with message (if any) return render_template('register.html') @app.route('/logout') def logout(): # Remove session data, this will log the user out session.pop('loggedin', None) session.pop('id', None) session.pop('username', None) # Redirect to login page return redirect(url_for('login')) @app.route('/profile') def profile(): cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor) # Check if user is loggedin if 'loggedin' in session: cursor.execute('SELECT * FROM users WHERE id = %s', [session['id']]) account = cursor.fetchone() # Show the profile page with account info return render_template('profile.html', account=account) # User is not loggedin redirect to login page return redirect(url_for('login')) if __name__ == "__main__": app.run(debug=True)templates/login.html
//templates/login.html <!DOCTYPE html> <html> <head> <meta charset="utf-8"> <title>Login</title> <link rel="stylesheet" href="{{ url_for('static', filename='css/style.css') }}"> <link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.7.1/css/all.css"> <link href="https://maxcdn.bootstrapcdn.com/bootstrap/4.1.1/css/bootstrap.min.css" rel="stylesheet" id="bootstrap-css"> <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.1.1/js/bootstrap.min.js"></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script> </head> <body style="background-color:#ccc;"> <div class="login-box" > <div class="login-header">Login</div> <div class="login-body"> <p>Please enter your username and password</p> <form class="form-group" action="{{ url_for('login') }}" method="post"> <label>Username</label> <input type="text" class="form-control" name="username" placeholder="Username" id="username" required> <label>Password</label> <input type="password" class="form-control" name="password" placeholder="Password" id="password" required> <input type="checkbox" value="checked"><b>Remember</b> <input type="submit" value="Login" class="form-control btn btn-success " name=""> </form> {% with messages = get_flashed_messages() %} {% if messages %} {% for message in messages %} <div class="alert alert-success alert-dismissible fade show" role="alert"> {{ message }} <button type="button" class="close" data-dismiss="alert" aria-label="Close"> <span aria-hidden="true">×</span> </button> </div> {% endfor %} {% endif %} {% endwith %} <p>No account <a href="{{ url_for('register') }}">Register</a></p> </div> </div> </body> </html>templates/register.html
//templates/register.html <!DOCTYPE html> <html> <head> <meta charset="utf-8"> <title>Register</title> <link rel="stylesheet" href="{{ url_for('static', filename='css/style.css') }}"> <link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.7.1/css/all.css"> <!-- Google Fonts --> <link href='https://fonts.googleapis.com/css?family=Passion+One' rel='stylesheet' type='text/css'> <link href='https://fonts.googleapis.com/css?family=Oxygen' rel='stylesheet' type='text/css'> <link href="https://maxcdn.bootstrapcdn.com/bootstrap/4.1.1/css/bootstrap.min.css" rel="stylesheet" id="bootstrap-css"> </head> <body style="background-color:#ccc;"> <div class="container"> <div class="row main"> <div class="main-login main-center"> <h5>Register New user or login existing account</h5> <form action="{{ url_for('register') }}" method="post" autocomplete="off"> <div class="form-group"> <label for="name" class="cols-sm-2 control-label">Your Name</label> <div class="cols-sm-10"> <div class="input-group"> <span class="input-group-addon"><i class="fa fa-user fa" aria-hidden="true"></i></span> <input type="text" class="form-control" name="fullname" id="fullname" placeholder="Enter your Name"/> </div> </div> </div> <div class="form-group"> <label for="email" class="cols-sm-2 control-label">Your Email</label> <div class="cols-sm-10"> <div class="input-group"> <span class="input-group-addon"><i class="fa fa-envelope fa" aria-hidden="true"></i></span> <input type="text" class="form-control" name="email" id="email" placeholder="Enter your Email"/> </div> </div> </div> <div class="form-group"> <label for="username" class="cols-sm-2 control-label">Username</label> <div class="cols-sm-10"> <div class="input-group"> <span class="input-group-addon"><i class="fa fa-users fa" aria-hidden="true"></i></span> <input type="text" class="form-control" name="username" id="username" placeholder="Enter your Username" required> </div> </div> </div> <div class="form-group"> <label for="password" class="cols-sm-2 control-label">Password</label> <div class="cols-sm-10"> <div class="input-group"> <span class="input-group-addon"><i class="fa fa-lock fa-lg" aria-hidden="true"></i></span> <input type="password" class="form-control" name="password" placeholder="Password" id="password" required placeholder="Enter your Password"/> </div> </div> </div> <div class="form-group "> {% with messages = get_flashed_messages() %} {% if messages %} {% for message in messages %} <div class="alert alert-success alert-dismissible fade show" role="alert"> {{ message }} <button type="button" class="close" data-dismiss="alert" aria-label="Close"> <span aria-hidden="true">×</span> </button> </div> {% endfor %} {% endif %} {% endwith %} <input type="submit" value="Register" class="form-control btn btn-primary " name=""> <p style="padding:5px;"> <a href="{{ url_for('login') }}" class="btn btn-dark ">Login</a></p> </div> </form> </div> </div> </div> <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.1.1/js/bootstrap.min.js"></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script> </body> </html>templates/layout.html
//templates/layout.html <!DOCTYPE html> <html> <head> <meta charset="utf-8"> <title>{% block title %}{% endblock %}</title> <link rel="stylesheet" href="{{ url_for('static', filename='css/style.css') }}"> <link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.7.1/css/all.css"> <link href="https://maxcdn.bootstrapcdn.com/bootstrap/4.1.1/css/bootstrap.min.css" rel="stylesheet" id="bootstrap-css"> <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.1.1/js/bootstrap.min.js"></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script> </head> <body class="loggedin"> <nav class="site-header sticky-top py-1"> <div class="container d-flex flex-column flex-md-row justify-content-between"> <a class="py-2 d-none d-md-inline-block" href="{{ url_for('home') }}"><i class="fas fa-home"></i> Home</a> <a class="py-2 d-none d-md-inline-block" href="{{ url_for('profile') }}"><i class="fas fa-user-circle"></i> Profile</a> <a class="py-2 d-none d-md-inline-block" href="{{ url_for('logout') }}"><i class="fas fa-sign-out-alt"></i> Logout</a> </div> </nav> <div class="container-fluid"> <div class="row"> <nav class="col-md-2 d-none d-md-block bg-light sidebar"> <div class="sidebar-sticky"> <ul class="nav flex-column"> <li class="nav-item"> <a class="nav-link active" href="#"> <i class="fas fa-home"></i> Dashboard <span class="sr-only">(current)</span> </a> </li> <li class="nav-item"> <a class="nav-link" href="#"> <i class="fas fa-shopping-cart"></i> Orders </a> </li> <li class="nav-item"> <a class="nav-link" href="#"> <i class="fas fa-shopping-cart"></i> Products </a> </li> <li class="nav-item"> <a class="nav-link" href="#"> <i class="fas fa-users"></i> Customers </a> </li> <li class="nav-item"> <a class="nav-link" href="#"> <i class="fas fa-download"></i> Reports </a> </li> </ul> </div> </nav> <main role="main" class="col-md-9 ml-sm-auto col-lg-10 pt-3 px-4"> <div class="d-flex justify-content-between flex-wrap flex-md-nowrap align-items-center pb-2 mb-3 border-bottom"> <div class="col-12"> {% block content %}{% endblock %} </div> </div> </main> </div> </div> </body> </html>templates/home.html
//templates/home.html {% extends 'layout.html' %} {% block title %}Home{% endblock %} {% block content %} <h1 class="h2">Dashboard</h1> <p>Welcome back, {{ username }}!</p> {% endblock %}templates/profile.html
//templates/profile.html {% extends 'layout.html' %} {% block title %}Profile{% endblock %} {% block content %} <h1>Profile Page</h1> <div> <p>Your account details are below:</p> <table> <tr> <td>Name:</td> <td>{{ account['fullname'] }}</td> </tr> <tr> <td>Username:</td> <td>{{ account['username'] }}</td> </tr> <tr> <td>Email:</td> <td>{{ account['email'] }}</td> </tr> </table> </div> {% endblock %}static/css/style.css
//static/css/style.css body { background-color: #435165; margin: 0; } .login-box { height: 50%; width: 30%; border: 1px solid grey; margin-left: 35%; margin-top: 10%; position: relative; box-shadow: 21px 12px 24px 10px rgba(0, 0, 0, .5); background: #dadada; } .login-header { text-align: center; font-family: "vardhana",cursive; font-size: 35px; background: linear-gradient(to bottom, #00bfff 0%, #5b6d7c 100%); color:#fff; position: relative; box-shadow: 1px 3px 14px rgba(0, 0, 0, .5); } .login-body { padding: 20px; line-height: 2; } .msg { color: #721c24; background-color: #f8d7da; border-color: #f5c6cb; padding: .75rem 1.25rem; margin-bottom: 1rem; border: 1px solid transparent; border-radius: .25rem; } .main{ margin-top:50px; } h1.title { font-size: 50px; font-family: 'Passion One', cursive; font-weight: 400; } hr{ width: 10%; color: #fff; } .form-group{ margin-bottom: 15px; } label{ margin-bottom: 15px; } input, input::-webkit-input-placeholder { font-size: 11px; padding-top: 3px; } .main-login{ background-color: #fff; /* shadows and rounded borders */ -moz-border-radius: 2px; -webkit-border-radius: 2px; border-radius: 2px; -moz-box-shadow: 0px 2px 2px rgba(0, 0, 0, 0.3); -webkit-box-shadow: 0px 2px 2px rgba(0, 0, 0, 0.3); box-shadow: 0px 2px 2px rgba(0, 0, 0, 0.3); } .form-control { height: auto!important; padding: 8px 12px !important; } .input-group { -webkit-box-shadow: 0px 2px 5px 0px rgba(0,0,0,0.21)!important; -moz-box-shadow: 0px 2px 5px 0px rgba(0,0,0,0.21)!important; box-shadow: 0px 2px 5px 0px rgba(0,0,0,0.21)!important; } .main-center{ margin-top: 30px; margin: 0 auto; max-width: 400px; padding: 10px 40px; background:#5b6d7c; color: #FFF; text-shadow: none; -webkit-box-shadow: 0px 3px 5px 0px rgba(0,0,0,0.31); -moz-box-shadow: 0px 3px 5px 0px rgba(0,0,0,0.31); box-shadow: 0px 3px 5px 0px rgba(0,0,0,0.31); } span.input-group-addon i { color: #fff; font-size: 17px;padding:10px; } .login-button{ margin-top: 5px; } .login-register{ font-size: 11px; text-align: center; } .site-header { background-color: rgba(0, 0, 0, .85); -webkit-backdrop-filter: saturate(180%) blur(20px); backdrop-filter: saturate(180%) blur(20px); } .site-header a { color:#fff; }