We are building a natural language database analyst that sits on top of an existing SQLite schema. It lets anyone in your organization ask questions in plain English and get back verified SQL results, without writing a single query. I ship this pattern often when teams need to expose analytics to non-technical users, and running it on Oxlo.ai keeps costs predictable because long schema prompts do not inflate the per-request price.
What you'll need
- Python 3.10 or newer
- The OpenAI SDK:
pip install openai - An Oxlo.ai API key from https://portal.oxlo.ai
Step 1: Create a sample database
I will use SQLite so you can run this immediately without installing Postgres or MySQL. The schema mimics a real e-commerce system with customers, products, orders, and line items.
import sqlite3
DB_PATH = "store.db"
def init_db():
conn = sqlite3.connect(DB_PATH)
cur = conn.cursor()
cur.executescript("""
CREATE TABLE IF NOT EXISTS customers (
customer_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT,
signup_date TEXT
);
CREATE TABLE IF NOT EXISTS products (
product_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
category TEXT,
price REAL
);
CREATE TABLE IF NOT EXISTS orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER REFERENCES customers(customer_id),
order_date TEXT,
total REAL
);
CREATE TABLE IF NOT EXISTS order_items (
item_id INTEGER PRIMARY KEY,
order_id INTEGER REFERENCES orders(order_id),
product_id INTEGER REFERENCES products(product_id),
quantity INTEGER
);
INSERT INTO customers VALUES
(1, 'Alice Smith', 'alice@example.com', '2023-01-15'),
(2, 'Bob Jones', 'bob@example.com', '2023-02-20');
INSERT INTO products VALUES
(1, 'Wireless Mouse', 'Electronics', 29.99),
(2, 'USB-C Cable', 'Electronics', 9.99),
(3, 'Notebook', 'Office', 12.50);
INSERT INTO orders VALUES
(1, 1, '2024-01-10', 39.98),
(2, 2, '2024-01-12', 22.49);
INSERT INTO order_items VALUES
(1, 1, 1, 1),
(2, 1, 2, 1),
(3, 2, 3, 1),
(4, 2, 2, 1);
""")
conn.commit()
conn.close()
if __name__ == "__main__":
init_db()
print("Database initialized.")
Step 2: Extract the schema
The agent needs to know what tables and columns exist. I pull the CREATE statements directly from sqlite_master so the prompt stays in sync with the actual database.
import sqlite3
DB_PATH = "store.db"
def get_schema():
conn = sqlite3.connect(DB_PATH)
cur = conn.cursor()
cur.execute(
"SELECT sql FROM sqlite_master "
"WHERE type='table' AND name NOT LIKE 'sqlite_%'"
)
rows = cur.fetchall()
conn.close()
return "\n".join(row[0] for row in rows if row[0])
schema = get_schema()
print(schema)
Step 3: Define the agent prompt
I keep the system prompt strict. The model is allowed to generate exactly one SELECT statement and nothing else. This minimizes the risk of accidental data loss.
def make_system_prompt(schema: str) -> str:
return (
"You are a read-only database analyst. "
"Translate the user's question into a single, valid SQLite SELECT statement. "
"Use only the tables and columns provided in the schema below. "
"Never generate INSERT, UPDATE, DELETE, DROP, ALTER, or PRAGMA statements. "
"If the question cannot be answered with the schema, reply exactly with: "
"UNSUPPORTED: . "
"Wrap the SQL in a markdown code block like
```sql ... ```
.\n\n"
f"Schema:\n{schema}"
)
Step 4: Initialize the Oxlo.ai client
Oxlo.ai is fully OpenAI SDK compatible, so the swap is one line. I use qwen-3-32b because it handles structured agent workflows well. Because Oxlo.ai uses flat per-request pricing, sending the full schema every turn is cheap, even when the context grows. See https://oxlo.ai/pricing for plan details.
from openai import OpenAI
client = OpenAI(base_url="https://api.oxlo.ai/v1", api_key="YOUR_OXLO_API_KEY")
Step 5: Generate SQL from natural language
I pass the schema and the user question to the model, then parse the markdown block to extract clean SQL.
import re
def generate_sql(question: str, schema: str) -> str:
response = client.chat.completions.create(
model="qwen-3-32b",
messages=[
{"role": "system", "content": make_system_prompt(schema)},
{"role": "user", "content": question},
],
)
content = response.choices[0].message.content
match = re.search(r"
```sql\s*(.*?)```
", content, re.DOTALL)
if match:
return match.group(1).strip()
if content.strip().upper().startswith("SELECT"):
return content.strip()
return content.strip()
Step 6: Execute and summarize
Safety first. I enable SQLite's query-only pragma so the connection cannot mutate data. Then I run a second call to llama-3.3-70b to turn the raw rows into a readable sentence.
def run_query(sql: str):
conn = sqlite3.connect(DB_PATH)
conn.execute("PRAGMA query_only = ON")
cur = conn.cursor()
try:
cur.execute(sql)
rows = cur.fetchall()
cols = [desc[0] for desc in cur.description] if cur.description else []
return {"columns": cols, "rows": rows}
except Exception as e:
return {"error": str(e)}
finally:
conn.close()
def summarize(question: str, sql: str, result: dict) -> str:
if "error" in result:
return f"Query failed: {result['error']}"
payload = (
f"Question: {question}\n"
f"SQL: {sql}\n"
f"Columns: {result['columns']}\n"
f"Rows: {result['rows']}"
)
response = client.chat.completions.create(
model="llama-3.3-70b",
messages=[
{"role": "system", "content": "Summarize database query results in one to two sentences for a non-technical user."},
{"role": "user", "content": payload},
],
)
return response.choices[0].message.content.strip()
Step 7: Wrap it in a REPL
This loop ties everything together. It initializes the database, caches the schema, and waits for user questions.
if __name__ == "__main__":
init_db()
schema = get_schema()
print("Database analyst ready. Ask a question or type 'quit'.")
while True:
try:
q = input("\n> ").strip()
except (EOFError, KeyboardInterrupt):
break
if q.lower() in ("quit", "exit"):
break
sql = generate_sql(q, schema)
if sql.upper().startswith("UNSUPPORTED"):
print(sql)
continue
result = run_query(sql)
answer = summarize(q, sql, result)
print(f"\nSQL: {sql}")
print(f"Answer: {answer}")
Run it
Save the full script as analyst.py, set your OXLO_API_KEY, and run python analyst.py. Here is a sample session:
> What is the total revenue from electronics?
SQL: SELECT SUM(oi.quantity * p.price) AS revenue
FROM order_items oi
JOIN orders o ON oi.order_id = o.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE p.category = 'Electronics';
Answer: The total revenue from electronics is $39.97.
Next steps
Swap the SQLite connection for an existing Postgres or MySQL client by replacing the connection logic and schema query, leaving the Oxlo.ai generation layer unchanged.
Add a Pandas read_sql layer so the agent returns DataFrames instead of raw tuples, which makes it easy to drop into a Jupyter notebook or BI pipeline.
Top comments (0)