User registration in Flask - backend, part 1 of 2

Posted on 2020-06-01T12:33:45Z by Kevin Foong

In this post we will go through how to create a fully functional login and registration form in Flask. Part 1 (this post) will go through the backend part - setting up Flask, Flask-SQLAlchemy and creating the database. In part 2 we will build the actual forms and templates and see this in action.

In this tutorial we will start from the beginning, assuming that you only have Python installed. This tutorial was created using Windows but should be similar for other operating systems.

Initial setup

1. Create a folder for your project. Then in this folder create a Python virtual environment.

python -m venv env

2. Activate your virtual environment.

env\scripts\activate

3. Install the following packages. 

1. flask - The Flask application
2. python-dotenv - Enables you to read environment variables from a .env file
3. flask-sqlalchemy - ORM (Object Relational Mapper) which enables you to access your database using SQL like Python statements within Flask. 
4. flask-migrate - Enables you to easily make changes to your SQLalchemy database on the fly, based on Alembic.
5. PyMySQL - A Python MySQL library which enables you to connect to your MySQL database.

pip install flask
pip install python-dotenv
pip install flask-sqlalchemy
pip install flask-migrate
pip install pymysql

4. We will be structuring the Flask app based on a package which suits small to medium sized websites. For larger websites you may want to look at blueprints. In your root project folder create the following files.

.env - This is where you add any system and secret keys. You do NOT share this file on Github.

DATABASE_URL contains the connection string to your MySQL database. We have referenced "sqltut" which is the name of our database we will be creating later.
SECRET_KEY is used by things like session, csrf tokens in forms and anything that requires encryption. We will only need this when we look at WTForms in part 2.

FLASK_APP=app.py
# For development it should be FLASK_ENV=development
# For production it should be FLASK_ENV=production
FLASK_ENV=development

SECRET_KEY=SuperSecr3t123
DATABASE_URL=mysql+pymysql://username:password@localhost:3306/sqltut

config.py - This is our config file.

This file is used to read in our environment variables from our .env file.

import os
from dotenv import load_dotenv
from pathlib import Path

# handy to have - makes referencing files on local system easier
basedir = Path(__file__).parent
load_dotenv(basedir / '.env')

class Config(object):
    SECRET_KEY = os.environ.get('SECRET_KEY')
    SQLALCHEMY_DATABASE_URI = os.environ.get('DATABASE_URL')
    SQLALCHEMY_TRACK_MODIFICATIONS=False

app.py - This imports your app package which we willl create in step 5.

from app import app

5. Create a sub-folder called "app" from your project root. This is your Python package. Within this folder create the following files.

__init__.py - Ths is the initialisation file for a Python package which is implicitly executed when the package is called.

This is where we create the Flask instance and initialise all our packages.

from flask import Flask
from config import Config
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate

app = Flask(__name__)

app.config.from_object(Config)
db = SQLAlchemy(app)
migrate = Migrate(app, db, compare_type=True)

from . import models

Models

With SQLalchemy you define your database using Python. You do this by creating a class which inherits db.Model

In the app folder, create a new file models.py

There is quite alot happening in this file so we will try and break it down.

User table

• __tablename__ is the actual name we will give to the database table.
• id is the primary key of the table. This is an integer that is generated by the database and auto-increments.
• email - for this column we have included index=True. Generally you use indexing on a column if it is frequently used in WHERE clauses or sorting (using ORDER BY), and also if the data in the column doesn't keep changing. For example we may have a table of thousands of users and want the ability to search for all users sorted by their email.
• password_hash - we don't store the actual password in our table but a password hash. A password hash may look something like "pbkdf2:sha256:150000$8J7bUe2X$235991ceeea9650944a782db83fe940fd411b1fcf5f5b7026d00fa317594d029"
  We use werkzeug.security to generate the hash as that already comes with Flask. Note that if someone obtains the hash there is no known way to be able to reverse engineer it to obtain the plain text password.
• role_id - the User table has a one to many relationship with the Roles table. That means one user can have one role, but one role can have many users. We represent this in our  models by defining a foreign key on the one side (in this case User) which links to the many side (in this case Role). We have also set default=2 which means when inserting a user, if we don't specify which role the user belongs to, it will always be assigned to role id = 2 which is going to be a normal user. Later we will need to populate the roles table using SQL to ensure that a normal user role has id = 2.
• update_date and create_date - these are timestamps needed to keep track of when the record was created and when it was updated. Generally it is a good idea to have both update_date and create_date. create_date will be the date the record is created and will not change. update_date is updated every time you update the record such as when the user makes a change to their profile (such as when they change their password).
  default=datetime.utcnow is useful because we don't have to explicitly specify the date every time we update the record in the database. This will automatically assign the current date/time to the field.
• We also add UserMixin to the table which is provided by Flask Login. This is covered in part 2 of the tutorial. The UserMixin class allows us to do things like check if the current user has logged in (is_authenticated).

We also provide two methods to set and check passwords.

The last method def __repr__(self) is just a printable representation of the object. To see an example go to flask shell (type in flask shell in your command prompt) and enter the below. You will see <User me@abc.com> as the output, as we specified only the user's email for output.

>>> from app import db
>>> from app.models import User
>>> u = User(email='me@abc.com',password_hash='just testing')
>>> u
<User me@abc.com>

Role table

The fields in this table are similar to the fields in the User table. The only field we haven't encountered yet is "users".

• users - this is not an actual field in the table, but it is specified in our model so that we can use Python to easily access the child table. In this case User is the parent table and Role is the child table. So if we had a user object called "user" we can access the user's role by using user.role. We defined "role" in backref='role'. 
  
  The lazy=dynamic part allows you to add a separate query after the returned results giving you more flexibility. For example we can add .first() or .all() to the end of our statement. This will become clearer when we go through some examples later.

The full models.py is shown below.

from flask import current_app
from flask_login import UserMixin
from datetime import datetime, timedelta
from werkzeug.security import generate_password_hash, check_password_hash
from . import db


class User(db.Model, UserMixin):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(50), index=True, unique=True, nullable=False)
    password_hash = db.Column(db.String(100))
    role_id = db.Column(db.Integer, db.ForeignKey('role.id'), default=2, nullable=False)
    update_date = db.Column(db.DateTime, default=datetime.utcnow, nullable=False)
    create_date = db.Column(db.DateTime, default=datetime.utcnow, nullable=False)

    def set_password(self, password):
        self.password_hash = generate_password_hash(password)

    def check_password(self, password):
        return check_password_hash(self.password_hash, password)

    def __repr__(self):
        return '<User {}>'.format(self.email)


class Role(db.Model):
    __tablename__ = 'role'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), unique=True, nullable=False)
    users = db.relationship('User', backref='role', lazy='dynamic')

    def __repr__(self):
        return '<Role {}>'.format(self.name)

At this stage your folder structure should look like this.

MySQL and Flask-Migrate

I assume you have MySQL already installed on your computer. You might also want to install MySQL Workbench for a graphical UI way of working.

First create a new empty database (schema) in MySQL.

CREATE SCHEMA `sqltut` ; 

Now we use Flask-Migrate to create our tables. flask db init creates a "migration" folder and initialises our migration. flask db migrate -m "first" detects any changes that need to be made and commits those changes. flask db upgrade will make these changes on your database. Back on your command line, run the below commands.

flask db init
flask db migrate -m "first"
flask db upgrade

If you get an error like "ERROR [root] Error: cryptography is required for sha256_password or caching_sha2_password" after you run flask db migrate, first check your MySQL username and password. If you google around there are quite alot of more complicated suggestions, for example on Stack Overflow, which may not be needed.

Your database tables and columns should now be created and should look like this.

Now that your tables are created, populate the Role table with user roles. In this example, a user can either be a normal "user" or "admin". Run the below SQL command in MySQL to populate the table.

INSERT INTO role (name)
VALUES ('admin'), ('user');

The Role table is now pop