Skip to main content

​
Data Flow Architecture

Understanding how data moves from the database to the user’s browser is essential for web development.
1

Database Query

Python code executes SQL to retrieve data from SQLite
2

Data Processing

Flask processes the data and prepares it for display
3

Template Rendering

Jinja template engine combines data with HTML
4

HTTP Response

Browser receives and displays the final HTML

​
1. Data Retrieval (Python/SQLite)

The core logic for fetching data resides in the Flask application. 
import sqlite3
from flask import Flask, render_template

app = Flask(__name__)

def get_users():
    # Establish database connection
    conn = sqlite3.connect('users.db')
    
    # Set row factory for dictionary-like access
    conn.row_factory = sqlite3.Row
    
    # Create cursor
    cur = conn.cursor()
    
    # Execute query
    cur.execute("SELECT * FROM users")
    
    # Fetch all results
    users = cur.fetchall()
    
    # Close connection
    conn.close()
    
    return users

​
Key Components

conn = sqlite3.connect('users.db')
cursor = conn.cursor()
  • Connection: Establishes link to the database file
  • Cursor: Executes SQL commands and fetches results
conn.row_factory = sqlite3.Row
This crucial setting makes retrieved rows behave like dictionaries, allowing column access by name (user['name']) instead of index (user[1]).
Without row_factory:
user = (1, 'Alice Smith', '[email protected]')
name = user[1]  # Access by index
With row_factory:
user = Row(id=1, name='Alice Smith', email='[email protected]')
name = user['name']  # Access by column name

cur.execute("SELECT * FROM users")
users = cur.fetchall()
  • execute(): Runs the SQL query
  • fetchall(): Returns all resulting rows as a list
  • Alternative methods: fetchone() for single row, fetchmany(n) for n rows

​
2. Passing Data to Templates

Flask’s render_template function bridges Python data and HTML. 
@app.route('/')
def index():
    users = get_users()
    return render_template('index.html', users=users)
The keyword argument users=users makes the Python variable users available inside the index.html template under the same name.

​
How It Works

# Route handler
@app.route('/')
def index():
    # Get data from database
    users = get_users()
    
    # Pass to template as keyword argument
    return render_template('index.html', users=users)
                         # template name    # variable name

​
3. Rendering in HTML (Jinja)

Jinja is Flask’s templating language that processes dynamic content.

​
Template Syntax

Control Structures

{% for item in items %}
{% if condition %}
{% endif %}
{% endfor %}
Use {% %} for logic

Output Variables

{{ variable }}
{{ user['name'] }}
{{ user.email }}
Use {{ }} to display data

​
Complete Example

<!DOCTYPE html>
<html>
<head>
    <title>Users List</title>
    <style>
        table {
            border-collapse: collapse;
            width: 100%;
        }
        th, td {
            border: 1px solid #ddd;
            padding: 8px;
            text-align: left;
        }
        th {
            background-color: #4CAF50;
            color: white;
        }
    </style>
</head>
<body>
    <h1>Registered Users</h1>
    
    <table>
        <thead>
            <tr>
                <th>ID</th>
                <th>Name</th>
                <th>Email</th>
            </tr>
        </thead>
        <tbody>
            {% for user in users %}
            <tr>
                <td>{{ user['id'] }}</td>
                <td>{{ user['name'] }}</td>
                <td>{{ user['email'] }}</td>
            </tr>
            {% endfor %}
        </tbody>
    </table>
</body>
</html>

​
Jinja Features

{% for user in users %}
    <li>{{ user['name'] }} - {{ user['email'] }}</li>
{% endfor %}
Iterate over the list of users and display each one.
{% if users %}
    <p>Found {{ users|length }} users</p>
{% else %}
    <p>No users found</p>
{% endif %}
Show different content based on conditions.
{{ user['name']|upper }}
{{ user['email']|lower }}
{{ users|length }}
Transform data before displaying it.

​
SQLite as a Lightweight Backend

SQLite serves as an excellent data persistence layer for small to medium applications.

​
Why SQLite?

Serverless

No separate server process needed - operates directly on a file

Embedded

Runs in-process with the Flask application

Zero Configuration

Works out of the box with Python’s built-in module

ACID Compliant

Provides full transactional guarantees

​
Use Cases

  • Development and prototyping
  • Small web applications
  • Mobile and desktop apps
  • Testing environments
  • Low to moderate traffic sites
  • Single-user applications
SQLite supports multiple concurrent reads efficiently, but only allows one writer at a time. For production web applications with many simultaneous users, consider PostgreSQL or MySQL.

​
Best Practices

1

Use context managers

def get_users():
    with sqlite3.connect('users.db') as conn:
        conn.row_factory = sqlite3.Row
        cur = conn.cursor()
        cur.execute("SELECT * FROM users")
        return cur.fetchall()
Automatically closes the connection, even if an error occurs.
2

Separate database logic

# database.py
def get_db_connection():
    conn = sqlite3.connect('users.db')
    conn.row_factory = sqlite3.Row
    return conn
Keep database code organized and reusable.
3

Handle errors gracefully

try:
    users = get_users()
except sqlite3.Error as e:
    print(f"Database error: {e}")
    users = []
Always anticipate potential database errors.
For a more scalable approach, consider using an ORM like SQLAlchemy, which provides an abstraction layer making it easier to switch databases later.