Python Flask CRUD Simple application with authentication

Python Flask CRUD Simple application with authentication

Python Flask CRUD (Create, read, update and delete) Simple application with authentication and Mysql, bootstrap

Table `user_flask`

CREATE TABLE `user_flask` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(150) NOT NULL,
  `username` varchar(100) NOT NULL,
  `password` varchar(200) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `user_flask`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `user_flask`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
COMMIT;

Table structure for table `articles`

CREATE TABLE `articles` (
  `id` int(8) NOT NULL,
  `title` varchar(255) NOT NULL,
  `body` text NOT NULL,
  `create_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `author` varchar(200) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Indexes for table `articles`
--
ALTER TABLE `articles`
  ADD PRIMARY KEY (`id`),
  ADD KEY `date` (`create_date`);

ALTER TABLE `articles`
  MODIFY `id` int(8) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
COMMIT;

 
#app.py
from flask import Flask, render_template, flash, redirect, url_for, session, request, logging
from flaskext.mysql import MySQL
import pymysql
from wtforms import Form, StringField, TextAreaField, PasswordField, validators
from passlib.hash import sha256_crypt
from functools import wraps

app = Flask(__name__)
app.secret_key = "Cairocoders-Ednalan"
 
mysql = MySQL()
  
# MySQL configurations
app.config['MYSQL_DATABASE_USER'] = 'root'
app.config['MYSQL_DATABASE_PASSWORD'] = ''
app.config['MYSQL_DATABASE_DB'] = 'testingdb'
app.config['MYSQL_DATABASE_HOST'] = 'localhost'
mysql.init_app(app)
 
# Register Form Class
class RegisterForm(Form):
    name = StringField('Name', [validators.Length(min=1, max=50)])
    username = StringField('Username', [validators.Length(min=4, max=25)])
    email = StringField('Email', [validators.Length(min=6, max=50)])
    password = PasswordField('Password', [
        validators.DataRequired(),
        validators.EqualTo('confirm', message='Passwords do not match')
    ])
    confirm = PasswordField('Confirm Password')

# Article Form Class
class ArticleForm(Form):
    title = StringField('Title', [validators.Length(min=1, max=200)])
    body = TextAreaField('Body', [validators.Length(min=30)])
 
# Index
@app.route('/')
def index():
    return render_template('home.html')
 
# About
@app.route('/about')
def about():
    return render_template('about.html')

# Articles
@app.route('/articles')
def articles():
    # Create cursor
    conn = mysql.connect()
    cur = conn.cursor(pymysql.cursors.DictCursor)
 
    # Get articles
    result = cur.execute("SELECT * FROM articles")
    articles = cur.fetchall()
    if result > 0:
        return render_template('articles.html', articles=articles)
    else:
        msg = 'No Articles Found'
        return render_template('articles.html', msg=msg)
    # Close connection
    cur.close()

#Single Article
@app.route('/article//')
def article(id):
    # Create cursor
    conn = mysql.connect()
    cur = conn.cursor(pymysql.cursors.DictCursor)
 
    # Get article
    result = cur.execute("SELECT * FROM articles WHERE id = %s", [id])
    article = cur.fetchone()
    return render_template('article.html', article=article)
 
# User Register
@app.route('/register', methods=['GET', 'POST'])
def register():
    form = RegisterForm(request.form)
    if request.method == 'POST' and form.validate():
        name = form.name.data
        email = form.email.data
        username = form.username.data
        password = sha256_crypt.encrypt(str(form.password.data))
         
        conn = mysql.connect()
        cur = conn.cursor(pymysql.cursors.DictCursor)
        # Execute query
        cur.execute("INSERT INTO user_flask(name, email, username, password) VALUES(%s, %s, %s, %s)", (name, email, username, password))
        # Commit to DB
        conn.commit()
        # Close connection
        cur.close()
        flash('You are now registered and can log in', 'success')
        return redirect(url_for('login'))
    return render_template('register.html', form=form)

# User login
@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        # Get Form Fields
        username = request.form['username']
        password_candidate = request.form['password']
 
        conn = mysql.connect()
        cur = conn.cursor(pymysql.cursors.DictCursor)
 
        # Get user by username
        result = cur.execute("SELECT * FROM user_flask WHERE username = %s", [username])
 
        if result > 0:
            # Get stored hash
            data = cur.fetchone()
            password = data['password']
 
            # Compare Passwords
            if sha256_crypt.verify(password_candidate, password):
                # Passed
                session['logged_in'] = True
                session['username'] = username
 
                flash('You are now logged in', 'success')
                return redirect(url_for('dashboard'))
            else:
                error = 'Invalid login'
                return render_template('login.html', error=error)
            # Close connection
            cur.close()
        else:
            error = 'Username not found'
            return render_template('login.html', error=error)
 
    return render_template('login.html')

# Check if user logged in
def is_logged_in(f):
    @wraps(f)
    def wrap(*args, **kwargs):
        if 'logged_in' in session:
            return f(*args, **kwargs)
        else:
            flash('Unauthorized, Please login', 'danger')
            return redirect(url_for('login'))
    return wrap

# Logout
@app.route('/logout')
@is_logged_in
def logout():
    session.clear()
    flash('You are now logged out', 'success')
    return redirect(url_for('login'))
 
# Dashboard
@app.route('/dashboard')
@is_logged_in
def dashboard():
    # Create cursor
    conn = mysql.connect()
    cur = conn.cursor(pymysql.cursors.DictCursor)
 
    # Get articles
    #result = cur.execute("SELECT * FROM articles")
    # Show articles only from the user logged in 
    result = cur.execute("SELECT * FROM articles WHERE author = %s", [session['username']])
 
    articles = cur.fetchall()
 
    if result > 0:
        return render_template('dashboard.html', articles=articles)
    else:
        msg = 'No Articles Found'
        return render_template('dashboard.html', msg=msg)
    # Close connection
    cur.close()

# Add Article
@app.route('/add_article', methods=['GET', 'POST'])
@is_logged_in
def add_article():
    form = ArticleForm(request.form)
    if request.method == 'POST' and form.validate():
        title = form.title.data
        body = form.body.data
 
        # Create Cursor
        conn = mysql.connect()
        cur = conn.cursor(pymysql.cursors.DictCursor)
 
        # Execute
        cur.execute("INSERT INTO articles(title, body, author) VALUES(%s, %s, %s)",(title, body, session['username']))
        # Commit to DB
        conn.commit()
        #Close connection
        cur.close()
        flash('Article Created', 'success')
        return redirect(url_for('dashboard'))
    return render_template('add_article.html', form=form)

# Edit Article
@app.route('/edit_article/', methods=['GET', 'POST'])
@is_logged_in
def edit_article(id):
    # Create cursor
    conn = mysql.connect()
    cur = conn.cursor(pymysql.cursors.DictCursor)
 
    # Get article by id
    result = cur.execute("SELECT * FROM articles WHERE id = %s", [id])
    article = cur.fetchone()
    cur.close()
    # Get form
    form = ArticleForm(request.form)
    # Populate article form fields
    form.title.data = article['title']
    form.body.data = article['body']
 
    if request.method == 'POST' and form.validate():
        title = request.form['title']
        body = request.form['body']
        # Create Cursor
        cur = conn.cursor(pymysql.cursors.DictCursor)
        app.logger.info(title)
        # Execute
        cur.execute ("UPDATE articles SET title=%s, body=%s WHERE id=%s",(title, body, id))
        # Commit to DB
        conn.commit()
        #Close connection
        cur.close()
        flash('Article Updated', 'success')
        return redirect(url_for('dashboard'))
    return render_template('edit_article.html', form=form)
 
 
# Delete Article
@app.route('/delete_article/', methods=['POST'])
@is_logged_in
def delete_article(id):
    # Create cursor
    conn = mysql.connect()
    cur = conn.cursor(pymysql.cursors.DictCursor)
 
    # Execute
    cur.execute("DELETE FROM articles WHERE id = %s", [id])
    # Commit to DB
    conn.commit()
    #Close connection
    cur.close()
    flash('Article Deleted', 'success')
    return redirect(url_for('dashboard'))
 
if __name__ == '__main__':
 app.run(debug=True)

//home.html
{% extends 'layout.html' %}

{% block body %}
  <div class="jumbotron text-center">
    <h2>Python Flask CRUD (Create, read, update and delete) Simple application with authentication and Mysql</h2>
    <p class="lead">Python Flask CRUD (Create, read, update and delete) Simple application with authentication and Mysql</p>
    {% if session.logged_in == NULL %}
      <a href="/register" class="btn btn-primary btn-lg">Register</a>
      <a href="/login" class="btn btn-success btn-lg">Login</a>
    {% endif %}
  </div>
{% endblock %}

//layout.php
<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8">
    <title>Python Flask CRUD Simple application with authentication</title>
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" rel="stylesheet" id="bootstrap-css">
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script> 
<script src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.16/js/dataTables.bootstrap4.min.js"></script>
  </head>
  <body>
 {% include 'includes/_navbar.html' %}
 <div class="container">
      {% include 'includes/_messages.html' %}
      {% block body %}{% endblock %}
    </div>
<style>
.container {padding-top:50px;}
</style>
<script type="text/javascript">
$(document).ready(function() {
    $('#articles').DataTable({     
      "aLengthMenu": [[3, 5, 10, 25, -1], [3, 5, 10, 25, "All"]],
        "iDisplayLength": 3
       } 
    );
} );

CKEDITOR.replace('editor')
</script>
  </body>
</html>

//templates/includes/_navbar.html
<nav class="navbar navbar-expand-lg navbar-light bg-light">
  <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarTogglerDemo01" aria-controls="navbarTogglerDemo01" aria-expanded="false" aria-label="Toggle navigation">
    <span class="navbar-toggler-icon"></span>
  </button>
  <div class="collapse navbar-collapse" id="navbarTogglerDemo01">
    <a class="navbar-brand" href="#">Cairocoders</a>
    <ul class="navbar-nav mr-auto mt-2 mt-lg-0">
      <li class="nav-item active">
        <a class="nav-link" href="/">Home <span class="sr-only">(current)</span></a>
      </li>
      <li class="nav-item">
        <a class="nav-link" href="/about">About</a>
      </li>
      <li class="nav-item">
        <a class="nav-link" href="/articles">Articles</a>
      </li>
    </ul>
     <ul class="nav navbar-nav navbar-right">
            {% if session.logged_in %}
              <li class="nav-item"><a class="nav-link" href="/dashboard">Dashboard</a></li>
              <li class="nav-item"><a class="nav-link" href="/logout">Logout</a></li>
            {% else %}
              <li class="nav-item"><a class="nav-link" href="/register">Register</a></li>
              <li class="nav-item"><a class="nav-link" href="/login">Login</a></li>
            {% endif %}

          </ul>
  </div>
</nav>

//templates/includes/_messages.html
{% with messages = get_flashed_messages(with_categories=true) %}
  {% if messages %}
    {% for category, message in messages %}
      <div class="alert alert-{{ category }}">{{ message }}</div>
    {% endfor %}
  {% endif %}
{% endwith %}

{% if error %}
  <div class="alert alert-danger">{{error}}</div>
{% endif %}

{% if msg %}
  <div class="alert alert-success">{{msg}}</div>
{% endif %}

//templates/includes/_formhelpers.html
{% macro render_field(field) %}
  {{ field.label }}
  {{ field(**kwargs)|safe }}
  {% if field.errors %}
    {% for error in field.errors %}
      <span class="help-inline">{{ error }}</span>
    {% endfor %}
  {% endif %}
{% endmacro %}

//templates/about.html
{% extends 'layout.html' %}

{% block body %}
  <h1>About Us</h1>
  <p>Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.</p>
{% endblock %}

//templates/articles.html
{% extends 'layout.html' %}

{% block body %}
  <h1>Articles</h1>
  <ul class="list-group">
    {% for article in articles %}
      <li class="list-group-item"><a href="article/{{article.id}}">{{article.title}}</a></li>
    {% endfor %}
  </ul>
{% endblock %}

//templates/article.html
{% extends 'layout.html' %}

{% block body %}
  <h1>{{article.title}}</h1>
  <small>Written by {{article.author}} on {{article.create_date}}</small>
  <hr>
  <div>
    {{article.body | safe}}
  </div>
{% endblock %}

//templates/register.html
{% extends 'layout.html' %}

{% block body %}
  <h1>Register</h1>
  {% from "includes/_formhelpers.html" import render_field %}
  <form method="POST" action="">
    <div class="form-group">
      {{render_field(form.name, class_="form-control")}}
    </div>
    <div class="form-group">
      {{render_field(form.email, class_="form-control")}}
    </div>
    <div class="form-group">
      {{render_field(form.username, class_="form-control")}}
    </div>
    <div class="form-group">
      {{render_field(form.password, class_="form-control")}}
    </div>
    <div class="form-group">
      {{render_field(form.confirm, class_="form-control")}}
    </div>
    <p><input type="submit" class="btn btn-primary" value="Submit"></p>
  </form>
{% endblock %}

//templates/login.html
{% extends 'layout.html' %}

{% block body %}
  <h1>Login</h1>
  <form action="" method="POST">
    <div class="form-group">
      <label>Username</label>
      <input type="text" name="username" class="form-control" value={{request.form.username}}>
    </div>
    <div class="form-group">
      <label>Password</label>
      <input type="password" name="password" class="form-control" value={{request.form.password}}>
    </div>
    <button type="submit" class="btn btn-primary">Submit</button>
  </form>
{% endblock %}

//templates/dashboard.html
{% extends 'layout.html' %}

{% block body %}
  <h1>Dashboard <small> Welcome {{session.username}}</small></h1>
  <a class="btn btn-success" href="/add_article"> Add Article</a>
  <hr>
  <table class="table table-striped">
    <tr>
      <th>ID</th>
      <th>Title</th>
      <th>Author</th>
      <th>Date</th>
      <th></th>
      <th></th>
    </tr>
    {% for article in articles %}
      <tr>
        <td>{{article.id}}</td>
        <td>{{article.title}}</td>
        <td>{{article.author}}</td>
        <td>{{article.create_date}}</td>
        <td><a href="edit_article/{{article.id}}" class="btn btn-default pull-right">Edit</a></td>
        <td>
          <form action="{{url_for('delete_article', id=article.id)}}" method="post">
            <input type="hidden" name="_method" value="DELETE">
            <input type="submit" value="Delete" class="btn btn-danger">
          </form>
        </td>
      </tr>
    {% endfor %}
  </table>
{% endblock %}

//templates/add_article.html
{% extends 'layout.html' %}

{% block body %}
  <h1>Add Article</h1>
  {% from "includes/_formhelpers.html" import render_field %}
  <form method="POST" action="">
    <div class="form-group">
      {{ render_field(form.title, class_="form-control") }}
    </div>
    <div class="form-group">
      {{ render_field(form.body, class_="form-control", id="editor") }}
    </div>
    <p><input class="btn btn-primary" type="submit" value="Submit">
  </form>
{% endblock %}

//templates/edit_article.html
{% extends 'layout.html' %}

{% block body %}
  <h1>Edit Article</h1>
  {% from "includes/_formhelpers.html" import render_field %}
  <form method="POST" action="">
    <div class="form-group">
      {{ render_field(form.title, class_="form-control") }}
    </div>
    <div class="form-group">
      {{ render_field(form.body, class_="form-control", id="editor") }}
    </div>
    <p><input class="btn btn-primary" type="submit" value="Submit">
  </form>
{% endblock %}