Flask SQLite Basics

SQLite is a lightweight database that stores all its data in a single file on disk. Flask works with SQLite directly through Python's built-in sqlite3 module — no extra software installation required. This makes SQLite the perfect starting point for learning database-backed Flask applications.

What SQLite Is

Most databases run as a separate server process. SQLite is different — it is a library that your application links directly. The entire database lives in one .db file. For applications with moderate traffic (thousands of users per day), SQLite performs excellently.

Traditional database:
  Flask app ──network connection──▶ Database server ──▶ data files

SQLite:
  Flask app ──direct file access──▶ myapp.db

Creating a Database and Table

import sqlite3

def get_db():
    conn = sqlite3.connect('users.db')
    conn.row_factory = sqlite3.Row  # returns rows as dict-like objects
    return conn

def init_db():
    conn = get_db()
    conn.execute('''
        CREATE TABLE IF NOT EXISTS users (
            id       INTEGER PRIMARY KEY AUTOINCREMENT,
            name     TEXT    NOT NULL,
            email    TEXT    UNIQUE NOT NULL,
            created  TEXT    DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    conn.commit()
    conn.close()

init_db()

sqlite3.Row makes each row behave like a dictionary so you access columns by name (row['name']) instead of index (row[1]).

Inserting Data

@app.route('/add-user', methods=['POST'])
def add_user():
    name  = request.form.get('name')
    email = request.form.get('email')

    conn = get_db()
    conn.execute(
        'INSERT INTO users (name, email) VALUES (?, ?)',
        (name, email)
    )
    conn.commit()
    conn.close()
    return redirect(url_for('user_list'))

The ? placeholders are critical for security. They tell SQLite to treat the values as data, not as SQL commands. Never insert user input directly into a SQL string using string formatting — that creates a SQL injection vulnerability.

UNSAFE:   f"INSERT INTO users VALUES ('{name}')"   ← SQL injection risk
SAFE:     'INSERT INTO users VALUES (?)', (name,)   ← parameterized

Reading Data

@app.route('/users')
def user_list():
    conn = get_db()
    users = conn.execute('SELECT * FROM users ORDER BY name').fetchall()
    conn.close()
    return render_template('users.html', users=users)

In the template:

<ul>
{% for user in users %}
  <li>{{ user['name'] }} — {{ user['email'] }}</li>
{% endfor %}
</ul>

Fetch Methods

Updating and Deleting Data

# Update
conn.execute(
    'UPDATE users SET name = ? WHERE id = ?',
    (new_name, user_id)
)
conn.commit()

# Delete
conn.execute('DELETE FROM users WHERE id = ?', (user_id,))
conn.commit()

Using Flask's Application Context for DB Connections

Opening a new database connection for every request works but is not efficient for production. Flask's application context (g object) stores one connection per request and closes it automatically when the request ends:

from flask import g

def get_db():
    if 'db' not in g:
        g.db = sqlite3.connect('users.db')
        g.db.row_factory = sqlite3.Row
    return g.db

@app.teardown_appcontext
def close_db(error):
    db = g.pop('db', None)
    if db is not None:
        db.close()

Summary

SQLite gives you a full SQL database with zero server setup. The sqlite3 module is built into Python, so no installation is needed. Use parameterized queries (? placeholders) for every value that comes from user input. Store one database connection per request using Flask's g object and close it with teardown_appcontext. For production applications with heavy traffic, switch to PostgreSQL or MySQL — but SQLite is ideal for learning and small projects.