Practical notes: Text-to-SQL Agent in Python: LLM Tool Calling Tutorial

This walkthrough rebuilds the path from raw materials to a working system for: Build a Text-to-SQL Agent in Python Where Only the Tool Is Code. The focus is operable steps, explicit checks, and code that you can drop into a repo without guessing intent. For Overview, define the inputs, the owner of the step, and the exit criteria before changing code. Operators should be able to re-run the step from a known checkpoint without guessing hidden state. Treat this stage as a contract between inputs and validated outputs. Name the artifacts, define success checks, and refuse silent partial completion.

The split: definition versus implementation

When working through The split: definition versus implementation, write down the contract first: required inputs, success signal, and what happens on partial failure. That checklist keeps later code changes honest. Record timings and token or query cost next to functional results. Cost visibility early prevents surprise bills when the path moves from demo to shared environments. Log request id, model id, and latency on every call. Without that trail, intermittent provider errors look like application bugs.

What you need

When working through What you need, write down the contract first: required inputs, success signal, and what happens on partial failure. That checklist keeps later code changes honest. Keep configuration outside application code. Environment files, secret stores, and feature flags belong in one place operators can audit without reading the whole graph. Log request id, model id, and latency on every call. Without that trail, intermittent provider errors look like application bugs.

pip install acruxcore

1. Seed a database worth querying

When working through 1. Seed a database worth querying, write down the contract first: required inputs, success signal, and what happens on partial failure. That checklist keeps later code changes honest. Document the happy path and the recovery path together. Retries, human gates, and dead-letter handling are part of the product, not later polish. Log request id, model id, and latency on every call. Without that trail, intermittent provider errors look like application bugs. When working through 1. Seed a database worth querying, write down the contract first: required inputs, success signal, and what happens on partial failure. That checklist keeps later code changes honest. Treat this stage as a contract between inputs and validated outputs. Name the artifacts, define success checks, and refuse silent partial completion.

conn.executescript("""
    CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, category TEXT, price REAL, stock INTEGER);
    CREATE TABLE orders (id INTEGER PRIMARY KEY, product_id INTEGER REFERENCES products(id),
                          quantity INTEGER, order_date TEXT, customer TEXT);
""")
conn.executemany("INSERT INTO products VALUES (?, ?, ?, ?, ?)", PRODUCTS)
conn.executemany("INSERT INTO orders VALUES (?, ?, ?, ?, ?)", ORDERS)

python seed_db.py
# Seeded store.db: 8 products, 15 orders.

2. Register the model in the dashboard

1. Register the model in the dashboard works best when treated as a measurable surface. Capture one golden transcript, one failure case, and the rollback note before expanding scope. Record timings and token or query cost next to functional results. Cost visibility early prevents surprise bills when the path moves from demo to shared environments. Pin the interpreter and dependency lockfile before teaching the loop. Drift between laptop and CI is the most common silent break for API demos.

3. Write the prompt in the dashboard

1. Write the prompt in the dashboard works best when treated as a measurable surface. Capture one golden transcript, one failure case, and the rollback note before expanding scope. Keep configuration outside application code. Environment files, secret stores, and feature flags belong in one place operators can audit without reading the whole graph. Pin the interpreter and dependency lockfile before teaching the loop. Drift between laptop and CI is the most common silent break for API demos.

You are a data analyst for an online store. Answer questions about products and
sales by querying a SQLite database with the query_database tool. Never guess —
always query.

Schema:
CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, category TEXT, price REAL, stock INTEGER);
CREATE TABLE orders (id INTEGER PRIMARY KEY, product_id INTEGER REFERENCES products(id), quantity INTEGER, order_date TEXT, customer TEXT);Write a single read-only SQLite SELECT, call query_database with it, then answer
in one or two sentences using only the rows it returns. Prices are in USD;
revenue = quantity * price; order_date is YYYY-MM-DD.

4. Define the tool in code — and let it publish itself

1. Define the tool in code — and let it publish itself works best when treated as a measurable surface. Capture one golden transcript, one failure case, and the rollback note before expanding scope. Document the happy path and the recovery path together. Retries, human gates, and dead-letter handling are part of the product, not later polish. Pin the interpreter and dependency lockfile before teaching the loop. Drift between laptop and CI is the most common silent break for API demos.
2. Define the tool in code — and let it publish itself works best when treated as a measurable surface. Capture one golden transcript, one failure case, and the rollback note before expanding scope. Treat this stage as a contract between inputs and validated outputs. Name the artifacts, define success checks, and refuse silent partial completion.

from acruxcore import AcruxCore, acrux

@acrux.tool
async def query_database(sql: str) -> list[dict]:
    """Run a read-only SQL SELECT against the store database.    Args:
        sql: A single read-only SQLite SELECT statement.
    """
    statement = sql.strip().rstrip(";").strip()
    if not statement.lower().startswith("select"):
        raise ValueError("Only read-only SELECT statements are allowed.")
    if ";" in statement:
        raise ValueError("Only a single statement is allowed.")
    conn = sqlite3.connect(f"file:{DB_PATH}?mode=ro", uri=True)
    conn.row_factory = sqlite3.Row
    try:
        return [dict(row) for row in conn.execute(statement).fetchall()]
    finally:
        conn.close()

{
  "name": "query_database",
  "description": "Run a read-only SQL SELECT against the store database.",
  "parameters": {
    "type": "object",
    "properties": {
      "sql": {"type": "string", "description": "A single read-only SQLite SELECT statement."}
    },
    "required": ["sql"]
  }
}

async with AcruxCore() as hub:
    await hub.tools.sync([query_database])

5. Let the dashboard own a tool’s wording

For 5. Let the dashboard own a tool’s wording, define the inputs, the owner of the step, and the exit criteria before changing code. Operators should be able to re-run the step from a known checkpoint without guessing hidden state. Record timings and token or query cost next to functional results. Cost visibility early prevents surprise bills when the path moves from demo to shared environments. Separate client construction from the message loop so providers can be swapped without rewriting the conversation state machine.

@acrux.tool
async def check_disclosure_policy(field: str) -> dict:
    # No docstring, on purpose. See below — the absence is the mechanism.
    sensitive = field.strip().lower() in {"customer", "customer_name", "email"}
    return {
        "field": field,
        "may_disclose": not sensitive,
        "guidance": (
            "Do not name an individual customer. Report aggregate figures only."
            if sensitive
            else "This column may be shown to the user."
        ),
    }

{
  "name": "check_disclosure_policy",
  "description": null,
  "parameters": {
    "type": "object",
    "properties": {"field": {"type": "string"}},
    "required": ["field"]
  }
}

Published: ToolSyncResult(tool_id='2572965e-…', version_number=2, committed=False, alias='production', superseded_source=None)

6. Run it

For 6. Run it, define the inputs, the owner of the step, and the exit criteria before changing code. Operators should be able to re-run the step from a known checkpoint without guessing hidden state. Keep configuration outside application code. Environment files, secret stores, and feature flags belong in one place operators can audit without reading the whole graph. Separate client construction from the message loop so providers can be swapped without rewriting the conversation state machine.

async def ask(hub: AcruxCore, question: str) -> str:
    rendered = await hub.prompts.render("sql-analyst-agent", "production")
    messages = [*rendered.messages, {"role": "user", "content": question}]
    result = await hub.gateway.run_prompt_with_tools(
        rendered,
        messages=messages,
        tools=[query_database, check_disclosure_policy],
        trace={"name": "sql-analyst-agent", "session_id": "sql-agent-demo"},
    )
    print(f"  (trace {result.trace_id})")
    return result.content

export ACRUXCORE_API_KEY=<your personal api key>
export ACRUXCORE_BASE_URL=https://api.acruxcore.com/api/v1
python sql_agent.py

Q: Which product generated the most total revenue, and how much?
  (trace 606dbd38-cb34-4cc3-a1a1-ec4dc9af87b2)
A: The **Aeron Chair** generated the most total revenue at **$4,185.00**.

Q: How many total units were ordered in June 2026?
  (trace d1ace20b-ae00-4c4d-9294-a613327e1583)
A: In June 2026, a total of **93 units** were ordered.Q: Who is our biggest customer by total spend?
  (trace ea57a392-9af2-41b6-bfd8-48297ee17a8c)
A: Our biggest customer by total spend has spent $6,995.00. I'm unable to disclose the
   specific customer name due to privacy policy, but I can confirm this is our top
   customer by total spending.

7. Read the trace

For 7. Read the trace, define the inputs, the owner of the step, and the exit criteria before changing code. Operators should be able to re-run the step from a known checkpoint without guessing hidden state. Document the happy path and the recovery path together. Retries, human gates, and dead-letter handling are part of the product, not later polish. Separate client construction from the message loop so providers can be swapped without rewriting the conversation state machine. For 7. Read the trace, define the inputs, the owner of the step, and the exit criteria before changing code. Operators should be able to re-run the step from a known checkpoint without guessing hidden state. Treat this stage as a contract between inputs and validated outputs. Name the artifacts, define success checks, and refuse silent partial completion.

8. Group runs into a session

When working through 8. Group runs into a session, write down the contract first: required inputs, success signal, and what happens on partial failure. That checklist keeps later code changes honest. Record timings and token or query cost next to functional results. Cost visibility early prevents surprise bills when the path moves from demo to shared environments. Log request id, model id, and latency on every call. Without that trail, intermittent provider errors look like application bugs.

9. The payoff: change the model without touching code

When working through 9. The payoff: change the model without touching code, write down the contract first: required inputs, success signal, and what happens on partial failure. That checklist keeps later code changes honest. Keep configuration outside application code. Environment files, secret stores, and feature flags belong in one place operators can audit without reading the whole graph. Log request id, model id, and latency on every call. Without that trail, intermittent provider errors look like application bugs.

Should your code own the tool at all?

When working through Should your code own the tool at all?, write down the contract first: required inputs, success signal, and what happens on partial failure. That checklist keeps later code changes honest. Document the happy path and the recovery path together. Retries, human gates, and dead-letter handling are part of the product, not later polish. Log request id, model id, and latency on every call. Without that trail, intermittent provider errors look like application bugs. When working through Should your code own the tool at all?, write down the contract first: required inputs, success signal, and what happens on partial failure. That checklist keeps later code changes honest. Treat this stage as a contract between inputs and validated outputs. Name the artifacts, define success checks, and refuse silent partial completion.

Where to take it next

Where to take it next works best when treated as a measurable surface. Capture one golden transcript, one failure case, and the rollback note before expanding scope. Record timings and token or query cost next to functional results. Cost visibility early prevents surprise bills when the path moves from demo to shared environments. Pin the interpreter and dependency lockfile before teaching the loop. Drift between laptop and CI is the most common silent break for API demos.

Operational checklist

Operational checklist works best when treated as a measurable surface. Capture one golden transcript, one failure case, and the rollback note before expanding scope.

Prefer small, testable units over sprawling scripts. When a step fails, the failure should point at a single responsibility rather than a tangled pipeline.

Pin the interpreter and dependency lockfile before teaching the loop. Drift between laptop and CI is the most common silent break for API demos.

Authenticate at the gateway and re-authorize at the data plane. A bearer token alone is not a tenancy boundary.

Checkpoint after expensive steps. Resume should not re-bill the same LLM call when an operator retries a later node.

Pin dependency versions and record the image digest that ran the demo. Reproducibility beats tribal knowledge.

Before promoting the stack, freeze versions, capture a golden transcript for the critical path, and confirm rollback steps. Shared environments need rate limits, tenancy checks, and a clear owner for secret rotation. Prefer boring reliability over clever one-off demos.

Batch note for a664c3276a43: keep provider keys out of the repo, set a per-session token ceiling, and store transcripts next to the eval fixtures so later model swaps stay comparable.

When working through hardening note 0, write down the contract first: required inputs, success signal, and what happens on partial failure. That checklist keeps later code changes honest. Prefer small, testable units over sprawling scripts. When a step fails, the failure should point at a single responsibility rather than a tangled pipeline.

Hardening detail 0/766: measure wall time, error class, and token spend for this note, then decide whether to keep the change based on a fixed question set rather than anecdote.

hardening note 1 works best when treated as a measurable surface. Capture one golden transcript, one failure case, and the rollback note before expanding scope. Record timings and token or query cost next to functional results. Cost visibility early prevents surprise bills when the path moves from demo to shared environments.

Hardening detail 1/766: measure wall time, error class, and token spend for this note, then decide whether to keep the change based on a fixed question set rather than anecdote.

For hardening note 2, define the inputs, the owner of the step, and the exit criteria before changing code. Operators should be able to re-run the step from a known checkpoint without guessing hidden state. Document the happy path and the recovery path together. Retries, human gates, and dead-letter handling are part of the product, not later polish.

Hardening detail 2/766: measure wall time, error class, and token spend for this note, then decide whether to keep the change based on a fixed question set rather than anecdote.

When working through hardening note 3, write down the contract first: required inputs, success signal, and what happens on partial failure. That checklist keeps later code changes honest. Treat this stage as a contract between inputs and validated outputs. Name the artifacts, define success checks, and refuse silent partial completion.

Hardening detail 3/766: measure wall time, error class, and token spend for this note, then decide whether to keep the change based on a fixed question set rather than anecdote.

hardening note 4 works best when treated as a measurable surface. Capture one golden transcript, one failure case, and the rollback note before expanding scope. Keep configuration outside application code. Environment files, secret stores, and feature flags belong in one place operators can audit without reading the whole graph.

Hardening detail 4/766: measure wall time, error class, and token spend for this note, then decide whether to keep the change based on a fixed question set rather than anecdote.

For hardening note 5, define the inputs, the owner of the step, and the exit criteria before changing code. Operators should be able to re-run the step from a known checkpoint without guessing hidden state. Prefer small, testable units over sprawling scripts. When a step fails, the failure should point at a single responsibility rather than a tangled pipeline.

Hardening detail 5/766: measure wall time, error class, and token spend for this note, then decide whether to keep the change based on a fixed question set rather than anecdote.

When working through hardening note 6, write down the contract first: required inputs, success signal, and what happens on partial failure. That checklist keeps later code changes honest. Record timings and token or query cost next to functional results. Cost visibility early prevents surprise bills when the path moves from demo to shared environments.

Hardening detail 6/766: measure wall time, error class, and token spend for this note, then decide whether to keep the change based on a fixed question set rather than anecdote.

hardening note 7 works best when treated as a measurable surface. Capture one golden transcript, one failure case, and the rollback note before expanding scope. Document the happy path and the recovery path together. Retries, human gates, and dead-letter handling are part of the product, not later polish.

Hardening detail 7/766: measure wall time, error class, and token spend for this note, then decide whether to keep the change based on a fixed question set rather than anecdote.