DevDuniya
Mar 26, 2025
In this comprehensive tutorial, we'll walk through building a complete authentication system with Flask and MySQL. You'll learn how to implement user registration, login functionality, protected dashboard access, and logout features - all while securely storing user data in a MySQL database.
Flask is a lightweight Python web framework that's perfect for building web applications, while MySQL remains one of the most popular relational database systems. Combining these technologies gives you:
Before we begin, make sure you have:
First, let's create our project structure and install the necessary packages.
mkdir flask-auth-tutorial
cd flask-auth-tutorial
python -m venv venv
source venv/bin/activate # On Windows use `venv\Scripts\activate`
pip install flask flask-mysqldb flask-login passlib
Here's how we'll organize our files:
flask-auth-tutorial/
│
├── app.py # Main application file
├── templates/ # HTML templates
│ ├── layout.html # Base template
│ ├── login.html # Login page
│ ├── register.html # Registration page
│ └── dashboard.html # Dashboard page
└── requirements.txt # Dependencies
First, let's create our MySQL database and user table.
mysql -u root -p
CREATE DATABASE flask_auth;
USE flask_auth;
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Now, let's create our Flask application with authentication functionality.
from flask import Flask, render_template, request, redirect, url_for, flash, session
from flask_mysqldb import MySQL
from passlib.hash import sha256_crypt
from flask_login import LoginManager, UserMixin, login_user, login_required, logout_user, current_user
app = Flask(__name__)
# MySQL Configuration
app.config['MYSQL_HOST'] = 'localhost'
app.config['MYSQL_USER'] = 'root'
app.config['MYSQL_PASSWORD'] = '' # Enter your MySQL password here
app.config['MYSQL_DB'] = 'flask_auth'
app.config['MYSQL_CURSORCLASS'] = 'DictCursor'
# Secret Key
app.secret_key = 'your_secret_key_here'
# Initialize MySQL
mysql = MySQL(app)
# Flask-Login Configuration
login_manager = LoginManager()
login_manager.init_app(app)
login_manager.login_view = 'login'
class User(UserMixin):
def __init__(self, id, username, email):
self.id = id
self.username = username
self.email = email
@login_manager.user_loader
def load_user(user_id):
cur = mysql.connection.cursor()
cur.execute("SELECT * FROM users WHERE id = %s", (user_id,))
user = cur.fetchone()
cur.close()
if user:
return User(id=user['id'], username=user['username'], email=user['email'])
return None
# Routes
@app.route('/')
def home():
return render_template('home.html')
@app.route('/register', methods=['GET', 'POST'])
def register():
if request.method == 'POST':
username = request.form['username']
email = request.form['email']
password = sha256_crypt.encrypt(request.form['password'])
cur = mysql.connection.cursor()
# Check if username or email already exists
cur.execute("SELECT * FROM users WHERE username = %s OR email = %s", (username, email))
existing_user = cur.fetchone()
if existing_user:
flash('Username or email already exists', 'danger')
return render_template('register.html')
# Insert new user
cur.execute("INSERT INTO users (username, email, password) VALUES (%s, %s, %s)",
(username, email, password))
mysql.connection.commit()
cur.close()
flash('Registration successful! Please login.', 'success')
return redirect(url_for('login'))
return render_template('register.html')
@app.route('/login', methods=['GET', 'POST'])
def login():
if request.method == 'POST':
username = request.form['username']
password_candidate = request.form['password']
cur = mysql.connection.cursor()
# Get user by username
result = cur.execute("SELECT * FROM users WHERE username = %s", [username])
if result > 0:
data = cur.fetchone()
password = data['password']
# Compare passwords
if sha256_crypt.verify(password_candidate, password):
user = User(id=data['id'], username=data['username'], email=data['email'])
login_user(user)
flash('You are now logged in', 'success')
return redirect(url_for('dashboard'))
else:
flash('Invalid password', 'danger')
return render_template('login.html')
else:
flash('Username not found', 'danger')
return render_template('login.html')
cur.close()
return render_template('login.html')
@app.route('/dashboard')
@login_required
def dashboard():
return render_template('dashboard.html', username=current_user.username)
@app.route('/logout')
@login_required
def logout():
logout_user()
flash('You are now logged out', 'success')
return redirect(url_for('login'))
if __name__ == '__main__':
app.run(debug=True)
Now let's create our templates for the different pages.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Flask Auth System</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
<nav class="navbar navbar-expand-lg navbar-dark bg-dark">
<div class="container">
<a class="navbar-brand" href="/">Flask Auth</a>
<button class="navbar-toggler" type="button" data-bs-toggle="collapse" data-bs-target="#navbarNav">
<span class="navbar-toggler-icon"></span>
</button>
<div class="collapse navbar-collapse" id="navbarNav">
<ul class="navbar-nav ms-auto">
{% if current_user.is_authenticated %}
<li class="nav-item">
<a class="nav-link" href="{{ url_for('dashboard') }}">Dashboard</a>
</li>
<li class="nav-item">
<a class="nav-link" href="{{ url_for('logout') }}">Logout</a>
</li>
{% else %}
<li class="nav-item">
<a class="nav-link" href="{{ url_for('login') }}">Login</a>
</li>
<li class="nav-item">
<a class="nav-link" href="{{ url_for('register') }}">Register</a>
</li>
{% endif %}
</ul>
</div>
</div>
</nav>
<div class="container mt-4">
{% with messages = get_flashed_messages(with_categories=true) %}
{% if messages %}
{% for category, message in messages %}
<div class="alert alert-{{ category }} alert-dismissible fade show" role="alert">
{{ message }}
<button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
</div>
{% endfor %}
{% endif %}
{% endwith %}
{% block content %}{% endblock %}
</div>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>
{% extends "layout.html" %}
{% block content %}
<div class="row justify-content-center">
<div class="col-md-6">
<div class="card">
<div class="card-header">
<h4>Login</h4>
</div>
<div class="card-body">
<form action="{{ url_for('login') }}" method="POST">
<div class="form-group mb-3">
<label for="username">Username</label>
<input type="text" name="username" id="username" class="form-control" required>
</div>
<div class="form-group mb-3">
<label for="password">Password</label>
<input type="password" name="password" id="password" class="form-control" required>
</div>
<button type="submit" class="btn btn-primary">Login</button>
</form>
<p class="mt-3">Don't have an account? <a href="{{ url_for('register') }}">Register here</a></p>
</div>
</div>
</div>
</div>
{% endblock %}
{% extends "layout.html" %}
{% block content %}
<div class="row justify-content-center">
<div class="col-md-6">
<div class="card">
<div class="card-header">
<h4>Register</h4>
</div>
<div class="card-body">
<form action="{{ url_for('register') }}" method="POST">
<div class="form-group mb-3">
<label for="username">Username</label>
<input type="text" name="username" id="username" class="form-control" required>
</div>
<div class="form-group mb-3">
<label for="email">Email</label>
<input type="email" name="email" id="email" class="form-control" required>
</div>
<div class="form-group mb-3">
<label for="password">Password</label>
<input type="password" name="password" id="password" class="form-control" required>
</div>
<button type="submit" class="btn btn-primary">Register</button>
</form>
<p class="mt-3">Already have an account? <a href="{{ url_for('login') }}">Login here</a></p>
</div>
</div>
</div>
</div>
{% endblock %}
{% extends "layout.html" %}
{% block content %}
<div class="row justify-content-center">
<div class="col-md-8">
<div class="card">
<div class="card-header">
<h4>Welcome to Your Dashboard, {{ username }}!</h4>
</div>
<div class="card-body">
<p>This is your protected dashboard page. Only authenticated users can access this page.</p>
<p>You can implement any content here that should be visible to logged-in users only.</p>
</div>
</div>
</div>
</div>
{% endblock %}
To start your Flask application, run:
python app.py
Then open your browser and navigate to http://localhost:5000.
@login_requiredPassword Hashing: We use passlib with SHA-256 to securely hash passwords before storing them.
Session Management: Flask-Login handles session management securely.
CSRF Protection: While not shown in this basic example, consider implementing CSRF protection for production.
SQL Injection Prevention: Using parameterized queries helps prevent SQL injection.
HTTPS: Always use HTTPS in production to encrypt data in transit.
Here are some ways you can extend this basic authentication system:
Email Verification: Add email confirmation before allowing login.
Password Reset: Implement a password reset functionality.
User Roles: Add roles (admin, user, etc.) with different permissions.
Profile Management: Allow users to edit their profiles.
Remember Me: Implement "remember me" functionality for persistent sessions.
MySQL Connection Errors: Ensure MySQL is running and credentials in app.py are correct.
Table Not Found: Verify you created the users table in the flask_auth database.
Module Not Found: Make sure you installed all required packages with pip.
Login Not Persisting: Check your secret key is set and consistent.
Password Verification Failing: Ensure you're using the same hashing method consistently.
In this tutorial, we've built a complete Flask authentication system with MySQL that includes:
This foundation can be extended to build more complex web applications with user authentication. The combination of Flask and MySQL provides a robust yet flexible system that can scale as your needs grow.
Remember to always prioritize security when dealing with user authentication, especially in production environments. Consider adding additional layers of security like rate limiting, brute force protection, and more sophisticated password policies for real-world applications.
Dev Duniya
DevDuniya
DevDuniya