DEV Community

Cover image for Flask Blog Tutorial #2: User Registration and Interacting with MySQL
GeekTech
GeekTech

Posted on • Originally published at geektech.codes

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 
Enter fullscreen mode Exit fullscreen mode

-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;
Enter fullscreen mode Exit fullscreen mode

showdb.png

Next, create your database for your flask blog app:

CREATE DATABASE name_of_db; 
Enter fullscreen mode Exit fullscreen mode

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

SHOW DATABASES; 
Enter fullscreen mode Exit fullscreen mode

To create tables in your db:

USE name_of_db; 
Enter fullscreen mode Exit fullscreen mode

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); 
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

usedb.png

To confirm, write this command to show created tables

SHOW TABLES; then DESCRIBE users;
Enter fullscreen mode Exit fullscreen mode

You should see this:

describedb.png

Flask-MySQL DB

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

To install:

pip install flask-mysqldb 
Enter fullscreen mode Exit fullscreen mode

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

To install:

pip install Flask-WTF. 
Enter fullscreen mode Exit fullscreen mode

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

To install:

pip install passlib. 

Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

From wtform import Form, StringField, TextAreaField, PasswordField, Validators
Enter fullscreen mode Exit fullscreen mode

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

From passlib.hash import sha256 crypt
Enter fullscreen mode Exit fullscreen mode

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')
Enter fullscreen mode Exit fullscreen mode

Next, let’s create a route for registration.

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

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)
Enter fullscreen mode Exit fullscreen mode

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 %}
Enter fullscreen mode Exit fullscreen mode

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 %}
Enter fullscreen mode Exit fullscreen mode

register_form.png

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
Enter fullscreen mode Exit fullscreen mode

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'
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
) 
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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')
Enter fullscreen mode Exit fullscreen mode

success is the category of this flash message.

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

redirect(url_for('index'))
Enter fullscreen mode Exit fullscreen mode

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 %}
Enter fullscreen mode Exit fullscreen mode

Then, include it in layout.html

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

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>
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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.

Discussion (0)