If you have database access, you know the message: "Got a sec? Can you pull a number for me?"
Sales wants a number. Support wants a number. Finance wants theirs by five. Each one is small. Together they eat the day, and every one costs you the time it takes to get back into what you were doing.
This post builds the smallest thing that removes you from that loop: one page where a colleague types a question and gets an answer they can check without you.
What "an answer they can check" means
If the page only shows a sentence, people will come back and ask you to confirm it. So every answer needs three parts:
- The answer in words.
- The rows it came from.
- The query that ran, folded away for anyone who wants it.
With those three on screen, "is this right?" stops being a question for you.
The whole app
One Flask file. It serves a page and proxies questions to FactQL, which returns the answer, the rows and the query in one JSON response.
You don't need to sign up to try it: without a key, the script borrows FactQL's public sample key, which points at a demo film-rental shop.
# pip install flask requests
import os, requests
from flask import Flask, request, jsonify, render_template_string
app = Flask(__name__)
FACTQL = "https://api.factql.ai"
def api_key():
# Use your own key if you have one; otherwise borrow the public sample key.
key = os.environ.get("FACTQL_API_KEY")
if not key:
key = requests.get(f"{FACTQL}/sample", timeout=30).json()["api_key"]
return key
KEY = api_key()
PAGE = """
<!doctype html>
<title>Ask the data</title>
<style>
body { font: 16px system-ui; max-width: 760px; margin: 40px auto; padding: 0 16px; }
input { width: 75%; padding: 10px; } button { padding: 10px 16px; }
table { border-collapse: collapse; margin: 12px 0; } td, th { border: 1px solid #ccc; padding: 6px 10px; }
pre { background: #f4f4f4; padding: 12px; overflow-x: auto; }
</style>
<h1>Ask the data</h1>
<form id="f"><input id="q" placeholder="Who are our top 5 customers by total spend?"> <button>Ask</button></form>
<div id="out"></div>
<script>
const out = document.getElementById("out");
const cell = (tag, text) => { const el = document.createElement(tag); el.textContent = text; return el; };
document.getElementById("f").onsubmit = async (e) => {
e.preventDefault();
out.textContent = "Working...";
const r = await fetch("/ask", { method: "POST", headers: {"Content-Type": "application/json"},
body: JSON.stringify({ q: document.getElementById("q").value }) });
const a = await r.json();
out.textContent = "";
out.append(cell("p", a.explanation || a.error || "No answer."));
if (a.data) {
const t = document.createElement("table"), h = document.createElement("tr");
a.data.columns.forEach(c => h.append(cell("th", c))); t.append(h);
a.data.rows.forEach(row => { const tr = document.createElement("tr"); row.forEach(v => tr.append(cell("td", v))); t.append(tr); });
out.append(t);
}
if (a.sql) { const d = document.createElement("details"); d.append(cell("summary", "The query that ran"), cell("pre", a.sql)); out.append(d); }
};
</script>
"""
@app.get("/")
def home():
return render_template_string(PAGE)
@app.post("/ask")
def ask():
q = (request.get_json(silent=True) or {}).get("q", "").strip()
if not q:
return jsonify(error="Type a question first."), 400
r = requests.post(f"{FACTQL}/query", headers={"X-API-Key": KEY}, json={"q": q}, timeout=120)
if r.status_code == 429:
return jsonify(error=f"Too many questions. Try again in {r.headers.get('Retry-After', 'a few')} seconds."), 429
r.raise_for_status()
a = r.json()
# Pass on only what the page shows.
return jsonify({k: a.get(k) for k in ("result", "explanation", "data", "sql", "lineage")})
if __name__ == "__main__":
app.run(port=5000)
Run it:
pip install flask requests
python app.py
# open http://localhost:5000
What comes back
I asked "Who are our top 5 customers by total spend?". This is the real response from the sample data (trimmed to the fields the page shows):
{
"result": "success",
"explanation": "Your top 5 customers by total payments are Karl Seal (358.12), Tim Cary (344.06), Russell Brinson (324.04), Harry Arce (323.17) and Daisy Bates (322.12).",
"data": {
"columns": ["first_name", "last_name", "total_spend"],
"rows": [["KARL", "SEAL", 358.12], ["TIM", "CARY", 344.06], ["RUSSELL", "BRINSON", 324.04], ["HARRY", "ARCE", 323.17], ["DAISY", "BATES", 322.12]]
},
"sql": "SELECT c.first_name, c.last_name, SUM(p.amount) AS total_spend\nFROM payment p JOIN customer c ON c.customer_id = p.customer_id\nGROUP BY c.customer_id, c.first_name, c.last_name\nORDER BY total_spend DESC\nLIMIT 5"
}
(The raw amounts come back as floats like 358.1200000000005; round them when you display money.)
The page shows the sentence, draws the rows as a table, and tucks the query under "The query that ran".
Four things worth getting right
-
Keep the key on the server. The browser talks to
/ask, never to the upstream service. A key in page code can be read by anyone who opens the page. -
Write values with
textContent, notinnerHTML. Answers and rows are data. Treat them as text so nothing in a cell can run as markup. - Use a read-only database user when you point this at your own database instead of the sample.
- Handle 429. The sample key allows only a couple of questions a minute per IP, so the proxy passes the wait time on instead of failing silently.
Pointing it at your own data
Set FACTQL_API_KEY to your own key and the same file works against your data: an uploaded CSV or Excel file, or a PostgreSQL or MySQL database connected with a read-only user. The free plan needs no card.
When not to do this
If people ask the same five questions every week, a dashboard is the better tool. This page is for the long tail: the questions nobody predicted, which are exactly the ones that land in your DMs.
What's the "quick question" you get asked most often?
Top comments (0)