Flask Blog Tutorial #2: User Registration and Interacting with MySQL

Howdy Geeks! 👋

I am back again.

In this article, we will create a database to save user information for registration and create forms for registration. We will use MySQL so you can use either APACHE or if you use Windows, you could download MySQL and use it.

Tutorial to download APACHE: https://youtu.be/oJnCEqeAsUk

Tutorial to download MySQL: https://www.youtube.com/watch?v=H0ZpCVhS6hw

Interacting with MySQL database

After installing MySQL, open cmd and run this code to access MySQL and create databases and tables.

mysql -u root -p 

-u is for username,

-p is for the password, and

root is the main admin so we are accessing MySQL DB as a root user.

To create a database:

After successfully signing in, enter this command to show the created databases under your user.

 SHOW DATABASES;

Next, create your database for your flask blog app:

CREATE DATABASE name_of_db; 

This will create a DB, you can verify it by doing,

SHOW DATABASES; 

To create tables in your db:

USE name_of_db; 

Next, we create tables:

CREATE TABLE users(id INT(11) AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100),email VARCHAR(100), username VARCHAR(30), password VARCHAR(100), registered_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP); 

This creates an ID table that has a primary key, name table of 100 characters,*** email*** table of 100 characters, username table of 30 characters, password table ( which will be hashed) of 100 characters and table for a date the user registers that automatically use the current date as the value.

You should see a

QUERY OK, 0 rows affected, 1 warning (2.34 sec)

To confirm, write this command to show created tables

SHOW TABLES; then DESCRIBE users;

You should see this:

Flask-MySQL DB

Next, we install flask-mysql to interact with our created tables on MySQL.

To install:

pip install flask-mysqldb 

In addition, we need to validate our login and register form so let’s install** wtform**.

To install:

pip install Flask-WTF. 

Since we are saving users’ passwords we need to hash them. We can do so by using** passlib**.

To install:

pip install passlib. 

Register form

Let’s work on the registration form, but before that, import:

• Flash: For flash messages on the form.

• Redirect: For redirecting users after completing the form.

• url_for: For creating a URL that redirects users and prevents the overhead of having to change URLs in the application (especially in templates).

• session: For supporting Server-side application.

• logging: For functions and classes which implement a flexible event logging system for applications and libraries.

Do not forget to import our DB,

From flask mysqldb import MySQL

Next up, import wtform and the fields we will be using.

From wtform import Form, StringField, TextAreaField, PasswordField, Validators

Next, let’s import passlib to hash our saved password

From passlib.hash import sha256 crypt

Creating a Registration form

We want to create our form with wtforms. To do this, we need to create class for each form.

class RegisterForm(Form):
    name = StringField('Name', [validators.Length(min=5, max=40)])
    username = StringField('Username', [validators.Length(min=7, max=30)])
    email = StringField('Email', [validators.Length(min=7, max=35)])
    password = PasswordField('Password', [
        validators.DataRequired(),
        validators.EqualTo('confirm', message='Password does not match')
    ])
    confirm = PasswordField('Confirm Password')

Next, let’s create a route for registration.

@app.route('/register', ['GET', 'POST'])
def register():
    form = RegisterForm(request.form)

By default, all route has a GET method but we need to define a POST method. Do not forget to import requests.

Let’s create a register template and call it

@app.route('/register', methods=['GET', 'POST'])
def register():
    form = RegisterForm(request.form)
    return render_template('register.html', form=form)

Add random text on your HTML file and go to http://127.0.0.1:5000/register
You should see it display.

Let’s create our form:

{% 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 %}

Now that is done, we need to write form helpers to beautify and authenticate our created form.

Create a new file under includes folder. Name the file, _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 %}

Now that we are done we that, we want to handle our submission. Let’s set up MySQL first.

First, import flask mysql.

from data import Articles
from flask_mysqldb import MySQL

Then connect it to the app

# Config MySQL DB
app.config ['MYSQL_HOST'] = 'localhost'
app.config ["MYSQL_USER"] = 'root'
app.config ["MYSQL_PASSWORD"] = '1234567'
app.config ["MYSQL_DB"] = 'MyFlaskBlogDB'
app.config ["MYSQL_CURSORCLASS"] = 'DictCursor'

MYSQL_HOST defines the host of the database, which is our localhost
MYSQL_USER is the user that authenticates the database
MYSQL_PASSWORD is the password for the database
MYSQL_DB is the database in use.
MYSQL_CURSORCLASS: In this case, we will set it to return information in a dictionary by default.

Next, we initialize our database

mysql = MySQL(app)

Next, let’s save information if it’s a POST request.

@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.hash(str(form.password.data))     
    return render_template('register.html', form=form)

sha256_crypt.hash: ensures that the password sent to the database is encrypted. The above code stores the information in a variable and saves it.

Let’s create a cursor that can create queries or commands.

    password = sha256_crypt.encrypt(str(form.password.data))  

        # Creates cursor
        cur = mysql.connection.cursor() 

        cur.execute("INSERT INTO users(name, email, username, password) VALUES(%s, %s, %s, %s)", (name, email, username, password)
) 

The above code creates a connection to MySQL and saves the information to the tables in the database, but it won’t do anything unless we commit.

...
# commit to db 
Mysql.connection.commit()

#close connection
cur.close

In addition, we want to send a flash message after the user registers. With flash messaging, we give a message and also categorize it.

flash('You are now registered and may login. Welcome to BlogIt!', 'success')

success is the category of this flash message.

Next, we want to redirect after a successful logging in.

redirect(url_for('index'))

When we redirect, we use the url_for to define where we wish users to see next.

For the flash messaging, we need where we can include it in our template so create a file under includes and call it** _messages.html**.

Add this code to newly created file:

{% 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 %}

Then, include it in layout.html

 {% include 'includes/_navbar.html' %}
    <div class="container">
    { include 'includes/_messages.html' %}
        {% block body%}{% endblock%}
    </div>

Let’s add a register button on our navbar. Go to* _navbar.html* add this code below the ul in the navbar.

        <ul class="nav navbar-nav navbar-right">
          <li><a href="/register">Register</a></li>
        </ul>

Restart the server and you would see a register button on the right side of the navbar.
Before we test the form, let’s set a secret key. Add it to the bottom of your app.py file.
app.secret_key = 'Secret145'.

Register a user and you would see it in your database. To check in the DB,

SELECT * FROM users;

Redirecting

After registering, we want users to be redirected to the login page. We will discuss more in this on the next article series.

Thank you for reading!

Follow us on Twitter for more tech blogs.

Until next time,
Sophia Iroegbu.