Flask and MySQL (Database Modelling)

Flask — 27 December 2021 · Comments off

We use Flask-SQLAlchemy is an extension for Flask that adds support for SQLAlchemy to your application

Installation

$ pip install -U Flask-SQLAlchemy

$ pip install mysqlclient

Setup Connect To MySQL Database URI

Organizing project files:

project/app.py      # run website
project/config.py   # config env database

projects/flask_demo/__init__.py # init app
projects/flask_demo/models.py  # models tables database
projects/flask_demo/views.py  # views website
projects/flask_demo/templates/show_all.html # html template list user pages
projects/flask_demo/templates/add_user.html # html template add new user pages

You need fill information Mysql: username, password, host, db_name

# config.py

class Config:
    # General Config
    SECRET_KEY = 'random string'
    FLASK_APP = 'demo'
    FLASK_ENV = 'development'
    
    # Database
    SQLALCHEMY_DATABASE_URI = 'mysql://username:password@localhost/db_name'
    SQLALCHEMY_ECHO = False
    SQLALCHEMY_TRACK_MODIFICATIONS = False

# app.py

from flask_demo import init_app

app = init_app()

if __name__ == "__main__":
    app.run(host='0.0.0.0')


# flask_demo/__init__.py

from flask import Flask
from flask_sqlalchemy import SQLAlchemy


app = Flask(__name__)
db = SQLAlchemy()

def init_app():
    app.config.from_object('config.Config')
    db.init_app(app)
    with app.app_context():
        from . import models
        db.create_all()
        from . import views
        return app


# flask_demo/models.py

from . import db

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True)
    email = db.Column(db.String(120), unique=True)
    fullname = db.Column(db.String(200))

    def __init__(self, username, email, fullname):
        self.username = username
        self.email = email
        self.fullname = fullname

    def __repr__(self):
        return '< User %r >' % self.username


# flask_demo/views.py

from flask import Flask, render_template, url_for, request, flash, redirect
from flask import current_app as app
from .models import db, User

@app.route('/')
def show_all():
   return render_template('show_all.html', users = User.query.all())

@app.route('/new', methods = ['GET', 'POST'])
def new():
   if request.method == 'POST':
      if not request.form['fullname'] or not request.form['username'] or not request.form['email']:
         flash('Please enter all the fields', 'error')
      else:
         user = User.query.filter((User.username==request.form['username']) | (User.email==request.form['email'])).first()
         if user:
             flash('User already exists!', 'error')
         else:
             user = User(request.form['username'], request.form['email'],
                request.form['fullname'])

             db.session.add(user)
             db.session.commit()

             flash('Record was successfully added')
             return redirect(url_for('show_all'))

   return render_template('add_user.html')

  • Home Page User

The HTML script for the template (‘show_all.html‘) is as follows

<!DOCTYPE html>
<html lang = "en">
   <head>
   <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-1BmE4kWBq78iYhFldvKuhfTAU6auU8tT94WrHftjDbrCEXSU1oBoqyl2QvZ6jIW3" crossorigin="anonymous">
   </head>
   <body>
    <main>
      <div class="container">
        <header class="d-flex flex-wrap justify-content-center py-3 mb-4 border-bottom">
          <a href="/" class="d-flex align-items-center mb-3 mb-md-0 me-md-auto text-dark text-decoration-none">
          <span class="fs-4">SQLAlchemy Example</span>
          </a>
        </header>
          
       <a href="/new" class="btn btn-primary">Create User</a>
    
       <table class="table table-striped">
       <thead>
            <tr>
               <th>Username</th>
               <th>Email</th>
               <th>FullName</th>
            </tr>
         </thead>
         
         <tbody>
            {% for user in users %}
               <tr>
                  <td>{{ user.username }}</td>
                  <td>{{ user.email }}</td>
                  <td>{{ user.fullname }}</td>
               </tr>
            {% endfor %}
         </tbody>
      </table>
      </div>
   </main>
   <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js" integrity="sha384-ka7Sk0Gln4gmtz2MlQnikT1wXgYsOg+OMhuP+IlRH9sENBO0LRn5q+8nbTov4+1p" crossorigin="anonymous"></script>
   </body>
</html>

  • Add new User

The HTML script for the template (‘add_user.html ‘) is as follows

<!DOCTYPE html>
<html lang = "en">
   <head>
   <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-1BmE4kWBq78iYhFldvKuhfTAU6auU8tT94WrHftjDbrCEXSU1oBoqyl2QvZ6jIW3" crossorigin="anonymous">
   </head>
   <body>
    <main>
      <div class="container">
        <header class="d-flex flex-wrap justify-content-center py-3 mb-4 border-bottom">
          <a href="/" class="d-flex align-items-center mb-3 mb-md-0 me-md-auto text-dark text-decoration-none">
          <span class="fs-4">Add New User - SQLAlchemy Example</span>
          </a>
        </header>
          
       <div class="row">
        
           
           <form action = "{{ request.path }}" method = "post">
              <div class="mb-3">
               {%- for message in get_flashed_messages() %}
                 <p class="alert alert-danger">
                     {{ message }}
                  </p>
               {%- endfor %}
              </div>
               
              <div class="mb-3">
                <label for="fullname" class="form-label">Full Name</label>
                <input type="text" class="form-control" placeholder="Full Name" name="fullname" >
              </div>
              <div class="mb-3">
                <label for="username" class="form-label">Username</label>
                <input type="text" class="form-control" placeholder="Username" name="username" >
              </div>
               
              <div class="mb-3">
                <label for="email" class="form-label">Email</label>
                <input type="text" class="form-control" placeholder="Email" name="email" >
              </div>
              <button type="submit" class="btn btn-primary">Submit</button>
            </form>
          
       </div>
     </div>
   </main>
   <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js" integrity="sha384-ka7Sk0Gln4gmtz2MlQnikT1wXgYsOg+OMhuP+IlRH9sENBO0LRn5q+8nbTov4+1p" crossorigin="anonymous"></script>
   </body>
</html>

Run website Flask

Ubuntu/ MacOS

$ export FLASK_ENV=development
$ export FLASK_APP=app
$ flask run
  * Running on http://127.0.0.1:5000/

Windows

> FLASK_ENV=development
> set FLASK_APP=app
> flask run
  * Running on http://127.0.0.1:5000/

Open Browser and go to http://127.0.0.1:5000/

List User
Add New User