Data Storage Fundamentals: How Python Web Saves User Information with SQLite

In web development, we often need to store user registration information, login status, personal data, etc. Without data storage, information will be lost after the user refreshes the page—this is like trying to keep a diary without a notebook, where you’d forget what you wrote. SQLite is a lightweight database ideal for beginners to quickly get started because it doesn’t require an additional server; data is stored directly in files, acting like a “local small warehouse.”

Why Use SQLite?¶

SQLite is a built-in module in Python. Once Python is installed, it can be used directly without additional server configuration. It is suitable for small projects or development-stage applications because:
- No need to install an extra database server; operations are directly performed on local files
- Simple syntax, easy to understand
- Supports basic SQL statements with low learning cost
- Beginner-friendly, enabling quick implementation of data persistence

Environment Setup¶

We’ll use Flask (a lightweight web framework suitable for beginners) and SQLite (Python’s built-in, no extra installation required). First, ensure Flask is installed:

pip install flask

Creating the Database and User Table¶

First, we need a place to store user information—the “database” and “table.” SQLite uses files as databases, and tables are similar to Excel spreadsheets, organizing information with different fields.

Step 1: Connect to the Database and Create a Table¶

Create a new Python file (e.g., app.py). Import necessary libraries first:

import sqlite3
from flask import Flask, request, render_template_string

Then initialize the Flask application and define a function to create the database and user table. A user table requires at least:
- id: Unique identifier, auto-incrementing integer (no manual input needed)
- username: Username (unique, non-repeating)
- password: Password (simplified here; always encrypt in real projects!)
- email: Email (optional)

app = Flask(__name__)

# Function to connect to the database, returning a connection and cursor
def get_db_connection():
    conn = sqlite3.connect('user_database.db')  # Database file name: user_database.db
    conn.row_factory = sqlite3.Row  # Return query results as dictionaries for easy manipulation
    return conn

# Create user table (only execute once; comment out later to prevent duplicate creation)
def init_db():
    conn = get_db_connection()
    # SQL statement to create the user table
    conn.execute('''
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT NOT NULL UNIQUE,
            password TEXT NOT NULL,
            email TEXT
        )
    ''')
    conn.commit()
    conn.close()

# Initialize the database when the program starts (run only once, then comment out)
init_db()

Tip: CREATE TABLE IF NOT EXISTS ensures the table is created only if it doesn’t exist, avoiding duplicate execution errors. AUTOINCREMENT makes id auto-incrementing, so manual input is unnecessary.

Python Database Operations: CRUD¶

Now we implement user registration and viewing user lists—the two most common operations.

1. Register a User (Create Data)¶

When a user fills out the username, password, and email on the webpage and clicks submit, the data must be saved to the database. Use Flask routes to handle POST requests:

@app.route('/register', methods=['GET', 'POST'])
def register():
    if request.method == 'POST':
        # Retrieve user input from the form
        username = request.form.get('username')
        password = request.form.get('password')
        email = request.form.get('email', '')  # Email is optional, default to empty string

        # Connect to the database and insert data
        conn = get_db_connection()
        conn.execute('INSERT INTO users (username, password, email) VALUES (?, ?, ?)',
                     (username, password, email))
        conn.commit()  # Commit the transaction to save data
        conn.close()

        return "Registration successful! <a href='/users'>View User List</a>"
    else:
        # Display the registration form for GET requests
        return render_template_string('''
            <form method="POST">
                Username: <input type="text" name="username"><br>
                Password: <input type="password" name="password"><br>
                Email: <input type="email" name="email"><br>
                <button type="submit">Register</button>
            </form>
        ''')

2. Display User List (Read Data)¶

After registration, we need to view all user information—a query operation:

@app.route('/users')
def show_users():
    conn = get_db_connection()
    # Query all users and sort by ID
    users = conn.execute('SELECT * FROM users ORDER BY id').fetchall()
    conn.close()

    # Render user data into an HTML page
    user_list = '<h1>User List</h1><ul>'
    for user in users:
        user_list += f'<li>ID: {user["id"]}, Username: {user["username"]}, Email: {user["email"]}</li>'
    user_list += '</ul>'
    return user_list

3. Complete Code Integration¶

Integrate the above code into app.py:

import sqlite3
from flask import Flask, request, render_template_string

app = Flask(__name__)

# Database connection function
def get_db_connection():
    conn = sqlite3.connect('user_database.db')
    conn.row_factory = sqlite3.Row  # Results returned as dictionaries
    return conn

# Initialize database table (only run once)
def init_db():
    conn = get_db_connection()
    conn.execute('''
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT NOT NULL UNIQUE,
            password TEXT NOT NULL,
            email TEXT
        )
    ''')
    conn.commit()
    conn.close()

init_db()  # Run once, then comment out

# Registration page
@app.route('/register', methods=['GET', 'POST'])
def register():
    if request.method == 'POST':
        username = request.form.get('username')
        password = request.form.get('password')
        email = request.form.get('email', '')

        conn = get_db_connection()
        # Use parameterized queries to prevent SQL injection (critical!)
        conn.execute('INSERT INTO users (username, password, email) VALUES (?, ?, ?)',
                     (username, password, email))
        conn.commit()
        conn.close()
        return f"Registration successful! <a href='/users'>View User List</a>"
    else:
        return '''
            <form method="POST">
                Username: <input type="text" name="username"><br>
                Password: <input type="password" name="password"><br>
                Email: <input type="email" name="email"><br>
                <button type="submit">Register</button>
            </form>
        '''

# User list page
@app.route('/users')
def show_users():
    conn = get_db_connection()
    users = conn.execute('SELECT * FROM users ORDER BY id').fetchall()
    conn.close()
    result = '<h1>User List</h1>'
    for user in users:
        result += f'''
            <div>
                <p>ID: {user['id']}</p>
                <p>Username: {user['username']}</p>
                <p>Email: {user['email']}</p>
            </div>
        '''
    return result

if __name__ == '__main__':
    app.run(debug=True)

Verify Data and View the Database¶

Run the program:

python app.py

Then visit http://127.0.0.1:5000/register, fill in the details and submit. Then visit http://127.0.0.1:5000/users to check if the new user appears.

View the Database File¶

SQLite data is stored in the user_database.db file. You can view its contents using:

Method 1: Print data with Python code (for temporary debugging):

  def print_users():
      conn = get_db_connection()
      users = conn.execute('SELECT * FROM users').fetchall()
      for user in users:
          print(dict(user))  # Print in dictionary format
      conn.close()
  print_users()

Method 2: Use a visualization tool (recommended): Download DB Browser for SQLite, open user_database.db, and view the table content directly.

Notes¶

Password Security: Passwords are stored in plaintext in the example. Always encrypt passwords with a hashing algorithm (e.g., bcrypt) in production:

   import bcrypt
   password = bcrypt.hashpw(request.form.get('password').encode('utf-8'), bcrypt.gensalt())

SQL Injection Prevention: The example uses parameterized queries (?, ?) to avoid directly concatenating SQL strings.
Database Connection Closure: Always close the connection (conn.close()) after operations to prevent resource leaks.
Concurrency Issues: Simple SQLite connections may have issues under high concurrency. Use a with statement to manage connections automatically:

   with get_db_connection() as conn:
       conn.execute(...)
       conn.commit()

Conclusion¶

As a lightweight database, SQLite is an ideal choice for Python Web beginners learning data storage. Through this article, you’ve mastered:
- Basic concepts and advantages of SQLite
- Fundamentals of CRUD operations with Python
- Integrating SQLite with Flask for user registration and display
- Data verification and database viewing

Next steps to explore:
- Implement user login verification (password comparison)
- Use ORM frameworks (e.g., SQLAlchemy) to simplify operations
- Handle database migrations (e.g., Alembic)
- Optimize database usage for server deployment

SQLite, though small, addresses most basic data storage needs in web development and is the first step in learning data persistence!