How to Store Application Data with SQLite in Python?

 

In the previous tutorial, I used TinyDB as our storage solution to keep application data in a simple JSON-based format. While TinyDB is lightweight and easy to use, many web applications require a more structured and scalable database system. In this tutorial, we will switch to SQLite, a powerful relational database that integrates well with Python and web frameworks. By using SQLite, you will learn how to store, manage, and query data in a more structured way for real-world applications.

Prerequisite:

This tutorial is part of the Flask CKEditor Project Series.

📚 View the Complete Flask CKEditor Series

⬅ Previous Part                                                                                                                ➡ Next Part

Preliminary
Before I begin, please activate the virtual environment and install the required dependencies. Since SQLite is part of the Python standard library, it is pre-installed.

python -m venv venv
venv\Scripts\activate 
pip install flask

Then, we need to set up the file and folder structure, as below:

Question 1: What is SQLite?

SQLite is a lightweight, serverless relational database that stores data in a single file on your computer. Unlike traditional database systems, it does not require a separate server to run. Applications can directly read and write to the database file using SQL (Structured Query Language), making it very simple to set up and use.

Question 2: Why use SQLite?

1. Serverless and simple—SQLite runs inside your application, so there is no need to install or manage a database server.

2. Single-file database—All data is stored in one file, which makes it easy to move, copy, or back up.

3. Fast and lightweight, it is efficient and well-suited for small- to medium-sized applications.

4. Built into Python—Python includes SQLite support through the sqlite3 module, so no extra installation is required.

5. SQLite is an excellent tool for development and prototyping, with many developers using it to build and test applications before transitioning to larger databases.

Because of these advantages, SQLite is a popular choice for desktop apps, small web applications, and development projects built with frameworks like Flask.

I built the same app as per part 2, except that instead of being based on a non-schema in TinyDB, it is now based on the schema. In fact, a schema in SQLite defines the structure of a database. It describes how the data is organised, including the tables, columns, data types, and relationships within the database.

Therefore, my database.py effectively describes my schema.

Step 1: Build a database schema

Now I create a schema in my database.py, as below:

# Import the SQLite library
import sqlite3

# Create a connection to the SQLite database
conn = sqlite3.connect('flask_ckeditor/message.db')

# Create a cursor object to execute SQL commands
c = conn.cursor()

# Execute an SQL command to create a table named "message"
c.execute("""CREATE TABLE message (
    id integer primary key autoincrement,
    name text,
    email text,
    subscribe boolean,
    message text
    )""")

# Save the changes into the database
conn.commit()

# Close a connection
conn.close()

I have created 5 columns for the respective input fields, which contain different database types: id as an integer, subscribe as a boolean, and name, email, and message as text, as shown in the above code.

When I have run the following command in the terminal:

python flask_ckeditor/database.py

The blank database was created; please refer to the diagram below.

Step 2: Modify the submit() function in app.py
The submit() function is identical to part 2, except for how it records data in SQLite. Now I will retrieve the input data from the HTML file and insert the values according to the schema format in step 1. The extracted code for submit() is as follows:

from flask import (Flask, render_template, redirect, 
                   url_for)
from form import ContactForm
import sqlite3

@app.route('/message', methods=['GET','POST'])
def submit():
    # Create an instance of the ContactForm
    form = ContactForm()
    
    # Check if the form is submitted and passes validation
    if form.validate_on_submit():
    
        # Retrieve data from each form field
        name = form.name.data
        email = form.email.data
        subscribe = form.subscribe.data
        message = form.message.data
        
        # Create a connection to the SQLite database
        conn = sqlite3.connect("flask_ckeditor/message.db")
        
        # Create a cursor object to execute SQL commands
        c = conn.cursor()
        
        try:
            # SQL query to insert a new record into the "message" table
            query = """INSERT INTO message (name, email, subscribe, message)
               VALUES (:name, :email, :subscribe, :message)""" 
 
            # Create a dictionary containing the data to be inserted
            # The keys must match the placeholders in the SQL query
            my_data = {
                'name': name,
                'email': email,
                'subscribe': subscribe,
                'message': message
                }
         
             # Execute the SQL query and insert the data into the database
            content = c.execute(query, my_data)
            
            # Save (commit) the changes to the database
            conn.commit()   
            
            # Print the ID of the newly inserted record
            print(f"Data Added ID: " + str(content.lastrowid))
        
        # Catch any SQLite errors and display the error message
        except sqlite3.Error as e:
            print(e)
        
        # Redirect the user to the thank you page after successful submission
        return redirect(url_for('thankyou'))
    
    # If form is not submitted or validation fails,
    # re-render the contact page and display validation errors
    return render_template('contact.html', form=form)

When I clicked the submit button:

1) I need to validate all the input fields (data required), such as name, email, subscribe, and message. 

2) After validation, I need to store those input fields in the message.db database. 

3) If the form is accepted and validated, the app will now redirect to the thankyou() function. 

4) Otherwise, it will re-render on the same page.

The record retrieved by SQLite Browser is shown in the diagram below:

Final wrap-up:

In earlier tutorials, I used TinyDB because it is simple and stores data in a JSON file, making it straightforward to build small applications quickly. However, TinyDB has limitations such as weaker querying capabilities, slower performance with large datasets, and limited support for multiple users accessing the database at the same time. Because of these limitations, SQLite is often a better alternative. SQLite provides structured data storage, powerful SQL queries, and better data integrity while still being lightweight and easy to use. It also works seamlessly with Python and frameworks like Flask, making it a practical choice for many web applications.

Published: Mar 2026

Last Updated: Mar 2026

---------------------------------------------------------------------------------------------------------------------------------------------------

🎁 Get Your FREE Flask Cheat Sheet

Join 500+ Python learners and receive this printable Flask Cheat Sheet instantly. Plus, get new Flask tutorials delivered to your inbox.

✓ Practical coding tutorials
✓ Automation tips for SMEs
✓ New project ideas and templates

Subscribe

-----------------------------------------------------------------------------------------------------------------------------

Need a similar system for your business? 

I build custom Flask web applications and Python automation solutions for SMEs and solopreneurs. 

Hire me

-----------------------------------------------------------------------------------------------------------------------------

About the Author 

Kelvin Loh is a Python developer focused on Flask, desktop applications, and business automation solutions. He shares practical tutorials and real-world coding projects to help developers and small businesses build useful applications.

🚀 Place Order Now 🖼️ View Real Projects ❓ Ask a Question First 💰Try the 98% Open Rate Method