HTML CSS JavaScript jQuery PHP Python MySQL

Login System with Python Flask and MySQL

Updated on by David Adams

Login System with Python Flask and MySQL

In this tutorial, we'll be creating a complete login and registration system with Python Flask and MySQL.

Python is currently taking the web development scene by storm, growing consistently every year. It's easy to get started with Python and doesn't require as many dependencies that you need to download separately.

The Basic and Advanced packages include additional features and a download link to the source code.

1. Getting Started

There are a few steps we need to take before we create our python login and registration system, we need to download and set up Python and the extensions we're going to use.

1.1. What You Will Learn in this Tutorial

  • Form Design — Design a login and registration form with HTML5 and CSS3.
  • Templates — Create Flask templates with HTML and Python.
  • Basic Validation — Validating form data that is sent to the server (username, password, and email).
  • Session Management — Initialize sessions and store retrieved database results.
  • MySQL Queries — Select and insert records from/in our database table.
  • Routes — Routing will allow us to point our URL's to our functions.

1.2. Requirements

  • Download and install Python, for this tutorial I'll be using Python 3.7.2, make sure to check the box Add Python to PATH on the installation setup screen.
  • Download and install MySQL Community Server and MySQL Workbench, you can skip this step if you already have a MySQL server set up.
  • Install Python Flask with the command: pip install flask
  • Install Flask-MySQLdb with the command: pip install flask-mysqldb

1.3. File Structure & Setup

We need to create our project folder and files, you can put the folder anywhere on your computer, as long as Python can access it, create the folders and files below.

File Structure

\-- pythonlogin
  |-- main.py
  \-- static
    |-- style.css
  \-- templates
    |-- index.html
    |-- register.html
    |-- home.html
    |-- profile.html
    |-- layout.html

Each file will contain the following:

  • main.py — This will be our main project file, all our Python code will be in this file (Routes, MySQL connection, validation, etc).
  • index.html — Login form created with HTML5 and CSS3.
  • register.html — Registration form created with HTML5 and CSS3.
  • home.html — The home template to display for logged-in users.
  • profile.html — The profile template to display for logged-in users.
  • layout.html — The layout template for the home and profile templates.
  • style.css — The stylesheet for our login and registration system.

The below instruction will start your web server (Windows):

  • Make sure your MySQL server is up and running, it should have automatically started if you installed it via the installer.
  • Open Command Prompt, make sure you have the project folder selected, you can do this with the command cd c:\your_project_folder_destination on Windows.
  • Run command: set FLASK_APP=main.py
  • Run command: set FLASK_DEBUG=1
  • Run command: flask run

Debug mode will allow us to edit our files without restarting the web server.

2. Creating the Database and setting-up Tables

MySQL Workbench is a GUI for creating and editing our databases, follow the below instructions.

  • Open MySQL Workbench
  • Fill out your MySQL details
  • Click Test Connection, if successful you can click OK
  • Open your connection
  • Execute the following SQL statements:
CREATE DATABASE IF NOT EXISTS `pythonlogin` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `pythonlogin`;

CREATE TABLE IF NOT EXISTS `accounts` (
	`id` int(11) NOT NULL AUTO_INCREMENT,
  	`username` varchar(50) NOT NULL,
  	`password` varchar(255) NOT NULL,
  	`email` varchar(100) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

INSERT INTO `accounts` (`id`, `username`, `password`, `email`) VALUES (1, 'test', 'test', 'test@test.com');

MySQL Workbench Execute Login System SQL

That will create our database pythonlogin with the table accounts, the SQL will also insert a test account, this account we'll use for testing purposes later in the tutorial, it will make sure everything is working as it should.

3. Creating the Stylesheet (CSS3)

Edit style.css and insert the following:

* {
  	box-sizing: border-box;
  	font-family: -apple-system, BlinkMacSystemFont, "segoe ui", roboto, oxygen, ubuntu, cantarell, "fira sans", "droid sans", "helvetica neue", Arial, sans-serif;
  	font-size: 16px;
  	-webkit-font-smoothing: antialiased;
  	-moz-osx-font-smoothing: grayscale;
}
body {
  	background-color: #435165;
  	margin: 0;
}
.login, .register {
  	width: 400px;
  	background-color: #ffffff;
  	box-shadow: 0 0 9px 0 rgba(0, 0, 0, 0.3);
  	margin: 100px auto;
}
.login h1, .register h1 {
  	text-align: center;
  	color: #5b6574;
  	font-size: 24px;
  	padding: 20px 0 20px 0;
  	border-bottom: 1px solid #dee0e4;
}
.login .links, .register .links {
  	display: flex;
  	padding: 0 15px;
}
.login .links a, .register .links a {
  	color: #adb2ba;
  	text-decoration: none;
  	display: inline-flex;
  	padding: 0 10px 10px 10px;
  	font-weight: bold;
}
.login .links a:hover, .register .links a:hover {
  	color: #9da3ac;
}
.login .links a.active, .register .links a.active {
  	border-bottom: 3px solid #3274d6;
  	color: #3274d6;
}
.login form, .register form {
  	display: flex;
  	flex-wrap: wrap;
  	justify-content: center;
  	padding-top: 20px;
}
.login form label, .register form label {
  	display: flex;
  	justify-content: center;
  	align-items: center;
  	width: 50px;
  	height: 50px;
  	background-color: #3274d6;
  	color: #ffffff;
}
.login form input[type="password"], .login form input[type="text"], .login form input[type="email"], .register form input[type="password"], .register form input[type="text"], .register form input[type="email"] {
  	width: 310px;
  	height: 50px;
  	border: 1px solid #dee0e4;
  	margin-bottom: 20px;
  	padding: 0 15px;
}
.login form input[type="submit"], .register form input[type="submit"] {
  	width: 100%;
  	padding: 15px;
  	margin-top: 20px;
  	background-color: #3274d6;
  	border: 0;
  	cursor: pointer;
  	font-weight: bold;
  	color: #ffffff;
  	transition: background-color 0.2s;
}
.login form input[type="submit"]:hover, .register form input[type="submit"]:hover {
  	background-color: #2868c7;
  	transition: background-color 0.2s;
}
.navtop {
  	background-color: #2f3947;
  	height: 60px;
  	width: 100%;
  	border: 0;
}
.navtop div {
  	display: flex;
  	margin: 0 auto;
  	width: 1000px;
  	height: 100%;
}
.navtop div h1, .navtop div a {
  	display: inline-flex;
  	align-items: center;
}
.navtop div h1 {
  	flex: 1;
  	font-size: 24px;
  	padding: 0;
 	margin: 0;
  	color: #eaebed;
  	font-weight: normal;
}
.navtop div a {
  	padding: 0 20px;
  	text-decoration: none;
  	color: #c1c4c8;
  	font-weight: bold;
}
.navtop div a i {
  	padding: 2px 8px 0 0;
}
.navtop div a:hover {
  	color: #eaebed;
}
body.loggedin {
  	background-color: #f3f4f7;
}
.content {
  	width: 1000px;
  	margin: 0 auto;
}
.content h2 {
  	margin: 0;
  	padding: 25px 0;
  	font-size: 22px;
  	border-bottom: 1px solid #e0e0e3;
  	color: #4a536e;
}
.content > p, .content > div {
  	box-shadow: 0 0 5px 0 rgba(0, 0, 0, 0.1);
  	margin: 25px 0;
  	padding: 25px;
	background-color: #fff;
}
.content > p table td, .content > div table td {
	padding: 5px;
}
.content > p table td:first-child, .content > div table td:first-child {
	font-weight: bold;
	color: #4a536e;
	padding-right: 15px;
}
.content > div p {
	padding: 5px;
	margin: 0 0 10px 0;
}

Feel free to customize the stylesheet, change text color, font sizes, content width, etc. This stylesheet will be used for all our pages (login, registration, home, and profile pages).

4. Creating the Login System

We can finally start coding with Python! What we'll do in this part is create the login template, connect to MySQL database, login authentication, and create session variables.

The first thing we need to do is import the packages we're are going to use, edit main.py and insert the following:

from flask import Flask, render_template, request, redirect, url_for, session
from flask_mysqldb import MySQL
import MySQLdb.cursors
import re

Now we need to create some variables and configure the MySQL connection details.

Add after the above code:

app = Flask(__name__)

# Change this to your secret key (can be anything, it's for extra protection)
app.secret_key = 'your secret key'

# Enter your database connection details below
app.config['MYSQL_HOST'] = 'localhost'
app.config['MYSQL_USER'] = 'root'
app.config['MYSQL_PASSWORD'] = ''
app.config['MYSQL_DB'] = 'pythonlogin'

# Intialize MySQL
mysql = MySQL(app)

Make sure you change the MySQL details to your own details and change the secret key.

Now we can create the login page, to do that we need to create a new route.

Add after the above code:

# http://localhost:5000/pythonlogin/ - this will be the login page, we need to use both GET and POST requests
@app.route('/pythonlogin/', methods=['GET', 'POST'])
def login():
    return render_template('index.html', msg='')

4.1 Creating the Login Template

Edit index.html and insert:

<!DOCTYPE html>
<html>
	<head>
		<meta charset="utf-8">
		<title>Login</title>
		<link rel="stylesheet" href="{{ url_for('static', filename='style.css') }}">
		<link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.7.1/css/all.css">
	</head>
	<body>
		<div class="login">
			<h1>Login</h1>
			<div class="links">
				<a href="{{ url_for('login') }}" class="active">Login</a>
				<a href="#">Register</a>
			</div>
			<form action="{{ url_for('login') }}" method="post">
				<label for="username">
					<i class="fas fa-user"></i>
				</label>
				<input type="text" name="username" placeholder="Username" id="username" required>
				<label for="password">
					<i class="fas fa-lock"></i>
				</label>
				<input type="password" name="password" placeholder="Password" id="password" required>
				<div class="msg">{{ msg }}</div>
				<input type="submit" value="Login">
			</form>
		</div>
	</body>
</html>

As you can see with the login template we create the form with the input fields: username and password, and with the forms method set to post, this is so we can send the form data to our server using a POST request.

If we navigate to http://localhost:5000/pythonlogin/ it will look like the following:

http://localhost:5000/pythonlogin/
Python MySQL Login Form

If you click Login nothing will happen or will return an error, that's because we haven't yet set up the POST request to handle the form data.

4.2 Authenticating Users with Python

Now we need to go back to our main.py file and add the authentication code to our route function that we created.

After:

def login():

Add:

    # Output message if something goes wrong...
    msg = ''
    # Check if "username" and "password" POST requests exist (user submitted form)
    if request.method == 'POST' and 'username' in request.form and 'password' in request.form:
        # Create variables for easy access
        username = request.form['username']
        password = request.form['password']

With the code above we use an if statement to check if the requested method is POST and if the username and password exist in the request, if they both exist the username and password variables will be created.

Add after:

        # Check if account exists using MySQL
        cursor = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
        cursor.execute('SELECT * FROM accounts WHERE username = %s AND password = %s', (username, password))
        # Fetch one record and return result
        account = cursor.fetchone()

The code above will select the account in our database accounts table with the username and password that the user entered in the login form.

Add after:

        # If account exists in accounts table in out database
        if account:
            # Create session data, we can access this data in other routes
            session['loggedin'] = True
            session['id'] = account['id']
            session['username'] = account['username']
            # Redirect to home page
            return 'Logged in successfully!'
        else:
            # Account doesnt exist or username/password incorrect
            msg = 'Incorrect username/password!'

This will check if the account exists, if it does we create new session variables, these session variables will be remembered for the user, we can use them to determine if the user is logged in or not.

If the account doesn't exist we can simply show an error on the login form.

Your login route should look like the following:

@app.route('/pythonlogin/', methods=['GET', 'POST'])
def login():
    # Output message if something goes wrong...
    msg = ''
    # Check if "username" and "password" POST requests exist (user submitted form)
    if request.method == 'POST' and 'username' in request.form and 'password' in request.form:
        # Create variables for easy access
        username = request.form['username']
        password = request.form['password']
        # Check if account exists using MySQL
        cursor = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
        cursor.execute('SELECT * FROM accounts WHERE username = %s AND password = %s', (username, password))
        # Fetch one record and return result
        account = cursor.fetchone()
        # If account exists in accounts table in out database
        if account:
            # Create session data, we can access this data in other routes
            session['loggedin'] = True
            session['id'] = account['id']
            session['username'] = account['username']
            # Redirect to home page
            return 'Logged in successfully!'
        else:
            # Account doesnt exist or username/password incorrect
            msg = 'Incorrect username/password!'
    # Show the login form with message (if any)
    return render_template('index.html', msg=msg)

To make sure everything is working correctly navigate to http://localhost:5000/pythonlogin/ and input "test" in both the username and password fields, and then click Login, you should receive a message that outputs "Logged in successfully!".

4.3 Creating the Logout Script

For a user to log out all we have to do is remove the session variables that were created when the user logs in.

Add the following code to the main.py file:

# http://localhost:5000/python/logout - this will be the logout page
@app.route('/pythonlogin/logout')
def logout():
    # Remove session data, this will log the user out
   session.pop('loggedin', None)
   session.pop('id', None)
   session.pop('username', None)
   # Redirect to login page
   return redirect(url_for('login'))

This will remove each session variable, without these session variables the user cannot be logged in. And then redirect the user to the login page.

To log out we can navigate to the following URL: http://localhost:5000/pythonlogin/logout

5. Creating the Registration System

We need a registration system so users can register on our app, what we'll do in this part is create a new register route and create the registration template.

5.1 Creating the Registration Template

Edit register.html and insert:

<!DOCTYPE html>
<html>
	<head>
		<meta charset="utf-8">
		<title>Register</title>
		<link rel="stylesheet" href="{{ url_for('static', filename='style.css') }}">
		<link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.7.1/css/all.css">
	</head>
	<body>
		<div class="register">
			<h1>Register</h1>
			<div class="links">
				<a href="{{ url_for('login') }}">Login</a>
				<a href="{{ url_for('register') }}" class="active">Register</a>
			</div>
			<form action="{{ url_for('register') }}" method="post" autocomplete="off">
				<label for="username">
					<i class="fas fa-user"></i>
				</label>
				<input type="text" name="username" placeholder="Username" id="username" required>
				<label for="password">
					<i class="fas fa-lock"></i>
				</label>
				<input type="password" name="password" placeholder="Password" id="password" required>
				<label for="email">
					<i class="fas fa-envelope"></i>
				</label>
				<input type="email" name="email" placeholder="Email" id="email" required>
				<div class="msg">{{ msg }}</div>
				<input type="submit" value="Register">
			</form>
		</div>
	</body>
</html>

This is a basic registration template we'll use to register users, it's identical to the login template but includes the Email field.

The form's action attribute is set to the "register" route, we'll use this route to handle the POST request.

5.2 Registering Users with Python

Now that we have our template created we can begin to create the "register" route, this will handle the POST request and insert a new account into our accounts table, only if the submitted fields are valid that is.

Go back to the main.py and insert the following:

# http://localhost:5000/pythinlogin/register - this will be the registration page, we need to use both GET and POST requests
@app.route('/pythonlogin/register', methods=['GET', 'POST'])
def register():
    # Output message if something goes wrong...
    msg = ''
    # Check if "username", "password" and "email" POST requests exist (user submitted form)
    if request.method == 'POST' and 'username' in request.form and 'password' in request.form and 'email' in request.form:
        # Create variables for easy access
        username = request.form['username']
        password = request.form['password']
        email = request.form['email']
    elif request.method == 'POST':
        # Form is empty... (no POST data)
        msg = 'Please fill out the form!'
    # Show registration form with message (if any)
    return render_template('register.html', msg=msg)

Basically here we create the "register" route and check if all the submitted form fields exist if they don't output a simple error.

After:

email = request.form['email']

Add:

        # Check if account exists using MySQL
        cursor = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
        cursor.execute('SELECT * FROM accounts WHERE username = %s', (username))
        account = cursor.fetchone()
        # If account exists show error and validation checks
        if account:
            msg = 'Account already exists!'
        elif not re.match(r'[^@]+@[^@]+\.[^@]+', email):
            msg = 'Invalid email address!'
        elif not re.match(r'[A-Za-z0-9]+', username):
            msg = 'Username must contain only characters and numbers!'
        elif not username or not password or not email:
            msg = 'Please fill out the form!'
        else:
            # Account doesnt exists and the form data is valid, now insert new account into accounts table
            cursor.execute('INSERT INTO accounts VALUES (NULL, %s, %s, %s)', (username, password, email))
            mysql.connection.commit()
            msg = 'You have successfully registered!'

The above code will select an account with the submitted username and password, if the account doesn't exist we can validate the data, the validation will check if the submitted email is valid and check if the username contains only letters and numbers.

If everything checks out the code will insert a new account into our accounts tables.

To test that it is working correctly navigate to http://localhost:5000/pythonlogin/register and fill out the form and click the Register button, you should get the following response:

http://localhost:5000/pythonlogin/register
Python Registration Form

Users can now register and login to our app, next we'll create a basic home page for logged in users.

6. Creating the Home Page

The home page will be for logged in users, it's just a basic example.

Edit main.py and add the following:

# http://localhost:5000/pythinlogin/home - this will be the home page, only accessible for loggedin users
@app.route('/pythonlogin/home')
def home():
    # Check if user is loggedin
    if 'loggedin' in session:
        # User is loggedin show them the home page
        return render_template('home.html', username=session['username'])
    # User is not loggedin redirect to login page
    return redirect(url_for('login'))

This will create the home route function, if the user is logged in they will have access to the home page, if not redirect them to the login page.

Edit home.html and add the following:

{% extends 'layout.html' %}

{% block title %}Home{% endblock %}

{% block content %}
<h2>Home Page</h2>
<p>Welcome back, {{ username }}!</p>
{% endblock %}

We also need to create the layout for our logged in pages, edit layout.html and add:

<!DOCTYPE html>
<html>
	<head>
		<meta charset="utf-8">
		<title>{% block title %}{% endblock %}</title>
		<link rel="stylesheet" href="{{ url_for('static', filename='style.css') }}">
		<link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.7.1/css/all.css">
	</head>
	<body class="loggedin">
		<nav class="navtop">
			<div>
				<h1>Website Title</h1>
                <a href="{{ url_for('home') }}"><i class="fas fa-home"></i>Home</a>
				<a href="{{ url_for('profile') }}"><i class="fas fa-user-circle"></i>Profile</a>
				<a href="{{ url_for('logout') }}"><i class="fas fa-sign-out-alt"></i>Logout</a>
			</div>
		</nav>
		<div class="content">
			{% block content %}{% endblock %}
		</div>
	</body>
</html>

Now we can easily use the same layout for both the home and profile pages.

Currently, when a user logs in there will be an output message, we can now change that to redirect to the home page instead, find the following code in the login route function:

return 'Logged in successfully!'

Replace with:

return redirect(url_for('home'))

The user will now be redirected to the home page when they log in, enter the test details into the login form and click Login, you should see the following:

http://localhost:5000/pythonlogin/home
Python Login Home Page

This is just a simple home page that you can work from, next we'll create the profile page and display the user's details.

7. Creating the Profile Page

The profile page is where the user can go to view their details (username, password, and email).

Add the following route to main.cy:

# http://localhost:5000/pythinlogin/profile - this will be the profile page, only accessible for loggedin users
@app.route('/pythonlogin/profile')
def profile():
    # Check if user is loggedin
    if 'loggedin' in session:
        # We need all the account info for the user so we can display it on the profile page
        cursor = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
        cursor.execute('SELECT * FROM accounts WHERE id = %s', [session['id']])
        account = cursor.fetchone()
        # Show the profile page with account info
        return render_template('profile.html', account=account)
    # User is not loggedin redirect to login page
    return redirect(url_for('login'))

This will create the profile route and select all the account details from the database if the user is logged in.

Edit profile.html and add:

{% extends 'layout.html' %}

{% block title %}Profile{% endblock %}

{% block content %}
<h2>Profile Page</h2>
<div>
    <p>Your account details are below:</p>
    <table>
        <tr>
            <td>Username:</td>
            <td>{{ account['username'] }}</td>
        </tr>
        <tr>
            <td>Password:</td>
            <td>{{ account['password'] }}</td>
        </tr>
        <tr>
            <td>Email:</td>
            <td>{{ account['email'] }}</td>
        </tr>
    </table>
</div>
{% endblock %}

This will extend the layout (layout.html) that we created previously, if you navigate to the profile page it will look like the following:

http://localhost:5000/pythonlogin/profile
Python Login Profile Page

That's basically it for the home and profile pages, the Advanced package includes the edit profile feature, this will allow the user to change their username, password, and email.

Conclusion

Congratulations! You've created your own login and registration system with Python Flask and MySQL. You're free to use the source code in your application(s).

If you enjoyed this tutorial don't forget to hit the share button and drop a comment, this will help us create more tutorials.

If you would like to support us consider purchasing a package below, this will greatly help us create more tutorials and keep our server up and running.

Basic

Advanced

Source code
Database SQL file
Login & Registration system
Home page
Profile page
Activate account feature
Edit profile page
Remember me feature
AJAX integration
Password encryption
SCSS file
Commented code
Free updates
Free support (bugs and minor issues)
* Payments are processed with PayPal.
* Both packages include the tutorial source code.
* Advanced package also includes the basic package.

$5.00

Download

$15.00

Download