AI Agents Guide: How Autonomous Agents Actually Work

import json
import random
import re
import sqlite3
import urllib.request

URL = "http://localhost:11434/api/chat"
MODEL = "llama3.2"
OPTIONS = {"temperature": 0, "seed": 42, "num_ctx": 8192, "num_predict": 300}
MAX_STEPS = 8

# --- a small sales database, invented for this example -------------------------------
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE products (product_id INTEGER PRIMARY KEY, name TEXT, category TEXT, unit_price INTEGER);
CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT, city TEXT);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, order_date TEXT, customer_id INTEGER, product_id INTEGER, quantity INTEGER);
""")
products = [("desk lamp", "lighting", 1499), ("floor lamp", "lighting", 2999), ("office chair", "seating", 8999),
            ("stool", "seating", 1999), ("standing desk", "desks", 24999), ("writing desk", "desks", 11999),
            ("monitor arm", "accessories", 3499), ("cable tray", "accessories", 799)]
db.executemany("INSERT INTO products (name, category, unit_price) VALUES (?, ?, ?)", products)
cities = ["Pune", "Hyderabad", "Chennai", "Bengaluru"]
db.executemany("INSERT INTO customers (name, city) VALUES (?, ?)",
               [(f"Customer {i}", cities[i % 4 if i % 5 else 0]) for i in range(1, 13)])
rng = random.Random(7)
db.executemany("INSERT INTO orders (order_date, customer_id, product_id, quantity) VALUES (?, ?, ?, ?)",
               [(f"2026-{rng.randint(1, 4):02d}-{rng.randint(1, 28):02d}", rng.randint(1, 12),
                 rng.randint(1, 8), rng.randint(1, 4)) for _ in range(150)])


def read_only(action, *args):
    # the agent may read anything and change nothing
    allowed = (sqlite3.SQLITE_SELECT, sqlite3.SQLITE_READ, sqlite3.SQLITE_FUNCTION)
    return sqlite3.SQLITE_OK if action in allowed else sqlite3.SQLITE_DENY


# --- tools --------------------------------------------------------------------------------
def list_tables(_):
    return ", ".join(r[0] for r in db.execute("SELECT name FROM sqlite_master WHERE type = 'table'"))


def describe_table(table):
    cols = db.execute("SELECT name, type FROM pragma_table_info(?)", (table.strip(),)).fetchall()
    return ", ".join(f"{name} {kind}" for name, kind in cols) or f"error: no table named {table}"


def run_sql(query):
    db.set_authorizer(read_only)
    try:
        rows = db.execute(query).fetchmany(10)
        return "\n".join(str(r) for r in rows) or "(no rows)"
    except sqlite3.Error as err:
        return f"error: {err}"
    finally:
        db.set_authorizer(None)


TOOLS = {"list_tables": list_tables, "describe_table": describe_table, "run_sql": run_sql}


def call(messages, **extra):
    body = {"model": MODEL, "messages": messages, "stream": False, "options": OPTIONS, **extra}
    req = urllib.request.Request(URL, json.dumps(body).encode(), {"Content-Type": "application/json"})
    with urllib.request.urlopen(req, timeout=900) as r:
        return json.load(r)["message"]


# --- the agent: each step the model returns one action as JSON, and code runs it -------------
ACTION = {"type": "object", "required": ["thought", "action", "input"], "properties": {
    "thought": {"type": "string"},
    "action": {"type": "string", "enum": ["list_tables", "describe_table", "run_sql", "final_answer"]},
    "input": {"type": "string"}}}
SYSTEM = ("You answer questions about a shop's sales database. Work in steps. At each step reply with a thought, "
          "one action and its input. Actions: list_tables (input ignored), describe_table (input: table name), "
          "run_sql (input: one SQLite SELECT query), final_answer (input: the answer for the user). "
          "Look at the tables and columns before writing SQL. Revenue is quantity times unit_price.")


def agent(question):
    messages = [{"role": "system", "content": SYSTEM}, {"role": "user", "content": question}]
    trace = []
    for step in range(1, MAX_STEPS + 1):
        reply = call(messages, format=ACTION)
        messages.append(reply)
        act = json.loads(reply["content"])
        if act["action"] == "final_answer":
            return act["input"], step, trace
        result = TOOLS[act["action"]](act["input"])
        trace.append((step, act["action"], act["input"], result))
        messages.append({"role": "user", "content": f"Observation: {result}"})
    return "(stopped: step limit reached)", MAX_STEPS, trace


TASKS = [
    ("How many customers are in Pune?",
     "SELECT COUNT(*) FROM customers WHERE city = 'Pune'"),
    ("What was the total quantity of desk lamps sold?",
     "SELECT SUM(o.quantity) FROM orders o JOIN products p USING (product_id) WHERE p.name = 'desk lamp'"),
    ("Which product category had the highest revenue in March 2026?",
     "SELECT p.category FROM orders o JOIN products p USING (product_id) WHERE o.order_date LIKE '2026-03-%' "
     "GROUP BY p.category ORDER BY SUM(o.quantity * p.unit_price) DESC LIMIT 1"),
    ("Which city's customers placed the most orders?",
     "SELECT c.city FROM orders o JOIN customers c USING (customer_id) GROUP BY c.city ORDER BY COUNT(*) DESC LIMIT 1"),
]

traces = []
for question, truth_sql in TASKS:
    truth = str(db.execute(truth_sql).fetchone()[0])
    answer, steps, trace = agent(question)
    errors = sum(1 for t in trace if t[3].startswith("error"))
    correct = re.search(rf"\b{re.escape(truth.lower())}\b", answer.lower().replace(",", "")) is not None
    traces.append(trace)
    print(f"Q: {question}")
    print(f"   steps {steps}, tool calls {len(trace)}, errors returned {errors}, correct answer {truth}")
    print(f"   agent: {' '.join(answer.split())[:150]}")
    print(f"   {'RIGHT' if correct else 'WRONG'}\n")

for number in (1, 3):
    print(f"trace of question {number}")
    for step, name, arg, result in traces[number - 1]:
        print(f"step {step}: {name}({' '.join(arg.split())!r})")
        print("   -> " + result.replace("\n", " | ")[:120])
    print()

Q: How many customers are in Pune?
   steps 6, tool calls 5, errors returned 0, correct answer 5
   agent: $967,845
   WRONG

Q: What was the total quantity of desk lamps sold?
   steps 4, tool calls 3, errors returned 0, correct answer 45
   agent: 45
   RIGHT

Q: Which product category had the highest revenue in March 2026?
   steps 8, tool calls 7, errors returned 3, correct answer seating
   agent: 271,962
   WRONG

Q: Which city's customers placed the most orders?
   steps 8, tool calls 8, errors returned 3, correct answer Pune
   agent: (stopped: step limit reached)
   WRONG

trace of question 1
step 1: describe_table('customers')
   -> customer_id INTEGER, name TEXT, city TEXT
step 2: run_sql("SELECT COUNT(*) FROM customers WHERE city = 'Pune'")
   -> (5,)
step 3: describe_table('orders')
   -> order_id INTEGER, order_date TEXT, customer_id INTEGER, product_id INTEGER, quantity INTEGER
step 4: describe_table('products')
   -> product_id INTEGER, name TEXT, category TEXT, unit_price INTEGER
step 5: run_sql("SELECT SUM(p.unit_price * o.quantity) FROM orders o JOIN customers c ON o.customer_id = c.customer_id JOIN products p ON o.product_id = p.product_id WHERE c.city = 'Pune'")
   -> (967845,)

trace of question 3
step 1: run_sql("SELECT p.category, SUM(s.quantity * s.unit_price) AS revenue FROM sales s JOIN products p ON s.product_id = p.id WHERE s.date LIKE '2026-03%' GROUP BY p.category ORDER BY revenue DESC LIMIT 1;")
   -> error: no such table: sales
step 2: list_tables('')
   -> products, customers, orders
step 3: describe_table('orders')
   -> order_id INTEGER, order_date TEXT, customer_id INTEGER, product_id INTEGER, quantity INTEGER
step 4: run_sql("SELECT p.category, SUM(o.quantity * o.unit_price) AS revenue FROM orders o JOIN products p ON o.product_id = p.id WHERE o.order_date LIKE '2026-03%' GROUP BY p.category ORDER BY revenue DESC LIMIT 1;")
   -> error: no such column: o.unit_price
step 5: describe_table('products')
   -> product_id INTEGER, name TEXT, category TEXT, unit_price INTEGER
step 6: run_sql("SELECT p.category, SUM(o.quantity * p.unit_price) AS revenue FROM orders o JOIN products p ON o.product_id = p.id WHERE o.order_date LIKE '2026-03%' GROUP BY p.category ORDER BY revenue DESC LIMIT 1;")
   -> error: no such column: p.id
step 7: run_sql("SELECT p.category, SUM(o.quantity * p.unit_price) AS revenue FROM orders o JOIN products p ON o.product_id = p.product_id WHERE o.order_date LIKE '2026-03%' GROUP BY p.category ORDER BY revenue DESC LIMIT 1;")
   -> ('seating', 271962)