Flask SQLite: Integrating SQLite with Flask

Have you ever wanted to build a simple web application with Python that needs to store and query data but don’t want to deal with setting up a complex database server? Looking for a lightweight persistence layer for your Flask app? SQLite is the perfect solution!

SQLite is a self-contained, file-based, open-source SQL database engine that provides an easy way to store and access structured data without needing to run a separate database process. And Flask, the popular Python web framework, works seamlessly with SQLite, allowing you to integrate a database into your web apps with minimal fuss.

In this comprehensive guide, you’ll learn step-by-step how to:

• Set up a SQLite database file and tables from scratch in Flask
• Perform common CRUD (create, read, update, delete) operations
• Integrate the dataset across route handlers and views
• Query the database using raw SQL or the Flask-SQLAlchemy ORM
• Deploy your Flask app with the embedded SQLite database

So if you need localized data persistence for your next modest Flask project, read on to master integrating SQLite for simple yet powerful data storage and retrieval!

Creating a SQLite Database with Flask

Getting started with SQLite in Flask takes just a few simple steps:

1. Import sqlite3 into your Flask app script. This will allow Python to interface with SQLite databases.
2. Create the database connection. This will represent the actual SQLite file storing data.

Here is a sample code to create and connect to a SQLite database named data.db:

import sqlite3 
import flask

app = flask.Flask(__name__)

# Connect to SQLite database
conn = sqlite3.connect('data.db')

And that’s it! With two lines of code, you now have a fully functional SQLite database set up that your Flask app can interact with.

The connection object conn represents the database and can be used to run SQL queries and commands later to create tables, insert data etc.

Let’s move on to creating a table next.

Also read: The SQLite Handbook: A Start-to-Finish Resource for Learning and Using SQLite

Creating Tables in SQLite from Flask

Before storing retrievable data, we need to define tables to structure the data set.

Here is an example script that creates a simple users table with columns for id, name, age and location:

import sqlite3
import flask

app = flask.Flask(__name__)

conn = sqlite3.connect('data.db') 

# Create users table
conn.execute('''CREATE TABLE users
         (id INTEGER PRIMARY KEY AUTOINCREMENT,
         name TEXT NOT NULL,
         age INTEGER NOT NULL,
         location TEXT NOT NULL);''')

print('Users table created successfully!')

We use the SQLite CREATE TABLE statement to define column names, data types, constraints etc.

• INTEGER PRIMARY KEY AUTOINCREMENT is a special column that assigns a unique integer to increment for each record inserted automatically. This will be used later to identify each user uniquely.
• NOT NULL constraint ensures those fields are always populated.

You can confirm table creation by checking data.db file or querying in the Python shell:

>>> conn.execute("SELECT name FROM sqlite_master WHERE type='table';").fetchall() 
[('users')]

Now that the underlying structure is ready let’s populate the database!

Inserting Sample Data into the SQLite Database

With our users table created, we can start adding records by using the INSERT statement.

Add some sample users:

new_users = [
    ("John", 32, "New York"), 
    ("Sarah", 28, "Chicago"),
    ("Mike", 40, "San Francisco")  
]

insert_query = """INSERT INTO users (name, age, location) 
                  VALUES (?, ?, ?)"""

conn.executemany(insert_query, new_users) 
conn.commit() 

print('Sample users inserted!')

Here’s what’s happening above:

• We first created data for 3 sample users with name, age, and location. This will be a typical pattern – prepare a list of data you want to insert and iterate through it.
• We parameterize the INSERT query using question marks to represent input values
• executemany() runs the parameterized query over the list of user data efficiently in one step.
• commit() commits the transaction.

You can verify successful inserts by:

for row in conn.execute("SELECT * FROM users"):
    print(row)

# Output
(1, 'John', 32, 'New York')  
(2, 'Sarah', 28, 'Chicago')
(3, 'Mike', 40, 'San Francisco')

And that concludes the basics of getting an SQLite database set up and populated with starter data!

Now let’s shift gears…how do we tie this dataset into our Flask application code?

Integrating SQLite Dataset with Flask Views and Routes

A key reason to use SQLite with Flask is to connect certain routes and views in your application to relevant data being stored or manipulated.

For example, you may have a /users route that maps to displaying all user records. Or a /user/add route that maps to an input form and handler for inserting new users submitted from that form.

Here is some sample code for routes that query users and allow adding a user:

@app.route("/users")
def get_users():
    sql = "SELECT * FROM users"  
    rows = conn.execute(sql).fetchall()
    
    return render_template("users.html", users=rows) # Template rendering users
    
@app.route("/add_user", methods=["POST"])
def add_user():
    new_user = (request.form["name"], 
                request.form["age"], 
                request.form["location"])
                
    insert_sql = """INSERT INTO users (name, age, location) 
                  VALUES (?, ?, ?)"""
                  
    conn.execute(insert_sql, new_user)
    conn.commit()  
    
    return redirect("/users") # Redirect to users list with new user added

The key steps are:

• Querying data relevant to that route – e.g. SELECT all users
• Inserting submitted data into relevant tables
• Redirecting or rendering a template to display the updated data

This connects the SQLite records and tables to actual application pages and workflows!

Now let’s discuss some specifics around executing queries.

Querying SQLite Database in Flask

When working with an SQLite database in Flask, you’ll need to run various types of queries:

• SELECT – For retrieving records
• INSERT – For adding new rows of data
• UPDATE – For modifying existing data
• DELETE – For removing records

And in Python, there are two options to run these queries:

1. Raw SQL statements – You can write and execute SQL statements directly on the connection. We’ve seen this previously with SELECT, INSERT, etc. The benefit is you can fully leverage complex SQL logic.
2. Flask-SQLAlchemy ORM – This popular Flask extension provides an Object Relational Mapper for abstracting queries into Python without needing to write raw SQL. It can simplify your database interactions using Python classes and objects rather than tables and rows.

For example, here is how you might SELECT multiple users differently with raw SQL vs ORM:

Raw SQL:

sql = "SELECT * FROM users WHERE age > 30"
rows = conn.execute(sql) 
for row in rows:
   print(row)  

Flask-SQLAlchemy ORM:

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String) 
    age = db.Column(db.Integer)
    location = db.Column(db.String)

users = User.query.filter(User.age > 30).all()  
for user in users:
    print(user.name, user.age) 

So depending on your style, you can use raw SQL or a Pythonic ORM abstraction on top of it.

Now that we have a good handle on SQLite operations within Flask, how about we put this to work in a real-world example?

Real-World Example: Analytics Dashboard with SQLite

Let’s illustrate a realistic use case of Flask + SQLite together – building an analytics dashboard for tracking website traffic.

We will:

• Create tables to store metrics like daily visitors, etc
• Populate sample analytics data
• Build a Flask dashboard with routes to view data
• Use charts to visualize traffic

Here is the schema for two tables:

visits

locations

And sample dashboard routes:

@app.route('/')
def overview():
    # Show overview stats
    
@app.route('/locations') 
def locations():
    # Show visitors by location
 
@app.route('/history)
def history():
    # Plot visit history 

We can reuse the concepts we’ve covered, like database connection, CREATE TABLE, INSERT statements, etc. to build this out.

The result is an ability to slice and visualize web traffic and metrics empowered by a SQLite data store!

Comparison of Raw SQL vs SQLAlchemy ORM

Here is a helpful comparison when using raw SQL statements vs Flask-SQLAlchemy for working with SQLite:

As you can see, there are good reasons for either approach. Ultimately it comes down to your specific app needs.

Hopefully, this gives you a feature comparison to decide what works for your use case.

Summary

We’ve covered end-to-end how to work with an embedded SQLite database in Flask for storing, querying, and visualizing application data:

• Connect a SQLite file to start working with data
• Create tables and schema to model your data sets
• Insert sample data or production data for your application
• Query using raw SQL or Flask-SQLAlchemy for flexible data access
• Integrate SQLite datasets across Flask views and routes
• Build applications like analytics dashboards powered by SQLite data

By tapping SQLite for lightweight data persistence bundled with Python, you unlock simple yet powerful data storage, manipulation and analytics for your next Flask web application!

Give SQLite in Flask a try for your next modest-sized project that calls for data persistence and integration!