Python MySQL User Registration: Duplicate Validation & Hashing

Interactive Google Colab Practice Notebook

Practice building secure user registration workflows with duplicate username checks, bcrypt password hashing, and SQLAlchemy 2.0+ context managers directly inside Google Colab.

View on GitHub

User registration is one of the most fundamental workflows in web development. In this tutorial, we will build a secure user registration workflow in Python that validates duplicate usernames or emails, hashes passwords using industry-standard bcrypt encryption, and stores records securely in a MySQL database using SQLAlchemy 2.0+ [cite: 21].

1. Creating the Users Table Schema

Before capturing registrations, ensure your database has a dedicated users table with unique constraints on the username and email fields to prevent duplicates at the database level [cite: 21]:

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. Handling Registration with Hashing and Duplicate Validation

When a user submits a registration form, our Python script must perform two crucial checks before writing to the database [cite: 21]:

1. Check if the username or email already exists in the table.
2. Hash the plaintext password using bcrypt so plaintext credentials are never exposed in storage.

from sqlalchemy import create_engine, text
import bcrypt

# Initialize SQLAlchemy engine
engine = create_engine("mysql+pymysql://demo_user:demo_password@localhost/my_tutorial")

def register_user(username, email, password):
    with engine.connect() as conn:
        # Step 1: Check for duplicate username or email
        check_query = text("SELECT id FROM users WHERE username = :uname OR email = :email")
        existing = conn.execute(check_query, {"uname": username, "email": email}).fetchone()
        
        if existing:
            print("Registration Failed: Username or Email already exists!")
            return False

    # Step 2: Hash the password securely using bcrypt
    salt = bcrypt.gensalt()
    hashed_password = bcrypt.hashpw(password.encode('utf-8'), salt)

    # Step 3: Insert user into database inside transaction block
    try:
        with engine.begin() as conn:
            insert_query = text(
                "INSERT INTO users (username, email, password_hash) VALUES (:uname, :email, :phash)"
            )
            conn.execute(insert_query, {
                "uname": username,
                "email": email,
                "phash": hashed_password.decode('utf-8')
            })
            print(f"User '{username}' registered successfully!")
            return True
    except Exception as e:
        print("Database error during registration:", e)
        return False

# Test registration
register_user("john_doe", "john@example.com", "SecurePassword123")

Frequently Asked Questions & Common Errors

Related Tutorials & Next Steps

• Python SQLAlchemy Insert Guide

  Learn how to insert records and handle database transactions using SQLAlchemy 2.0+ [cite: 21].

• Python MySQL Error Handling

  Master capturing duplicate entry codes and database exceptions [cite: 21].

• Python MySQL Connection Guide

  Explore database connection settings and connection pooling [cite: 21].

• Student Table SQL Dump File

  Download sample database schemas and data dumps [cite: 21].

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: