Building a Blog with Flask and SQLAlchemy

Suppose your class teacher asks you to set up a digital notice board for Class 8-C — something where any of the 40 students can post an announcement ("Maths test postponed to Friday", "Bring cricket kit for sports day"), and everyone else can open it on their phone and read the latest notices. You open a code editor and write a Python program that keeps a list of notices in memory and prints them on a webpage. It works beautifully all afternoon. Then the school's WiFi router restarts at 6 PM for its nightly reboot, your program restarts along with it, and every single notice is gone. The list is empty again, as if nothing was ever posted.

This is not a rare bug — it is one of the most common mistakes made by anyone learning to build websites, and understanding why it happens, and how to fix it properly, is exactly what this chapter is about. We will build a real, working blog using two tools that professional web developers use every day: Flask, a Python framework that turns your code into a website, and SQLAlchemy, a library that lets your Python code talk to a database so that data survives restarts, crashes, and power cuts. By the end, you will understand not just how to copy code that works, but exactly why each line is necessary.

What a website actually is: request and response

Before writing any Flask code, it helps to be precise about what happens when you type a web address into a browser. It is easy to imagine a website as a fixed file sitting somewhere, like a photo in a folder. Some websites are exactly that — a plain HTML file sitting on a server, sent as-is to anyone who asks. But a blog is different: the page you see must change every time someone posts something new, so it cannot be a fixed file. Instead, a program runs on the server, and every time your browser asks for a page, that program decides what to send back, often by first checking a database.

This exchange has a name: the browser sends a request ("give me the homepage"), and the server computes and sends back a response (the HTML for that homepage, built fresh, right now, using whatever notices currently exist). Flask's entire job is to be the program on the server side that receives requests and decides what responses to send. Nothing more, nothing less.

Meeting Flask: routes are a switchboard

A Flask application is, at its simplest, a mapping from URL paths to Python functions. When a request arrives for a particular path, Flask calls the matching function and sends its return value back to the browser as the response. Here is the smallest possible Flask app:

from flask import Flask

app = Flask(__name__)

@app.route("/")
def home():
    return "<h1>Welcome to the Class 8-C Notice Board</h1>"

if __name__ == "__main__":
    app.run(debug=True)

Trace through this exactly as Flask would. Flask(__name__) creates an application object and registers the name of the current file with it (this is how Flask locates other files, like templates, later). The line @app.route("/") is a decorator — think of it as a sticky note attached to the home function that tells Flask: "whenever a request comes in for the path /, run this function and send back whatever it returns." When you run this file and open http://127.0.0.1:5000/ in a browser, three things happen in order: the browser sends a GET request for path /; Flask looks through its registered routes, finds a match, and calls home(); the returned string is sent back as the HTTP response body, which the browser renders as a heading. Change the path in your browser to /xyz and Flask will reply with a 404 "Not Found" error, because no function is registered for that path — this is a genuinely common beginner confusion, so it is worth stating clearly: a Flask route only responds to the exact path (or pattern) written inside @app.route(...).

Templates: separating structure from logic

Returning raw HTML strings from Python functions works for one line, but a real page has dozens of lines of HTML, and mixing that much markup into Python quickly becomes unreadable. Flask solves this with a templating engine called Jinja2. You place HTML files inside a folder named templates, and Flask fills in the blanks marked with double curly braces before sending the page. For instance, a file templates/home.html might read:

<h1>Class 8-C Notice Board</h1>
<p>There are {{ count }} notices today.</p>

and the Flask function would render it with:

from flask import Flask, render_template

app = Flask(__name__)

@app.route("/")
def home():
    return render_template("home.html", count=5)

render_template opens home.html, finds {{ count }}, replaces it with the value 5 that we passed in as a keyword argument, and returns the finished HTML as a string. This is the same idea as a mail-merge letter, where "Dear {{ name }}" gets filled in differently for each recipient — except here Flask is doing the merging, and the data can be anything: a number, a piece of text, or, as we will soon see, an entire list of blog posts pulled from a database.

Why an ordinary Python list is not good enough

Let's go back to the notice board and try storing notices in a plain Python list, since that seems like the obvious first attempt:

notices = []

@app.route("/add/<text>")
def add(text):
    notices.append(text)
    return f"Added: {text}"

@app.route("/")
def home():
    return f"<p>{len(notices)} notices so far</p>"

This runs fine while the program is alive. But notices is a Python variable, which means it lives in your computer's RAM (working memory). RAM is fast, but it is wiped clean the instant the program stops — whether that's because you pressed Ctrl+C, the server restarted, or the laptop lost power. A blog where every post vanishes on restart is not a blog at all. What we actually need is storage that survives after the program that created it has stopped running: storage on disk, in a proper structured form that can be searched, sorted, and updated reliably. That structured, disk-based storage is called a database.

What a database really is: rows in a register

You have almost certainly seen a school attendance register: a bound notebook, ruled into a table, where each row is one student and each column holds one fact about them (roll number, name, present or absent). A database table works on exactly this idea, formalized. A table is a named collection of rows, where every row has the same fixed set of columns, and every column has a declared type of data it will hold (a whole number, a short piece of text, a date, and so on). For our blog, we need a table called post, where each row is one blog post, with columns for an ID number, a title, the body text, and the date it was written. Unlike a Python list sitting in RAM, this table is written to a file on disk — a single file, in our case, since we will use SQLite, the simplest disk-based database, which needs no separate server program to run.

SQLAlchemy: teaching Python classes to become database tables

You could talk to a database by writing raw SQL (Structured Query Language) commands like INSERT INTO post (title, content) VALUES ('Test postponed', 'See you Friday'). That works, but it means writing string-based SQL scattered through your Python code, which is easy to get subtly wrong and hard to keep organized as the app grows. SQLAlchemy solves this with a technique called an ORM — an Object-Relational Mapper. The idea: you define an ordinary-looking Python class, and SQLAlchemy automatically creates a matching database table, where each attribute of the class becomes a column, and each object you create becomes a row. You then read and write rows using plain Python — creating objects, setting attributes, calling methods — while SQLAlchemy translates all of that into the correct SQL behind the scenes.

Here is the model for our blog post, together with the setup that connects it to an actual SQLite file:

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

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

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100), nullable=False)
    content = db.Column(db.Text, nullable=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    def __repr__(self):
        return f"<Post {self.id}: {self.title}>"

with app.app_context():
    db.create_all()

Read this line by line, because every line is doing real work. app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///blog.db" tells SQLAlchemy exactly which database file to use — here, a file named blog.db sitting next to your script. db = SQLAlchemy(app) creates the connector object that links Flask and SQLAlchemy together. The class Post(db.Model) inherits from SQLAlchemy's base model class, which is what makes it become a table rather than an ordinary class — the table's name defaults to post (the lowercase class name). Each db.Column(...) line declares one column: id is a whole number and the primary key, meaning SQLAlchemy will auto-generate a unique number (1, 2, 3, …) for every new row, which is how each post gets its own permanent, never-reused identity. title is text up to 100 characters and nullable=False means the database will refuse to save a post with a blank title. content uses db.Text, for text of unbounded length, since a post body can be long. created_at is a date-and-time column with default=datetime.utcnow.

That last detail is worth pausing on, because it hides a genuine and common misconception. Notice that we wrote datetime.utcnow — the function itself — and not datetime.utcnow() with parentheses. If we had written it with parentheses, Python would call the function once, at the moment the class is defined (when the program starts), and every single post ever created would be stamped with that same original startup time. By passing the function itself, without calling it, we are telling SQLAlchemy "call this function fresh, every single time a new row is inserted" — so each post correctly gets its own actual creation time. This one pair of parentheses is a classic place where beginners introduce a silent, hard-to-notice bug.

Finally, db.create_all() is what actually creates the post table inside blog.db if it does not already exist — it reads the Post class definition and issues the equivalent of a SQL CREATE TABLE command. It is wrapped in with app.app_context(): because SQLAlchemy needs to know which Flask application it is working for whenever it touches the database, and outside of an active request, you must open that context yourself.

Creating posts: the full request lifecycle

Now we add a route that lets a user submit a new notice through an HTML form, and trace precisely what happens when it runs:

@app.route("/new", methods=["GET", "POST"])
def new_post():
    if request.method == "POST":
        title = request.form["title"]
        content = request.form["content"]
        post = Post(title=title, content=content)
        db.session.add(post)
        db.session.commit()
        return redirect(url_for("home"))
    return render_template("new_post.html")

This single function handles two different situations, distinguished by request.method. When the browser first opens /new, that is a GET request — the user just wants to see the empty form — so the function falls through to the last line and renders new_post.html, a page containing an HTML <form> with input fields named title and content. When the user fills that form and clicks submit, the browser sends a POST request, this time carrying the typed data along with it. Inside the if block, request.form["title"] and request.form["content"] pull those two values out of the submitted form data, exactly like reading two entries out of a dictionary. post = Post(title=title, content=content) creates a plain Python object in memory — at this exact instant, nothing has touched the database yet. db.session.add(post) only stages the object, telling SQLAlchemy "remember to save this one" — still nothing is written to disk. Only db.session.commit() actually sends an SQL INSERT statement to the database file and permanently saves the row; it is also the moment the auto-incrementing id gets assigned to the object. Finally, redirect(url_for("home")) sends the browser a fresh instruction to go load the homepage, which is why after posting a notice you land back on the notice list instead of staring at a blank confirmation page.

The misconception worth naming explicitly here: many beginners assume that creating a Post(...) object automatically saves it, since after all, "I made a Post, so it must be in the database." It is not. A newly created object is exactly like writing a notice on a loose sheet of paper — it only becomes permanent once you both place it into the register (add) and the register is officially signed and closed for the day (commit). Skip commit(), and the "sheet of paper" is discarded the moment the program ends, with no error message at all — which makes this a particularly sneaky bug to track down.

Reading posts: turning rows back into a webpage

The homepage needs to fetch every saved post and list it, newest first:

@app.route("/")
def home():
    all_posts = Post.query.order_by(Post.created_at.desc()).all()
    return render_template("home.html", posts=all_posts)

Post.query is SQLAlchemy's starting point for asking questions about the post table. .order_by(Post.created_at.desc()) asks for the rows sorted by creation time in descending order (newest first) — behind the scenes this becomes a SQL ORDER BY created_at DESC clause. .all() actually executes the query and returns the matching rows, not as raw table data, but as a Python list of Post objects — SQLAlchemy has converted each database row back into an object with .id, .title, .content, and .created_at attributes you can use directly in Python. The template can now loop over this list:

<h1>Class 8-C Notice Board</h1>
<ul>
{% for post in posts %}
  <li>
    <a href="{{ url_for('view_post', post_id=post.id) }}">{{ post.title }}</a>
  </li>
{% endfor %}
</ul>
<a href="{{ url_for('new_post') }}">+ New Notice</a>

The {% for %} block is Jinja2's loop syntax (curly-brace-percent, rather than double curly braces, marks logic rather than a value to print), and it runs once per Post object in the list, printing a clickable link for each one. url_for('view_post', post_id=post.id) generates the correct URL for viewing that specific post without us ever having to hand-write it — if we later change the URL structure of the app, every link generated this way updates automatically, because it is built from the route's function name and its declared arguments rather than a hardcoded string.

Viewing one post, and deleting one

To view a single post, we need a route that accepts a variable part of the URL, and a query that fetches exactly one matching row:

@app.route("/post/<int:post_id>")
def view_post(post_id):
    post = Post.query.get_or_404(post_id)
    return render_template("post.html", post=post)

@app.route("/delete/<int:post_id>")
def delete_post(post_id):
    post = Post.query.get_or_404(post_id)
    db.session.delete(post)
    db.session.commit()
    return redirect(url_for("home"))

The <int:post_id> segment inside the route path is a converter: Flask extracts whatever number appears in that position of the URL, converts it from text to an actual Python integer, and passes it into the function as the argument post_id. So a request for /post/7 calls view_post(7). Post.query.get_or_404(post_id) looks up the row whose primary key equals that number; if such a row exists it is returned as a Post object, and if it does not exist (say, someone requests /post/999 and there is no such post), Flask automatically sends back a proper 404 "Not Found" page instead of crashing — this single method saves you from writing a manual existence check on every route. Deleting follows the identical add-then-commit discipline in reverse: db.session.delete(post) stages the removal, and db.session.commit() is what actually removes the row from disk.

Seeing the whole system at once

The diagram below lays out the complete path a request takes, and shows concretely how one Post object in Python corresponds to one row in the post table.

Why this matters beyond one classroom project

The pattern in this chapter — a route receives a request, an ORM class stages and commits changes, a template renders the result — is the same pattern behind far larger systems you already use. When you check your train's PNR status on IRCTC, a server-side program queries a database table of bookings and renders the result as a webpage, in essentially the same three steps. When a UPI app shows your last ten transactions, it is running a query very similar to Post.query.order_by(...).all() against a transactions table, not recalculating anything from scratch. Recognizing this shared shape is more valuable than memorizing any one line of Flask syntax, because it means you can read the architecture of almost any interactive website once you understand routes, models, and the add-then-commit discipline.

It is also worth being precise about where this sits relative to your CBSE Computer Science or Informatics Practices syllabus. Later CBSE coursework introduces connecting Python programs directly to a database (commonly through SQL commands executed from Python), and the underlying ideas — tables, rows, columns, primary keys, and the distinction between a query and a commit — are exactly the ideas this chapter has built from first principles, just approached here through the more modern and more forgiving ORM style that real companies use, rather than hand-written SQL strings. Getting comfortable with the vocabulary now (table, row, primary key, query, commit, route) means that vocabulary will not be new to you later; only the specific syntax will differ.

Check your understanding

1. A student writes post = Post(title="Sports Day", content="Saturday 9 AM") and then immediately checks blog.db using a database viewer, expecting to see the new row. It is not there. What is missing from their code, and why does Post(...) alone not save anything?
2. Explain, using the register analogy, the difference between db.session.add(post) and db.session.commit(). Why does SQLAlchemy split saving into these two separate steps instead of one?
3. In the model definition, why is default=datetime.utcnow written without parentheses? What would go wrong, concretely, if someone wrote default=datetime.utcnow() instead?
4. A route is defined as @app.route("/post/<int:post_id>"). What response would Flask give for a request to /post/12 if no post with id 12 exists, and which line of code in view_post is responsible for that behaviour?
5. Why can a Python list (like notices = []) never be a safe permanent storage method for a real website, no matter how carefully it is coded?
6. In Post.query.order_by(Post.created_at.desc()).all(), identify which part chooses the sort order, which part chooses descending direction, and which part actually triggers the database to run the query.

Summary

• A web app is a server-side program that receives a request and computes a fresh response; Flask's @app.route decorators map URL paths to the Python functions that build those responses.
• Jinja2 templates, rendered with render_template, keep HTML structure separate from Python logic, filling in {{ variable }} placeholders and looping with {% for %}.
• Data stored only in a Python variable lives in RAM and is lost whenever the program stops; a database stores data on disk, in tables made of rows and columns, so it survives restarts.
• SQLAlchemy is an ORM: a Python class inheriting from db.Model becomes a table, its db.Column attributes become columns, and each object you create and save becomes one row.
• Saving is always two steps: db.session.add(obj) stages the change, and db.session.commit() actually writes it to disk — creating an object alone never saves it.
• Reading uses Model.query with methods like .all(), .order_by(), .get_or_404(), each of which SQLAlchemy translates into the appropriate SQL statement automatically.