Skip to content

A curated collection of practical AI projects implementing OCR systems, RAG, AI agents, and other AI use cases.

6.3 KB
Raw Blame
"""
LangGraph ReAct agent for Agentic SQL Search, with three tools:
  • load_skill: loads SQL syntax rules and schema reference
  • get_schema: inspects the live database schema
  • execute_sql: runs a SELECT query and returns results
"""

import os
import sqlite3
from pathlib import Path

from dotenv import load_dotenv
from langchain_core.messages import AIMessage, ToolMessage
from langchain_core.tools import tool
from langchain_google_genai import ChatGoogleGenerativeAI
from langgraph.prebuilt import create_react_agent

load_dotenv()

DB_PATH = Path(__file__).parent / "data" / "ecommerce.db"
SKILLS_DIR = Path(__file__).parent / "skills"

# Mutable active path — updated at runtime when user uploads their own database
_active_db_path: Path = DB_PATH


def set_db_path(path) -> None:
    """Switch the database the agent tools query against."""
    global _active_db_path
    _active_db_path = Path(path)

llm = ChatGoogleGenerativeAI(
    model="gemma-4-26b-a4b-it",
    google_api_key=os.getenv("GEMINI_API_KEY"),
    temperature=0,
)


# ── Tools ──────────────────────────────────────────────────────────────────────

@tool
def load_skill(skill_name: str) -> str:
    """
    Load a skill document with syntax rules and query patterns.
    Always call this first with skill_name='sql' before writing any query.
    """
    path = SKILLS_DIR / f"{skill_name}.md"
    if not path.exists():
        available = [p.stem for p in SKILLS_DIR.glob("*.md")]
        return f"Skill '{skill_name}' not found. Available: {', '.join(available)}"
    return path.read_text()


@tool
def get_schema() -> str:
    """
    Return the full database schema — table names, column names, and types.
    Call this to understand what tables and columns are available before writing SQL.
    """
    conn = sqlite3.connect(_active_db_path)
    cursor = conn.cursor()
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name")
    tables = [row[0] for row in cursor.fetchall()]

    schema_parts = []
    for table in tables:
        cursor.execute(f"PRAGMA table_info({table})")
        cols = cursor.fetchall()
        col_defs = "\n".join([f"    {c[1]:30s} {c[2]}" for c in cols])
        schema_parts.append(f"TABLE: {table}\n{col_defs}")

    conn.close()
    return "\n\n".join(schema_parts)


@tool
def execute_sql(query: str) -> str:
    """
    Execute a SQL SELECT query against the e-commerce SQLite database.
    Returns results as a formatted table. Only SELECT queries are allowed.
    """
    query = query.strip()
    if not query.upper().startswith("SELECT"):
        return "Error: Only SELECT queries are allowed."

    try:
        conn = sqlite3.connect(_active_db_path)
        cursor = conn.cursor()
        cursor.execute(query)
        rows = cursor.fetchall()
        columns = [desc[0] for desc in cursor.description]
        conn.close()

        if not rows:
            return "Query executed successfully but returned no results."

        col_widths = [
            max(len(col), max((len(str(row[i])) for row in rows), default=0))
            for i, col in enumerate(columns)
        ]
        header = " | ".join(col.ljust(w) for col, w in zip(columns, col_widths))
        separator = "-+-".join("-" * w for w in col_widths)
        data_rows = [
            " | ".join(str(row[i]).ljust(col_widths[i]) for i in range(len(columns)))
            for row in rows[:100]
        ]
        result = "\n".join([header, separator] + data_rows)
        if len(rows) > 100:
            result += f"\n... ({len(rows)} total rows, showing first 100)"
        return result

    except sqlite3.Error as e:
        return f"SQL Error: {e}"


# ── Agent ──────────────────────────────────────────────────────────────────────

tools = [load_skill, get_schema, execute_sql]

SYSTEM_PROMPT = """You are a data analyst agent with access to an e-commerce SQLite database \
containing customers, products, orders, and order items.

Your job is to answer the user's question by querying the database.

Always follow this exact workflow:
1. Call load_skill with skill_name='sql' to review SQL syntax rules and query patterns
2. Call get_schema to confirm available tables and column names
3. Write and run the SQL query with execute_sql
4. Return a clear, concise summary of the results

Never guess column names — always check the schema first.
Only write SELECT queries. Be precise with JOINs and aggregations."""

graph = create_react_agent(llm, tools=tools, prompt=SYSTEM_PROMPT)


def run_agent(question: str) -> dict:
    """
    Run the agent on a question and return:
      {"answer": str, "steps": [{"tool": str, "input": dict, "output": str}]}
    """
    result = graph.invoke({"messages": [("human", question)]})
    messages = result["messages"]

    # ── Extract final text answer (last non-tool AI message) ──
    answer = ""
    for msg in reversed(messages):
        if isinstance(msg, AIMessage) and not msg.tool_calls:
            content = msg.content
            if isinstance(content, str):
                answer = content.strip()
            elif isinstance(content, list):
                parts = [
                    p.get("text", "") if isinstance(p, dict) else str(p)
                    for p in content
                ]
                answer = " ".join(parts).strip()
            if answer:
                break

    # ── Extract tool call steps ──
    steps = []
    tool_outputs: dict[str, str] = {}

    # First pass: collect tool outputs keyed by tool_call_id
    for msg in messages:
        if isinstance(msg, ToolMessage):
            content = msg.content
            tool_outputs[msg.tool_call_id] = (
                content if isinstance(content, str) else str(content)
            )

    # Second pass: pair AI tool calls with their outputs
    for msg in messages:
        if isinstance(msg, AIMessage) and msg.tool_calls:
            for tc in msg.tool_calls:
                steps.append({
                    "tool": tc["name"],
                    "input": tc["args"],
                    "output": tool_outputs.get(tc["id"], ""),
                })

    return {"answer": answer, "steps": steps}