Python ProgrammingLevel: Intermediatepython

Building a Flask REST API With SQLite: A Worked Example

Prepared by the Assignment Achievers software development team.

Free Educational Resource

Problem Statement / Assignment Scope

Build a small web API in Python with Flask and SQLite that stores `assignments` (title, subject, deadline). It must let a client list all assignments and add a new one, reply in JSON and use sensible HTTP status codes. Show how the same pattern extends to update and delete.

Prerequisites: Basic Python, what JSON looks like, and the common HTTP status codes (200, 201, 400, 404).

Step-by-Step Worked Solution

1. What makes it RESTful

Each kind of data is a resource with its own URL, here /api/assignments, and the HTTP method says what to do with it: GET to read, POST to create, PUT or PATCH to update and DELETE to remove.

2. The parts that earn marks

Database access: setting row_factory = sqlite3.Row lets each row be turned into a dictionary, which jsonify can send straight back.
Checking input first: the POST route rejects a request without a title or subject before it reaches the database, so bad input gets a clear 400 instead of a server error.
Meaningful status codes: 200 for a successful read, 201 Created for a new record and 400 Bad Request for invalid data.
Safe SQL: the ? placeholders pass values separately from the query, which prevents SQL injection.

3. Adding update and delete

Add routes on /api/assignments/<int:assignment_id>: PUT runs an UPDATE and DELETE runs a DELETE, and both return 404 when cursor.rowcount is 0 because no record had that id.

solution.pypython

import sqlite3
from flask import Flask, jsonify, request

app = Flask(__name__)
DATABASE = "coursework.db"


def connect():
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row  # rows behave like dictionaries
    return conn


def create_tables():
    with connect() as conn:
        conn.execute(
            """
            CREATE TABLE IF NOT EXISTS assignments (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT NOT NULL,
                subject TEXT NOT NULL,
                deadline TEXT NOT NULL
            )
            """
        )


@app.get("/api/assignments")
def list_assignments():
    with connect() as conn:
        rows = conn.execute("SELECT * FROM assignments ORDER BY deadline").fetchall()
    return jsonify([dict(row) for row in rows]), 200


@app.post("/api/assignments")
def add_assignment():
    body = request.get_json(silent=True) or {}
    if not body.get("title") or not body.get("subject"):
        return jsonify({"error": "title and subject are required"}), 400

    with connect() as conn:
        cursor = conn.execute(
            "INSERT INTO assignments (title, subject, deadline) VALUES (?, ?, ?)",
            (body["title"], body["subject"], body.get("deadline", "not set")),
        )
    return jsonify({"id": cursor.lastrowid}), 201


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

Key Takeaways & Core Principles

Open and close database connections with `with` blocks so they're always tidied up.
Return the status code that matches what happened: 200, 201, 400 or 404.
Never build SQL by joining strings; use `?` placeholders.
Validate the request body before touching the database.

Frequently Asked Questions

How would I add logins to this API?

A common approach is JSON Web Tokens: issue a token when the user signs in (for example with `Flask-JWT-Extended`) and require an `Authorization: Bearer <token>` header on protected routes.

Academic References & Standards

[1]Pallets Projects. (2024). Flask: A lightweight WSGI web application framework. Source Link

Help With Your Own Task

Got a Similar Question for Your Course?

Send it to an Assignment Achievers expert. You'll get an original answer that follows your brief and rubric, the working shown step by step, and a free originality report.