📓3.3: SQLite Databases

Table of Contents

• Databases in Python Web Apps
  • CRUD: Create, Read, Update, Delete
• Tutorial: SQLite + Flask
  • Step 1: Setting Up the Flask Application
  • Step 2: Creating the Database Model
  • Step 3: Creating the Database
  • Step 4: Setting Up Routes
  • Step 5: Creating HTML Templates
• Next Steps: Advanced SQLite
  • Indexing for Performance Optimization
  • Foreign Keys for Relational Data
  • Transactions for Data Integrity

---

Databases in Python Web Apps

What is SQL?

SQL (Structured Query Language) is the standard language for interacting with relational databases. It allows users to create, retrieve, update, and delete data stored in a structured format using tables. SQL databases, such as SQLite, PostgreSQL, and MySQL, are widely used in web applications to ensure efficient data management.

SQLite is a lightweight, serverless SQL database engine that is built into Python and commonly used for small to medium-sized applications.

Unlike other database systems that require a separate server process, SQLite operates as a simple file on disk, making it an excellent choice for local development and small-scale web applications.

CRUD: Create, Read, Update, Delete

💿 CRUD represents the four fundamental operations performed on a database. Each operation corresponds to a SQL statement.

1. Create – Inserting new records into the database.

   SQL: INSERT INTO table_name (column1, column2) VALUES (value1, value2);

2. Read – Retrieving and displaying stored data.

   SQL: SELECT * FROM table_name;

3. Update – Modifying existing records.

   SQL: UPDATE table_name SET column1 = value1 WHERE condition;

4. Delete – Removing records from the database.

   SQL: DELETE FROM table_name WHERE condition;

EXAMPLE: Crud Operations in Python

In a Flask app using SQLAlchemy, these operations translate into Python methods.

# Create
task = Todo(content="Complete Flask tutorial")
db.session.add(task)
db.session.commit()

# Read
tasks = Todo.query.all()

# Update
task = Todo.query.get(1)
task.content = "Updated task"
db.session.commit()

# Delete
db.session.delete(task)
db.session.commit()

---

Tutorial: SQLite + Flask

In this tutorial, we will walk through the process of integrating an SQLite database into a Flask web application. We will use SQLAlchemy, Flask’s ORM (Object-Relational Mapper), to manage the database. By the end of this tutorial, you will have a working to-do list web application that allows users to add and delete tasks, all stored in an SQLite database.

You can install the necessary dependencies using pip:

pip install flask flask-sqlalchemy

Step 1: Setting Up the Flask Application

First, create a new Python file called app.py and import the necessary modules:

from flask import Flask, render_template, request, redirect
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime

Then, initialize the Flask application and configure the SQLite database:

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///tasks.db'
db = SQLAlchemy(app)

Step 2: Creating the Database Model

Next, define a database model that represents a task:

class Todo(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    content = db.Column(db.String(200), nullable=False)
    date_created = db.Column(db.DateTime, default=datetime.utcnow)

    def __repr__(self):
        return f'<Task {self.id}>'

This model defines a Todo table with three columns:

• id: A unique identifier for each task
• content: The task description
• date_created: A timestamp for when the task was added

Step 3: Creating the Database

Before running the application, create the database by adding the following code at the bottom of app.py:

if __name__ == "__main__":
    with app.app_context():
        db.create_all()
    app.run(debug=True)

Run the Python script once to generate tasks.db.

Step 4: Setting Up Routes

Route for Displaying Tasks

Modify app.py to render a template displaying all tasks:

@app.route('/', methods=['GET', 'POST'])
def index():
    if request.method == 'POST':
        task_content = request.form['content']
        new_task = Todo(content=task_content)
        try:
            db.session.add(new_task)
            db.session.commit()
            return redirect('/')
        except:
            return 'There was an issue adding your task'
    
    tasks = Todo.query.order_by(Todo.date_created).all()
    return render_template('index.html', tasks=tasks)

Route for Deleting Tasks

@app.route('/delete/<int:id>')
def delete(id):
    task_to_delete = Todo.query.get_or_404(id)
    try:
        db.session.delete(task_to_delete)
        db.session.commit()
        return redirect('/')
    except:
        return 'There was a problem deleting that task'

Step 5: Creating HTML Templates

Base Template (templates/base.html)

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <link rel="stylesheet" href="">
    {% block head %}{% endblock %}
</head>
<body>
    {% block body %}{% endblock %}
</body>
</html>

Main Page (templates/index.html)

{% extends 'base.html' %}

{% block head %}
<title>Task Master</title>
{% endblock %}

{% block body %}
<div class="content">
    <h1 style="text-align:center;">Task Master</h1>
    <table>
        <tr>
            <th>Task</th>
            <th>Added</th>
            <th>Actions</th>
        </tr>
        {% for task in tasks %}
            <tr>
                <td>{{ task.content }}</td>
                <td>{{ task.date_created.date() }}</td>
                <td>
                    <a href="/delete/{{task.id}}">Delete</a>
                </td>
            </tr>
        {% endfor %}
    </table>
    <div class="form">
        <form action="/" method="POST">
            <input type="text" name="content" id="content" required>
            <input type="submit" value="Add Task">
        </form>
    </div>
</div>
{% endblock %}

---

Next Steps: Advanced SQLite

As your applications grow, you may need to implement advanced SQLite features for better performance and scalability.

Indexing for Performance Optimization

An index is a data structure that improves the speed of data retrieval operations. Without an index, a database must scan every row to find matching records, which can be slow for large datasets.

Example: Creating an Index

CREATE INDEX idx_task_content ON Todo(content);

This index allows faster lookups when searching for tasks by content.

Foreign Keys for Relational Data

Foreign keys enforce relationships between tables, ensuring data integrity. They prevent orphaned records and help structure data logically.

Example: Defining Foreign Keys in SQLAlchemy

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)

class Task(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    content = db.Column(db.String(200), nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    user = db.relationship('User', backref=db.backref('tasks', lazy=True))

Here, each task is associated with a specific user, ensuring structured data relationships.

Transactions for Data Integrity

A database transaction is a sequence of operations that must all be executed successfully or none at all. This ensures consistency in case of failures.

Example: Using Transactions in SQLAlchemy

try:
    db.session.begin()
    db.session.add(task1)
    db.session.add(task2)
    db.session.commit()
except:
    db.session.rollback()

If an error occurs while adding task1 or task2, the rollback prevents partial data corruption.

---

Acknowledgement

Content on this page is adapted from .