Databases and SQL from Python
Query SQLite from Python, use parameters safely, and load results into pandas.
Why it matters: Training data, experiment logs, and RAG docs often live in tables. SQL from Python is a baseline skill.
Basics
sqlite3 basics
sqlite3 is in the standard library. No server needed.
Flow: connect, execute, fetch, commit, close.
fetchone(), fetchall(), or iterate the cursor for large results.
The same DB-API style works with Postgres and MySQL drivers.
Tiny example
Create a table, insert one row, then select it.
import sqlite3
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE runs (model TEXT, accuracy REAL)")
conn.execute("INSERT INTO runs VALUES (?, ?)", ("mini", 0.91))
print(conn.execute("SELECT * FROM runs").fetchall())
conn.close()Basics
Parameters, not string formatting
Never build SQL with f-strings. That is SQL injection.
Use placeholders and pass values separately: execute(sql, (value,)).
SQLite uses ?. Postgres often uses %s.
This matters more when values come from users or LLM output.
Tiny example
Pass values with ? placeholders, never with an f-string.
import sqlite3
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE users (name TEXT)")
conn.execute("INSERT INTO users VALUES ('priya')")
name = "priya'; DROP TABLE users; --"
rows = conn.execute("SELECT * FROM users WHERE name = ?", (name,)).fetchall()
print(rows)
conn.close()Basics
SQL into pandas
pd.read_sql_query(sql, conn) returns a DataFrame.
Push heavy GROUP BY into SQL when tables are large.
Index columns you filter and join on.
Tiny example
Insert a few rows, then load a GROUP BY into pandas.
import sqlite3
import pandas as pd
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE calls (model TEXT, tokens INTEGER)")
conn.executemany("INSERT INTO calls VALUES (?, ?)", [("mini", 100), ("mini", 200), ("large", 500)])
df = pd.read_sql_query("SELECT model, SUM(tokens) AS tokens FROM calls GROUP BY model", conn)
print(df)
conn.close()Then AI / ML
AI / ML examples
Same idea, used in real AI work. Press Try in lab to run it.
AI / ML example
AI / ML: store and rank model runs
Complete SQLite create/insert/select for experiment rows.
In AI / ML: MLOps logs store model name, accuracy, tokens, and date per run.
example.py
Python
import sqlite3
conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute("""
CREATE TABLE runs (
id INTEGER PRIMARY KEY,
model TEXT NOT NULL,
accuracy REAL,
tokens INTEGER,
created_at TEXT
)
""")
rows = [
("gpt-4.1-mini", 0.913, 620, "2026-09-01"),
("gpt-4.1-mini", 0.907, 540, "2026-09-02"),
("llama-3-8b", 0.847, 1520, "2026-09-02"),
("llama-3-8b", 0.861, 1480, "2026-09-03"),
("claude-haiku", 0.900, 460, "2026-09-03"),
]
cur.executemany(
"INSERT INTO runs (model, accuracy, tokens, created_at) VALUES (?, ?, ?, ?)",
rows,
)
conn.commit()
for row in cur.execute("SELECT id, model, accuracy FROM runs ORDER BY accuracy DESC LIMIT 3"):
print(row)
count, avg = cur.execute("SELECT COUNT(*), AVG(accuracy) FROM runs").fetchone()
print(count, "runs, mean", round(avg, 4))
conn.close()AI / ML example 2
AI / ML: aggregate calls then analyse in pandas
GROUP BY in SQL, then summarise in a DataFrame.
In AI / ML: Token and latency cost reports are SQL aggregates loaded into pandas.
example.py
Python
import pandas as pd
import sqlite3
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE calls (model TEXT, tokens INTEGER, latency_ms INTEGER, ok INTEGER)")
conn.executemany("INSERT INTO calls VALUES (?, ?, ?, ?)", [
("mini", 620, 640, 1),
("mini", 540, 580, 1),
("mini", 900, 30000, 0),
("large", 1520, 1800, 1),
("large", 1480, 1750, 1),
("haiku", 460, 420, 1),
("haiku", 480, 450, 1),
])
conn.commit()
summary = pd.read_sql_query("""
SELECT model,
COUNT(*) AS calls,
SUM(tokens) AS total_tokens,
ROUND(AVG(latency_ms), 1) AS avg_latency,
ROUND(AVG(ok) * 100, 1) AS success_pct
FROM calls
GROUP BY model
ORDER BY total_tokens DESC
""", conn)
print(summary.to_string(index=False))
conn.close()Takeaways
- 1sqlite3 follows DB-API 2.0, so other SQL drivers feel familiar.
- 2Always use placeholders - never build SQL with f-strings.
- 3pd.read_sql_query loads results; aggregate in SQL when tables are large.