Module 18: Who uses this: sectors and careers · Lesson 18.5 · about 25 min
Software and data teams
Developers and data analysts use local models as a private coding assistant, SQL helper and log reader, keeping source code and company data in-house. Includes mini project 8, a local SQL helper.
Module 18 · about 25 minutes · read first: 11.2, 13.2 and 13.3. You’ll be able to set up a private coding and data assistant for your team. This includes mini project 8.
1. Why this sector
Developers and analysts use AI more than almost anyone, and they work with the most sensitive things a company has: its code and its data. Local models give them help without a policy fight, and run as many queries as they like.
2. Top uses
- Coding assistant in the editor: autocomplete and chat with a local coder model (Lesson 13.3).
- SQL helper: plain English to SQL against your own schema (the list of tables and their columns), explained.
- Log and error reading: paste a stack trace (the list of function calls an error came through) or a log section, get a likely cause.
- Documentation: docstrings, READMEs and change notes from code.
- Data cleaning scripts drafted for a person to review.
3. Mini project 8: a local SQL helper
This script sends only the table layout and the question to your local model, then runs the query read-only.
import sqlite3
from openai import OpenAI
client = OpenAI(base_url="http://localhost:11434/v1", api_key="local")
DB = "sales.db"
def schema(db: str) -> str:
con = sqlite3.connect(f"file:{db}?mode=ro", uri=True)
rows = con.execute("SELECT sql FROM sqlite_master WHERE type='table'").fetchall()
con.close()
return "\n".join(r[0] for r in rows)
def to_sql(question: str) -> str:
prompt = f"SQLite tables:\n{schema(DB)}\n\nWrite one SQLite SELECT query for: {question}\nReply with the SQL only."
reply = client.chat.completions.create(model="gemma4:12b", messages=[{"role": "user", "content": prompt}])
sql = reply.choices[0].message.content.strip().strip("`")
return sql.removeprefix("sql").strip()
def run(sql: str):
if not sql.lower().lstrip().startswith(("select", "with")):
raise ValueError("Only SELECT queries are allowed")
con = sqlite3.connect(f"file:{DB}?mode=ro", uri=True) # read-only
rows = con.execute(sql).fetchmany(50)
con.close()
return rows
sql = to_sql("Total sales by region in 2026, largest first")
print(sql)
print(run(sql))
Steps: make a small sales.db (or use the one from Lesson 13.2), run the script, read the SQL before trusting the numbers, then try five questions of your own and note which ones it gets wrong.
Here is what happens inside one run:
The question. "Total sales by region in 2026, largest first."
Only the shape goes in. schema() reads the CREATE TABLE lines: table and column names such as sales(region, amount, sold_on). Not one customer row is sent to the model.
The model writes SQL. For example SELECT region, SUM(amount) FROM sales WHERE sold_on LIKE '2026%' GROUP BY region ORDER BY 2 DESC.
Two locks. run() refuses anything that does not start with SELECT or WITH, and opens the database read-only, so even a bad query cannot change or delete data.
A person reads it. The SQL is printed before the numbers. You check it does what you asked (the right year, the right total) before you trust the result.
Maths and principle
Principle: send the model the shape of the data, not the data.
Equation: tokens sent = schema size + question; rows never leave the database.
Analogy: asking a colleague to write a query by showing them the table headings, not the customer list.
Try it: a schema of 40 columns is about 600 tokens. What does that mean for privacy? (No customer rows are in the prompt at all; only names of tables and columns.)
- schema
- the table and column names: the headings of your spreadsheets, not their contents
- tokens sent
- schema + instructions + question; it grows with the number of columns, never with the number of rows
- read-only
- the database is opened so nothing can be written, changed or deleted
- Physical meaning
- A table with ten rows and a table with ten million rows send the same prompt, because only the headings travel. Privacy and cost both stay flat however big the data grows.
“If the AI’s SQL runs without an error, the answer is right.” A query can run perfectly and still answer the wrong question: the wrong year, a missing filter, a join that counts rows twice. Read the SQL before trusting the numbers, and test the helper on questions whose answers you already know.
4. Worked scenario
Made-up team. A logistics company’s 6-person data team runs a coder model on a shared 24 GB card. Analysts use the SQL helper for first drafts and the editor assistant for Python. Their policy forbids pasting customer data into outside tools, so before this they had no AI help at all; now drafting time on routine reports is down by about a third.
5. Practice
- Run mini project 8 and record which questions it got wrong.
- Why check that the query starts with SELECT, when the database is already read-only?
- What could you add so the model sees example rows safely?
Answers
- Your answer; joins and date handling are common weak spots.
- Two locks are better than one: if someone later opens the database read-write, the check still stops edits.
- A few made-up or masked example rows, never real customer data.
New words: schema, read-only, coder model, stack trace.
- Local coding assistants and SQL helpers keep source code and company data inside the company.
- The SQL helper sends only the schema and the question: the prompt grows with columns, never with rows.
- Two locks: allow only SELECT queries, and open the database read-only.
- A query that runs can still be wrong; read the SQL and test on questions you know the answer to.
Check yourself
Sources
- Continue (open-source coding assistant for VS Code and JetBrains that works with Ollama), https://docs.continue.dev
- Ollama OpenAI-compatible API (official page), https://docs.ollama.com/openai
- Python sqlite3 module (official documentation), https://docs.python.org/3/library/sqlite3.html
Your ticks stay in this browser. Save them as a file on the course home.
Updated 2026-11-22. Prices and model names change fast: check the official source before you buy.