Flask + SQLite — Building Database-Driven Web Apps

🎯 Learning Objectives

• Connect SQLite database with Flask
• Execute CRUD operations (Create, Read, Update, Delete)
• Use parameterized queries to prevent SQL injection
• Structure database code for maintainability

1. Setting Up SQLite with Flask

python
Run Inline
import sqlite3
from flask import Flask, render_template, request, redirect, flash
app = Flask(__name__)
app.secret_key = 'dev'
def get_db():
    """Get database connection"""
    conn = sqlite3.connect('database.db')
    conn.row_factory = sqlite3.Row  # Access columns by name
    return conn
def init_db():
    """Create tables if they don't exist"""
    with get_db() as conn:
        conn.execute('''
            CREATE TABLE IF NOT EXISTS users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                email TEXT UNIQUE NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        conn.commit()
if __name__ == '__main__':
    init_db()
    app.run(debug=True)

2. CRUD Operations

2.1 Create (INSERT)

python
Run Inline
@app.route('/add', methods=['GET', 'POST'])
def add_user():
    if request.method == 'POST':
        name = request.form['name']
        email = request.form['email']
        try:
            with get_db() as conn:
                conn.execute(
                    'INSERT INTO users (name, email) VALUES (?, ?)',
                    (name, email)
                )
                conn.commit()
            flash('User added!', 'success')
            return redirect('/users')
        except sqlite3.IntegrityError:
            flash('Email already exists!', 'error')
    return render_template('add_user.html')

2.2 Read (SELECT)

python
Run Inline
@app.route('/users')
def list_users():
    with get_db() as conn:
        users = conn.execute('SELECT * FROM users ORDER BY created_at DESC').fetchall()
    return render_template('users.html', users=users)
@app.route('/user/<int:id>')
def view_user(id):
    with get_db() as conn:
        user = conn.execute('SELECT * FROM users WHERE id = ?', (id,)).fetchone()
        if user is None:
            flash('User not found!', 'error')
            return redirect('/users')
    return render_template('user.html', user=user)

2.3 Update (UPDATE)

python
Run Inline
@app.route('/edit/<int:id>', methods=['GET', 'POST'])
def edit_user(id):
    with get_db() as conn:
        user = conn.execute('SELECT * FROM users WHERE id = ?', (id,)).fetchone()
        if user is None:
            flash('User not found!', 'error')
            return redirect('/users')
        if request.method == 'POST':
            name = request.form['name']
            email = request.form['email']
            conn.execute(
                'UPDATE users SET name = ?, email = ? WHERE id = ?',
                (name, email, id)
            )
            conn.commit()
            flash('User updated!', 'success')
            return redirect('/users')
    return render_template('edit_user.html', user=user)

2.4 Delete (DELETE)

python
Run Inline
@app.route('/delete/<int:id>', methods=['POST'])
def delete_user(id):
    with get_db() as conn:
        conn.execute('DELETE FROM users WHERE id = ?', (id,))
        conn.commit()
    flash('User deleted!', 'success')
    return redirect('/users')

3. SQL Injection Prevention

python
Run Inline
# ❌ BAD — string formatting vulnerable to SQL injection
username = request.form['username']
conn.execute(f"SELECT * FROM users WHERE name = '{username}'")
# User types: '; DROP TABLE users; --  (table dropped!)
# ✅ GOOD — parameterized query
conn.execute('SELECT * FROM users WHERE name = ?', (username,))

4. template for Listing Users

html
{% extends "base.html" %}
{% block content %}
    <h1>Users</h1>
    <a href="/add">Add User</a>
    

| ID | Name | Email | Created | Actions |
| --- | --- | --- | --- | --- |
| {{ user.id }} | {{ user.name }} | {{ user.email }} | {{ user.created_at }} | Edit Delete |


{% endblock %}

5. Practice Questions

Q1: How do you prevent SQL injection?

Answer: Use parameterized queries with ? placeholders. Never use string formatting (f"..." or %) to build SQL queries. The database driver escapes values safely when using placeholders. Q2: What does conn.row_factory = sqlite3.Row do?

Answer: Allows accessing columns by name instead of index: user['name'] instead of user[1]. Makes code more readable and less fragile to column order changes. Q3: What is the purpose of fetchone() vs fetchall()?

Answer: fetchone() returns a single row (or None). fetchall() returns a list of all rows. Use fetchone() for queries expecting one result (by ID), fetchall() for list queries. Q4: Why use with get_db() as conn?

Answer: The with statement ensures the connection is properly closed even if an exception occurs. It's a context manager that auto-commits or rolls back transactions. Q5: What does sqlite3.IntegrityError indicate?

Answer: A database constraint violation — typically UNIQUE constraint (duplicate email) or NOT NULL constraint (missing required field). Q6: Write a query to find users with names starting with 'A'.

python
Run Inline
users = conn.execute(
    'SELECT * FROM users WHERE name LIKE ?',
    ('A%',)
).fetchall()

Q7: What's the difference between commit and close?

Answer: commit() saves pending changes to the database. close() closes the connection. The with statement handles both automatically — it commits on successful exit, rolls back on exception. Q8: How do you handle a form that updates a user's profile?

python
Run Inline
@app.route('/profile/update', methods=['POST'])
def update_profile():
    name = request.form['name']
    email = request.form['email']
    user_id = session['user_id']  # From logged-in user

    with get_db() as conn:
        conn.execute('UPDATE users SET name=?, email=? WHERE id=?',
                    (name, email, user_id))
        conn.commit()
    flash('Profile updated!', 'success')
    return redirect('/profile')

📐 Key Concepts

🔗 Cross-References

• Next: Sessions & Cookies Join Discord Previous3.2 Flask FormsNext5.1 Sessions & Cookies