How to Connect Flask to SQLite: Simple Guide

To connect Flask to SQLite, use the sqlite3 module to open a database connection inside your Flask app. Typically, you create a connection per request and close it after, using Flask's g object and app.teardown_appcontext to manage it.

Syntax

Here is the basic syntax to connect Flask to SQLite:

• sqlite3.connect('database.db'): Opens a connection to the SQLite database file.
• g: A Flask global object to store the connection during a request.
• app.teardown_appcontext: Closes the connection automatically after the request ends.

python

import sqlite3
from flask import Flask, g

app = Flask(__name__)

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

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

Example

This example shows a simple Flask app that connects to SQLite, creates a table, inserts data, and queries it.

python

import sqlite3
from flask import Flask, g, jsonify

app = Flask(__name__)

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

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

@app.route('/init')
def init_db():
    db = get_db()
    db.execute('CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)')
    db.commit()
    return 'Database initialized!'

@app.route('/add/<name>')
def add_user(name):
    db = get_db()
    db.execute('INSERT INTO users (name) VALUES (?)', (name,))
    db.commit()
    return f'User {name} added.'

@app.route('/users')
def list_users():
    db = get_db()
    users = db.execute('SELECT * FROM users').fetchall()
    return jsonify([dict(user) for user in users])

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

Output

Running the app and visiting /init creates the table.
Visiting /add/Alice adds a user.
Visiting /users returns JSON list of users like [{"id":1,"name":"Alice"}].

Common Pitfalls

Common mistakes when connecting Flask to SQLite include:

• Not closing the database connection, which can cause locked database errors.
• Opening a new connection for every query instead of reusing one per request.
• Not setting row_factory to get dictionary-like rows, making data harder to use.
• Forgetting to commit changes after insert/update/delete operations.

python

import sqlite3
from flask import Flask, g

app = Flask(__name__)

def get_db_wrong():
    # Wrong: opens a new connection every call
    return sqlite3.connect('db.db')

@app.route('/wrong')
def wrong_usage():
    db = get_db_wrong()
    db.execute('CREATE TABLE IF NOT EXISTS test (id INTEGER)')
    # Missing commit and close
    return 'This may cause issues.'

# Correct way uses get_db() with g and closes connection after request

Quick Reference

Tips for connecting Flask to SQLite:

• Use g to store the connection per request.
• Always close the connection with app.teardown_appcontext.
• Set row_factory = sqlite3.Row for easier data handling.
• Commit changes after write operations.
• Use parameterized queries to avoid SQL injection.

Key Takeaways

Use Flask's g object to keep one SQLite connection per request.

Always close the database connection after each request to avoid locks.

Set row_factory to sqlite3.Row for easy access to query results.

Commit your changes after inserts or updates to save them.

Use parameterized queries to keep your app safe from SQL injection.