Flask REST API with SQLite: Complete CRUD Implementation

Step-by-Step Worked Solution

from flask import Flask, request, jsonify
import sqlite3

app = Flask(__name__)
DB_NAME = "academic.db"

def get_db_connection():
    conn = sqlite3.connect(DB_NAME)
    conn.row_factory = sqlite3.Row
    return conn

def init_db():
    with get_db_connection() 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
            )
        ''')
        conn.commit()

@app.route("/api/assignments", methods=["GET"])
def get_all_assignments():
    with get_db_connection() as conn:
        rows = conn.execute("SELECT * FROM assignments").fetchall()
        return jsonify([dict(row) for row in rows]), 200

@app.route("/api/assignments", methods=["POST"])
def create_assignment():
    data = request.get_json()
    if not data or not data.get("title") or not data.get("subject"):
        return jsonify({"error": "Invalid payload: title and subject required"}), 400

    with get_db_connection() as conn:
        cursor = conn.execute(
            "INSERT INTO assignments (title, subject, deadline) VALUES (?, ?, ?)",
            (data["title"], data["subject"], data.get("deadline", "TBD"))
        )
        conn.commit()
        new_id = cursor.lastrowid

    return jsonify({"id": new_id, "message": "Assignment created successfully"}), 201

if __name__ == "__main__":
    init_db()
    app.run(debug=True, port=5000)