import sqlite3
import pathlib
from typing import Optional
from pydantic import BaseModel
from fastapi import APIRouter, HTTPException, status
router = APIRouter(tags=["shop"])
# router.db_path = "app/db/northwind.db"
router.db_path = str(pathlib.Path(__file__).parent.parent) + "/northwind.db"
@router.on_event("startup")
async def startup():
router.db_connection = sqlite3.connect(router.db_path)
router.db_connection.text_factory = lambda b: b.decode(errors="ignore") # northwind specific
@router.on_event("shutdown")
async def shutdown():
router.db_connection.close()
# Ex1 - selecting specific columns from "Categories" and "Customers" tables in db.
@router.get("/categories")
async def categories_view():
cursor = router.db_connection.cursor()
cursor.row_factory = sqlite3.Row # Changes Query response from list of lists to list of dicts
categories = cursor.execute("SELECT CategoryID, CategoryName FROM Categories ORDER BY CategoryId").fetchall()
return {
"categories":
