【實戰】Day 19：從 PostgreSQL 開發 REST API：為 Agent 打造 JSON 數據橋樑

from fastapi import FastAPI, HTTPException
import psycopg2 # PostgreSQL 連線套件
import json

app = FastAPI()

# 連接我們昨天建立的 PostgreSQL 中介庫
def get_db_connection():
    return psycopg2.connect(
        host="localhost",
        database="ai_database",
        user="ai_user",
        password="password"
    )

@app.get("/api/v1/orders/")
def get_orders(brand_name: str, start_date: str = None, end_date: str = None):
    """
    讓 Agent 透過品牌名稱 (如 Nike) 來查詢訂單資料
    """
    conn = get_db_connection()
    cursor = conn.cursor()
    
    # 防呆機制與基礎 SQL 組合
    query = "SELECT order_id, order_date, brand_name, insole_type, unit_price, order_qty, total_amount FROM safe_orders WHERE brand_name = %s"
    params = [brand_name]
    
    if start_date and end_date:
        query += " AND order_date BETWEEN %s AND %s"
        params.extend([start_date, end_date])
        
    cursor.execute(query, tuple(params))
    rows = cursor.fetchall()
    
    if not rows:
        raise HTTPException(status_code=404, detail="找不到該品牌的訂單")
        
    # 將關聯式資料轉換為 JSON 格式 (List of Dictionaries)
    result = []
    for row in rows:
        result.append({
            "order_id": row[0],
            "order_date": str(row[1]),
            "brand_name": row[2],
            "insole_type": row[3],
            "unit_price": float(row[4]),
            "order_qty": int(row[5]),
            "total_amount": float(row[6])
        })
        
    cursor.close()
    conn.close()
    
    return {"status": "success", "data": result}

{
  "status": "success",
  "data": [
    {
      "order_id": "ORD-1001",
      "order_date": "2024-01-15",
      "brand_name": "Nike",
      "insole_type": "PU 發泡鞋墊",
      "unit_price": 1.2,
      "order_qty": 50000,
      "total_amount": 60000.0
    },
    {
      "order_id": "ORD-1006",
      "order_date": "2024-03-15",
      "brand_name": "Nike",
      "insole_type": "EVA 成型鞋墊",
      "unit_price": 0.85,
      "order_qty": 15000,
      "total_amount": 12750.0
    }
  ]
}