My project 4 : Building a REST API (with Flask + SQLite)

[MyrinNew]

🗂️ Building a REST API with Flask + SQLite (Full CRUD Example)


For this project, I wanted to explore the fundamentals of backend API development —
specifically, how CRUD operations (Create, Read, Update, Delete) work behind the scenes.



This project implements a simple REST API using Flask + SQLite,
and exposes endpoints to manage items with id, name, and price.
It’s a small project, but it clearly shows how real-world APIs communicate with databases.






📂 1. Project Structure



flask_rest_api/
│── app.py
└── items.db (automatically created)





🧠 2. What This API Can Do

This little server supports full CRUD operations:

• GET /items → Retrieve all items
• GET /items/ → Retrieve one item
• POST /items → Create a new item
• PUT /items/ → Update an item
• DELETE /items/ → Delete an item

All responses are returned as JSON, making it perfect for frontend apps or mobile apps to consume.





🖥️ 3. Full Backend Code (app.py)





from flask import Flask, request, jsonify
import sqlite3

app = Flask(__name__)

# ---------- DB Initialization ----------
def init_db():
conn = sqlite3.connect("items.db")
cur = conn.cursor()
cur.execute("""
CREATE TABLE IF NOT EXISTS items (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
price REAL NOT NULL
)
""")
conn.commit()
conn.close()

init_db()

# ---------- GET: Retrieve all items ----------
@app.route("/items", methods=["GET"])
def get_items():
conn = sqlite3.connect("items.db")
cur = conn.cursor()
cur.execute("SELECT id, name, price FROM items")
rows = cur.fetchall()
conn.close()

items = [{"id": r[0], "name": r[1], "price": r[2]} for r in rows]
return jsonify(items)

# ---------- GET: Retrieve specific item ----------
@app.route("/items/", methods=["GET"])
def get_item(item_id):
conn = sqlite3.connect("items.db")
cur = conn.cursor()
cur.execute("SELECT id, name, price FROM items WHERE id = ?", (item_id,))
r = cur.fetchone()
conn.close()

if r:
return jsonify({"id": r[0], "name": r[1], "price": r[2]})
else:
return jsonify({"error": "Item not found"}), 404

# ---------- POST: Create item ----------
@app.route("/items", methods=["POST"])
def create_item():
data = request.get_json()
name = data.get("name")
price = data.get("price")

conn = sqlite3.connect("items.db")
cur = conn.cursor()
cur.execute("INSERT INTO items (name, price) VALUES (?, ?)", (name, price))
conn.commit()
new_id = cur.lastrowid
conn.close()

return jsonify({"message": "Created", "id": new_id}), 201

# ---------- PUT: Update item ----------
@app.route("/items/", methods=["PUT"])
def update_item(item_id):
data = request.get_json()
name = data.get("name")
price = data.get("price")

conn = sqlite3.connect("items.db")
cur = conn.cursor()
cur.execute("UPDATE items SET name = ?, price = ? WHERE id = ?",
(name, price, item_id))
conn.commit()
conn.close()

return jsonify({"message": "Updated"})

# ---------- DELETE: Delete item ----------
@app.route("/items/", methods=["DELETE"])
def delete_item(item_id):
conn = sqlite3.connect("items.db")
cur = conn.cursor()
cur.execute("DELETE FROM items WHERE id = ?", (item_id,))
conn.commit()
conn.close()

return jsonify({"message": "Deleted"})

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










📡 4. Example API Calls

✔ Create Item (POST)





POST /items
Content-Type: application/json

{
"name": "Apple",
"price": 2.5
}







✔ Get All Items (GET)





GET /items







✔ Update Item (PUT)





PUT /items/1
Content-Type: application/json

{
"name": "Updated Apple",
"price": 3.0
}







✔ Delete Item (DELETE)





DELETE /items/1











📘 5. What I Learned

• How Flask handles JSON API endpoints
• How to structure REST API routes
• How SQLite interacts with Python
• The basics of CRUD operations in backend development
• Why returning consistent JSON responses is important

🔧 6. Try It Yourself — Easy Improvements

• Add validation → Ensure name isn't empty or price isn't negative
• Add search → Filter items by name
• Add pagination → Useful when the item list becomes large
• Add timestamps → Store when each item was created
• Convert to Blueprint structure → More scalable Flask architecture

More...