Flask Python SQLite Integration Guide

Flask is a lightweight Python web framework. It pairs well with SQLite, a file-based database. Together, they simplify building web apps with local data storage. This article explains how to integrate Flask and SQLite, from setup to basic operations.

Prerequisites

Install Flask and SQLite first. Use pip to install Flask:

pip install Flask

SQLite is included in Python's standard library. Check the Python SQLite install guide for details.

Creating a SQLite Database

Start by creating a database file. Use the connect function to create a connection:

import sqlite3
# Create a database file or connect to it
conn = sqlite3.connect("example.db")
conn.close()

This creates a file named example.db. For advanced setup, see the database creation guide.

Integrating Flask with SQLite

In Flask, manage database connections within app contexts. Use sqlite3.connect inside route handlers:

from flask import Flask
import sqlite3

app = Flask(__name__)

@app.route("/")
def index():
    conn = sqlite3.connect("example.db")
    # Perform database operations here
    conn.close()
    return "Database connected!"

Run the app with flask run. Access the route in your browser to see the output:

Database connected!

Example: Simple CRUD Operations

Create a table and insert data. Use execute to run SQL commands:

@app.route("/init")
def init_db():
    conn = sqlite3.connect("example.db")
    cursor = conn.cursor()
    # Create a table
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS messages (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            content TEXT NOT NULL
        )
    """)
    # Insert a record
    cursor.execute("INSERT INTO messages (content) VALUES (?)", ("Hello Flask!",))
    conn.commit()
    conn.close()
    return "Database initialized!"

Access /init to set up the table and insert data. Then retrieve it:

@app.route("/messages")
def get_messages():
    conn = sqlite3.connect("example.db")
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM messages")
    rows = cursor.fetchall()
    conn.close()
    return str(rows)

Visit /messages to see stored data:

[(1, 'Hello Flask!')]

This example demonstrates basic CRUD operations. For more, check the SQLite example guide.

Handling Data Safely

Use parameterized queries to prevent SQL injection. Never interpolate user input directly into SQL:

# Safe: parameterized query
cursor.execute("SELECT * FROM messages WHERE content = ?", (user_input,))

Learn about query parameters in the paramstyle guide.

Common Issues and Solutions

• Database locked: Ensure all connections are closed after use.
• Missing table: Use CREATE TABLE IF NOT EXISTS to avoid errors.
• Connection leaks: Always close connections in finally blocks.

Conclusion

Flask and SQLite form a powerful duo for lightweight web apps. By mastering connect, execute, and parameterized queries, you can build robust applications. Explore further with advanced topics like transactions and PRAGMA settings. Keep experimenting!