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
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
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?
-
Serverless and simple—SQLite runs inside your application, so there is no need to install or manage a database server.
-
Single-file database—All data is stored in one file, which makes it easy to move, copy, or back up.
-
Fast and lightweight, it is efficient and well-suited for small- to medium-sized applications.
-
Built into Python—Python includes SQLite support through the
sqlite3module, so no extra installation is required. -
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()python flask_ckeditor/database.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)
Comments
Post a Comment