Flask Blog Tutorial #2: User Registration and Interacting with MySQL
Tags: flask, python, mysql, apach
Published: 2022-02-21 14:40:49

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;
``` 

![showdb.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1643684701839/CcNkRxfUw.png)

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)
```


![usedb.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1643684744072/2J-bCw5id.png)

To confirm, write this command to show created tables

```
SHOW TABLES; then DESCRIBE users;
```

You should see this: 

![describedb.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1643684546641/f2KokgSSr.png)

### 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 %}
```


![register_form.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1643684635280/JPrdK-0ZS.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
```

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](https://twitter.com/geektechpub) for more tech blogs.

Until next time,
Sophia Iroegbu.