Connect Flask to SQLite

Your Flask app works in memory, but every restart wipes out the data your users just created. That's the pain point this lesson solves: connecting Flask to a SQLite database so your web app can persist data across requests, restarts, and even across different users. By the end, you'll be able to store, query, and modify data in a real database file, all from inside your Flask routes.

The problem this lesson solves

A Flask app with no database is like a notebook with disappearing ink. Every time you restart the server, all the data—users, posts, comments—vanishes. In production, that's unacceptable. Users expect their data to survive. The classic mistake is to store data in a Python list or dictionary, which only lives as long as the process. The solution is a persistent database, and SQLite is the perfect starting point because it's built into Python, requires no separate server, and the entire database is just a single file.

You'll also face concurrency issues: multiple users hitting your app at the same time can corrupt data if you're only using in-memory structures. A proper database handles locking and transactions. This lesson teaches you how to bridge the gap between your Flask routes and a SQLite database, so your app stores and retrieves data reliably.

Core concept / mental model

Think of Flask as the receptionist and SQLite as the filing cabinet. When a user visits your site, Flask takes their request, talks to the filing cabinet (the database) to read or write data, and then sends back a response. The cabinet is a single file on disk (like app.db), and it uses SQL (Structured Query Language) to organize and find data. Your Python code never touches the file directly—it goes through a database driver (the sqlite3 module) that translates your Python calls into SQL commands.

The mental model has three layers:

1. Flask routes — handle HTTP requests and responses.
2. Database connection — a Python object that lets you execute SQL.
3. SQLite database file — where data actually lives.

A key idea is the connection lifecycle. You don't open a connection for the whole app; you open it per request (or per operation) and close it when done. This avoids resource leaks and keeps things clean. The sqlite3 module gives you a connect() function, and from that connection you create a cursor to run queries.

How it works step by step

Let's break down the process of connecting Flask to SQLite into clear stages. These steps form the backbone of every SQLite-backed Flask app you'll write.

1. Import the SQLite module — import sqlite3 at the top of your Python file.
2. Define a database path — a constant like DATABASE = 'app.db' that points to your database file. SQLite creates the file if it doesn't exist.
3. Create a connection — call sqlite3.connect(DATABASE) to get a connection object. This is your gateway to the database.
4. Create a cursor — from the connection, call .cursor() to get a cursor. The cursor executes SQL statements.
5. Execute SQL — use cursor methods like .execute() to run queries (e.g., CREATE TABLE, INSERT).
6. Commit changes — after write operations (INSERT, UPDATE, DELETE), call connection.commit() to save them permanently.
7. Close the connection — always call connection.close() when done to free resources.

For Flask, you typically wrap the connection logic in a helper function that returns a fresh connection for each request. This is important because SQLite connections are thread-local; sharing them across threads (which Flask does) can cause errors like sqlite3.ProgrammingError: SQLite objects created in a thread can only be used in that same thread.

Hands-on walkthrough

Now let's put it into practice with a complete Flask app that connects to SQLite, creates a table, and lets users add and view posts. We'll use a simple Posts model with id, title, and content.

First, create your Flask app with a database connection helper:

# app.py
import sqlite3
from flask import Flask, g, render_template, request, redirect, url_for

DATABASE = 'app.db'
app = Flask(__name__)

# Get a connection, creating the file if needed
def get_db():
    if 'db' not in g:
        g.db = sqlite3.connect(DATABASE)
        g.db.row_factory = sqlite3.Row  # Access columns by name
    return g.db

# Close the connection when the app context ends
@app.teardown_appcontext
def close_db(exc):
    db = g.pop('db', None)
    if db is not None:
        db.close()

# Create our table on startup (just once)
def init_db():
    with sqlite3.connect(DATABASE) as conn:
        conn.execute('''
            CREATE TABLE IF NOT EXISTS posts (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT NOT NULL,
                content TEXT NOT NULL
            )
        ''')

# Run init_db on first request (or we could call it manually)
with app.app_context():
    init_db()

@app.route('/')
def index():
    db = get_db()
    posts = db.execute('SELECT * FROM posts ORDER BY id DESC').fetchall()
    return render_template('index.html', posts=posts)

@app.route('/add', methods=['POST'])
def add_post():
    title = request.form['title']
    content = request.form['content']
    db = get_db()
    db.execute('INSERT INTO posts (title, content) VALUES (?, ?)', (title, content))
    db.commit()
    return redirect(url_for('index'))

Run this app with flask run and you'll have a working database-backed app. The get_db() function uses Flask's g object to store the connection for the current request, ensuring it's available across multiple functions and closed automatically.

Let's also write a quick standalone script to test the database connection independently of Flask:

# test_db.py
import sqlite3

conn = sqlite3.connect('app.db')
cursor = conn.cursor()
cursor.execute('''
    CREATE TABLE IF NOT EXISTS posts (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        content TEXT NOT NULL
    )
''')
cursor.execute("INSERT INTO posts (title, content) VALUES ('Hello', 'First post!')")
conn.commit()

# Query and print
cursor.execute('SELECT * FROM posts')
for row in cursor.fetchall():
    print(row)

conn.close()

Expected output:

(1, 'Hello', 'First post!')

Now, if you run the Flask app and visit http://127.0.0.1:5000/, you'll see the posts. Add a few more via a simple HTML form, and you'll notice they persist even after restarting the server.

Compare options / when to choose what

SQLite is not the only choice, but it's often the right one for small to medium web apps. Here's a comparison with popular alternatives:

When to choose SQLite: - Your app is small to medium, with moderate traffic. - You're prototyping or building a demo. - You want zero configuration and easy backups (just copy the file).

When to choose PostgreSQL/MySQL: - You expect high concurrency (many writes at once). - You need advanced features like JSON, full-text search, or complex queries. - You're deploying to a multi-server environment.

For most learning paths and small projects, SQLite is more than enough. It's also the default choice for Flask tutorials because it's built in and requires no extra setup.

Troubleshooting & edge cases

Let's tackle common issues you'll run into when connecting Flask to SQLite.

1. OperationalError: no such table — You forgot to create the table. Make sure you call init_db() or execute your CREATE TABLE statement before querying. If you're using with app.app_context(): in the main block, double-check the indentation and that your database path is correct.

2. sqlite3.ProgrammingError: SQLite objects created in a thread can only be used in that same thread — This happens when you share a single connection across threads. Flask uses threads for requests, so always open a new connection per request (or use g as shown). Never store a global connection.

3. Database is locked — SQLite locks the entire file during write operations. If you get sqlite3.OperationalError: database is locked, you likely have multiple connections writing simultaneously. Add timeout=10 to your connect() call to wait for the lock to clear.

4. Data not persisting — Forgot to call conn.commit() after INSERT, UPDATE, or DELETE statements. Without commit, changes are rolled back when the connection closes.

5. Using ? placeholder for values — Never use string formatting for SQL values; it's vulnerable to SQL injection. Always use parameterized queries with ? placeholders and pass a tuple of values.

6. g object not found — If you try to access g outside a Flask app context, you'll get a RuntimeError. Ensure your code is inside a Flask route or within with app.app_context(): if you're in a script.

What you learned & what's next

You've learned the core skill of connecting Flask to a SQLite database. You can now:

• Explain the three-layer mental model: Flask routes, database connections, and SQLite file.
• Set up a database connection in Flask using get_db() and g.
• Create tables, insert data, and query records from within Flask routes.
• Handle the connection lifecycle properly to avoid thread errors and data leaks.
• Choose between SQLite and other databases based on your app's needs.

These skills lay the foundation for building full-featured web apps. Next, you'll learn how to structure multi-route apps and handle form data more robustly—so your database becomes the backbone of a truly interactive site.