1. SQL basics with Python¶
Beginner · 10 min read
SQL (Structured Query Language) is how you ask a database for data. You describe what you want — "orders over ₹1,000 from Pune, newest first" — and the database works out how to find it.
1.1 Tables, rows and columns¶
A database holds tables. Each table has columns (with types) and rows (the records). One sample store database is used throughout this series:
import sqlite3
con = sqlite3.connect(":memory:") # a database in memory; use "shop.db" for a file
con.executescript("""
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
city TEXT,
plan TEXT -- 'free' or 'pro'
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id),
amount REAL,
status TEXT, -- 'delivered', 'shipped', 'cancelled'
created_at TEXT -- ISO date 'YYYY-MM-DD'
);
INSERT INTO customers VALUES
(1, 'Priya', 'Bengaluru', 'pro'), (2, 'Rahul', 'Pune', 'free'),
(3, 'Ananya', 'Pune', 'pro'), (4, 'Arjun', 'Delhi', 'free'),
(5, 'Meera', NULL, 'free');
INSERT INTO orders VALUES
(101, 1, 2499, 'delivered', '2026-09-02'), (102, 2, 799, 'delivered', '2026-09-05'),
(103, 1, 1299, 'shipped', '2026-09-20'), (104, 3, 5600, 'delivered', '2026-09-21'),
(105, 4, 450, 'cancelled', '2026-09-22'), (106, 3, 999, 'shipped', '2026-10-01'),
(107, 1, 349, 'delivered', '2026-10-02');
""")
print([r[0] for r in con.execute("SELECT name FROM sqlite_master WHERE type = 'table'")])
PRIMARY KEY— a unique ID for each row.REFERENCES customers(id)— a foreign key: each order points to a customer.NULL— "no value" (Meera's city is unknown). It is not the same as an empty string or 0.
1.2 SELECT — read data¶
A small helper to print query results as a table:
def show(sql: str, params: tuple = ()):
cur = con.execute(sql, params)
cols = [d[0] for d in cur.description]
rows = cur.fetchall()
widths = [max(len(str(x)) for x in [c, *[r[i] for r in rows]]) for i, c in enumerate(cols)]
print(" ".join(c.ljust(w) for c, w in zip(cols, widths)).rstrip())
for r in rows:
print(" ".join(str(x).ljust(w) for x, w in zip(r, widths)).rstrip())
show("SELECT name, city FROM customers")
SELECT * returns every column — handy for exploring, but name the columns in real code so a schema change
doesn't silently break it.
1.3 WHERE — filter rows¶
| Operator | Example |
|---|---|
=, != (or <>), <, >, <=, >= |
amount >= 1000 |
AND, OR, NOT |
city = 'Pune' AND plan = 'pro' |
IN |
status IN ('shipped', 'delivered') |
BETWEEN |
created_at BETWEEN '2026-09-01' AND '2026-09-30' |
LIKE (% = any text, _ = one character) |
name LIKE 'A%' |
IS NULL, IS NOT NULL |
city IS NULL |
= NULL never matches
WHERE city = NULL returns nothing, because any comparison with NULL is unknown. Always use
IS NULL / IS NOT NULL.
1.4 ORDER BY, LIMIT and DISTINCT¶
show("SELECT id, amount FROM orders ORDER BY amount DESC LIMIT 3") # top 3 orders by value
show("SELECT DISTINCT city FROM customers WHERE city IS NOT NULL ORDER BY city")
1.5 Computed columns and aliases¶
show("""
SELECT id,
amount,
ROUND(amount * 0.18, 2) AS gst,
UPPER(status) AS status
FROM orders
WHERE created_at >= '2026-10-01'
""")
AS names a column in the result. Dates stored as ISO text (YYYY-MM-DD) compare and sort correctly as strings.
1.6 Changing data: INSERT, UPDATE, DELETE¶
con.execute("INSERT INTO orders VALUES (108, 2, 1599, 'shipped', '2026-10-05')")
con.execute("UPDATE orders SET status = 'delivered' WHERE id = 103")
con.execute("DELETE FROM orders WHERE status = 'cancelled'")
con.commit() # save the changes
print(con.execute("SELECT COUNT(*) FROM orders").fetchone()[0], "orders")
Always use a WHERE with UPDATE and DELETE
DELETE FROM orders with no WHERE deletes every row. When an LLM writes SQL for you, this is exactly why
generated queries must run on a read-only connection — see Text-to-SQL.
1.7 Parameterised queries — never build SQL with f-strings¶
User input must go in as a parameter (? in SQLite, %s in psycopg for PostgreSQL), never pasted into the
SQL string:
user_input = "Pune' OR '1'='1" # a malicious "city name"
unsafe = f"SELECT name FROM customers WHERE city = '{user_input}'"
print("f-string: ", [r[0] for r in con.execute(unsafe)]) # returns EVERY customer
safe = con.execute("SELECT name FROM customers WHERE city = ?", (user_input,)).fetchall()
print("parameterised:", [r[0] for r in safe]) # no city has that name
This is SQL injection. With parameters, the database treats the input purely as a value. The same rule applies to values an LLM extracts and passes to your tools.
1.8 Rows as dicts — handy for LLM prompts¶
con.row_factory = sqlite3.Row
rows = con.execute("SELECT id, amount, status FROM orders WHERE customer_id = ?", (1,)).fetchall()
orders = [dict(r) for r in rows]
print(orders)
context = "\n".join(f"- Order {o['id']}: ₹{o['amount']:.0f}, {o['status']}" for o in orders)
print(f"Customer's recent orders:\n{context}")
[{'id': 101, 'amount': 2499.0, 'status': 'delivered'}, {'id': 103, 'amount': 1299.0, 'status': 'delivered'}, {'id': 107, 'amount': 349.0, 'status': 'delivered'}]
Customer's recent orders:
- Order 101: ₹2499, delivered
- Order 103: ₹1299, delivered
- Order 107: ₹349, delivered
This is how a support bot gets this customer's data into the prompt — fetched with a safe, fixed query, not by letting the LLM write SQL.
Practice¶
- List all
procustomers in Pune, sorted by name. - Write a function
orders_between(start, end)that uses parameters for both dates.
Next: Joins & aggregation — combine tables and summarise them.