Build a CRUD App with Flask & SQLite 🐍

A Beginner's Guide to Creating a Simple Blog Manager from a Single File.

Lightweight, Powerful, and Pythonic! ✨

Flask is a "micro" web framework for Python, meaning it provides the essentials for web development without imposing a rigid structure. It's perfect for learning and for building everything from small projects to large, complex applications.

In this tutorial, we will build a complete **Blog Post Manager** that demonstrates the four fundamental **CRUD** operations: **C**reate, **R**ead, **U**pdate, and **D**elete posts using Flask and Python's built-in SQLite database engine.

Step 0: Prerequisites & Goal 🎯

Project Goal

To create a web application where a user can view a list of blog posts, add new posts, edit existing ones, and delete them.

What You'll Need

Python and Pip installed on your system.

Step 1: Setting Up Your Flask Project 🏗️

Let's get our project folder and dependencies ready.

1. Create Project Structure

Create a new folder for your project. Inside it, create a file named `app.py` and a folder named `templates`.

mkdir flask_blog
cd flask_blog
touch app.py
mkdir templates

2. Install Flask

It's highly recommended to use a virtual environment. Open your terminal in the `flask_blog` folder and run:

# Create and activate a virtual environment (optional but recommended)
python -m venv venv
source venv/bin/activate  # On Windows, use `venv\Scripts\activate`

# Install Flask
pip install Flask

Step 2: The Database (Schema & Initialization) 💾

We'll use a separate script to create our database and the `posts` table. This is a clean way to handle one-time setup tasks.

Create a new file named `init_db.py` in your project folder.

# init_db.py
import sqlite3

connection = sqlite3.connect('database.db')

with open('schema.sql') as f:
    connection.executescript(f.read())

connection.commit()
connection.close()

This script requires a file named `schema.sql`. Create `schema.sql` and add the SQL to define our table:

-- schema.sql
DROP TABLE IF EXISTS posts;

CREATE TABLE posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    title TEXT NOT NULL,
    content TEXT NOT NULL
);

Now, run the script from your terminal to create the `database.db` file:

python init_db.py

Step 3: The Main Flask Application (`app.py`) 🚀

This is where all our application logic will live. We will build this file up, section by section. Here is the initial setup and the "Read" functionality.

Open `app.py` and add the following code:

import sqlite3
from flask import Flask, render_template, request, url_for, flash, redirect
from werkzeug.exceptions import abort

app = Flask(__name__)
app.config['SECRET_KEY'] = 'your secret key' # Change this for production

# Function to get a database connection.
# This function will be used by all our routes.
def get_db_connection():
    conn = sqlite3.connect('database.db')
    conn.row_factory = sqlite3.Row # This allows us to access columns by name
    return conn

# Function to get a single post by its ID
def get_post(post_id):
    conn = get_db_connection()
    post = conn.execute('SELECT * FROM posts WHERE id = ?', (post_id,)).fetchone()
    conn.close()
    if post is None:
        abort(404) # Abort with a 404 Not Found error if post doesn't exist
    return post

# --- Routes ---

@app.route('/')
def index():
    conn = get_db_connection()
    posts = conn.execute('SELECT * FROM posts ORDER BY created DESC').fetchall()
    conn.close()
    return render_template('index.html', posts=posts)

# ... We will add more routes for Create, Update, and Delete below ...

Key Concept: `sqlite3.Row`. By setting the connection's `row_factory`, the database cursor will return rows that behave like dictionaries. This means we can access data like `post['title']` instead of `post[2]`, making our code much more readable.

Step 4: The Views (HTML Templates) 🖥️

Now let's create the HTML files in the `templates` folder that our Flask app will render.

1. `base.html` (The Main Layout)

This file contains the common HTML structure that all other pages will extend.

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>{% block title %} {% endblock %} - FlaskBlog</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
    <nav class="navbar navbar-expand-lg navbar-dark bg-dark">
        <div class="container">
            <a class="navbar-brand" href="{{ url_for('index') }}">FlaskBlog</a>
        </div>
    </nav>
    <div class="container mt-4">
        {% for message in get_flashed_messages() %}
            <div class="alert alert-success">{{ message }}</div>
        {% endfor %}
        {% block content %} {% endblock %}
    </div>
</body>
</html>

2. `index.html` (The Blog List)

This template displays all the posts.

{% extends 'base.html' %}
{% block title %}Welcome{% endblock %}

{% block content %}
    <h1>Blog Posts</h1>
    <a href="{{ url_for('create') }}" class="btn btn-primary mb-3">Create New Post</a>
    
    {% for post in posts %}
        <div class="card mb-3">
            <div class="card-body">
                <h2>
                    <a href="{{ url_for('edit', id=post['id']) }}">
                        {{ post['title'] }}
                    </a>
                </h2>
                <span class="text-muted">{{ post['created'] }}</span>
            </div>
        </div>
    {% endfor %}
{% endblock %}

Step 5: Implementing Create, Update, and Delete ➕🔄🗑️

Now let's add the routes and templates for the remaining CRUD operations.

1. Add the Routes to `app.py`

Append the following routes to your `app.py` file.

@app.route('/create', methods=('GET', 'POST'))
def create():
    if request.method == 'POST':
        title = request.form['title']
        content = request.form['content']

        if not title:
            flash('Title is required!')
        else:
            conn = get_db_connection()
            conn.execute('INSERT INTO posts (title, content) VALUES (?, ?)', (title, content))
            conn.commit()
            conn.close()
            flash('"{}" was successfully created!'.format(title))
            return redirect(url_for('index'))
            
    return render_template('create.html')

@app.route('/<int:id>/edit', methods=('GET', 'POST'))
def edit(id):
    post = get_post(id)

    if request.method == 'POST':
        title = request.form['title']
        content = request.form['content']

        if not title:
            flash('Title is required!')
        else:
            conn = get_db_connection()
            conn.execute('UPDATE posts SET title = ?, content = ? WHERE id = ?', (title, content, id))
            conn.commit()
            conn.close()
            flash('"{}" was successfully updated!'.format(title))
            return redirect(url_for('index'))

    return render_template('edit.html', post=post)

@app.route('/<int:id>/delete', methods=('POST',))
def delete(id):
    post = get_post(id)
    conn = get_db_connection()
    conn.execute('DELETE FROM posts WHERE id = ?', (id,))
    conn.commit()
    conn.close()
    flash('"{}" was successfully deleted!'.format(post['title']))
    return redirect(url_for('index'))

2. Create the `create.html` Template

{% extends 'base.html' %}
{% block title %}Create a New Post{% endblock %}

{% block content %}
    <h1>Create a New Post</h1>
    <form method="post">
        <div class="mb-3">
            <label for="title" class="form-label">Title</label>
            <input type="text" name="title" class="form-control">
        </div>
        <div class="mb-3">
            <label for="content" class="form-label">Content</label>
            <textarea name="content" class="form-control" rows="5"></textarea>
        </div>
        <button type="submit" class="btn btn-primary">Submit</button>
    </form>
{% endblock %}

3. Create the `edit.html` Template

{% extends 'base.html' %}
{% block title %}Edit "{{ post['title'] }}"{% endblock %}

{% block content %}
    <h1>Edit "{{ post['title'] }}"</h1>
    <form method="post">
        <div class="mb-3">
            <label for="title" class="form-label">Title</label>
            <input type="text" name="title" class="form-control" value="{{ request.form['title'] or post['title'] }}">
        </div>
        <div class="mb-3">
            <label for="content" class="form-label">Content</label>
            <textarea name="content" class="form-control" rows="5">{{ request.form['content'] or post['content'] }}</textarea>
        </div>
        <button type="submit" class="btn btn-primary">Submit</button>
        <hr>
        <form action="{{ url_for('delete', id=post['id']) }}" method="post">
            <input type="submit" value="Delete Post" class="btn btn-danger" onclick="return confirm('Are you sure you want to delete this post?')">
        </form>
    </form>
{% endblock %}

Step 6: Run The Application! 🏁

Your application is now complete! To run it, go to your terminal (make sure your virtual environment is activated) and use the `flask` command.

# The --debug flag enables auto-reloading when you save changes
flask --app app run --debug

Now, open your web browser and navigate to the address provided (usually http://127.0.0.1:5000).

http://127.0.0.1:5000

You should see your blog application. You can now create new posts, see them on the main page, click on them to edit, and delete them from the edit page.

Congratulations! You've Built a Flask CRUD Application! 🎉

You have successfully created a complete, database-driven web application using only Flask and Python's standard libraries. This is a powerful demonstration of how quickly you can build functional web apps with Flask.

You learned how to:

Set up a basic Flask project and database schema.
Connect to and query an SQLite database from your routes.
Handle GET and POST requests for **CRUD** operations.
Use **Jinja2** templates to display dynamic data and create forms.
Use `url_for()` for robust routing and `flash()` for user feedback.

This project is a solid foundation. You can now explore adding features like user authentication, comments, or using a more advanced database tool like SQLAlchemy.