SQL for Python Developers

Learning SQL makes you a better Python developer, even if you use pandas and ORMs. The first post in a series that teaches it with DuckDB and Python.

Install DuckDB with pip, create your first table, insert data, and run a SQL query - all in Python, all in under 5 minutes, zero configuration required

Real SQL practice needs real-looking data. Generate hundreds of customers with Python's Faker library without downloading a single CSV

Your in-memory database disappears when Python exits. Let's fix that with a one-line change that saves everything to disk.

The mental model that makes SQL click: describe what you want instead of looping row by row. Think in sets and the keywords become intuitive.

We write SELECT first, but FROM runs first. It's the `for item in collection` part of a Python loop: pick your data source before anything else.

SQL's SELECT is more than picking columns. Rename with AS, compute expressions, and use DISTINCT for unique values.

SQL returns rows in no guaranteed order; the same query can come back differently twice. ORDER BY gives you control, like Python's sorted() with keys.

Filter rows with conditions, the SQL equivalent of list comprehension `if` clauses. Covers AND/OR, IN, BETWEEN, LIKE patterns, and NULL handling.

GROUP BY creates buckets and counts them. It's like Python's `collections.Counter` or pandas `groupby()`. Learn COUNT, SUM, AVG, MIN, and MAX.

WHERE filters rows before grouping; HAVING filters after. Need "only cities with more than 10 customers"? That's HAVING.

Connect related tables like looking up values in a Python dictionary. Covers INNER JOIN, LEFT JOIN, and when to use each.

Nest queries inside queries, like Python helper functions. Use them in WHERE, SELECT, or FROM to compute intermediate results.

WITH clauses let you name your subqueries and read top-to-bottom instead of inside-out. Transform nested spaghetti into clean steps.

NULL means unknown, not empty. Why NULL = NULL isn't true, three-valued logic, and how NULL behaves in WHERE, GROUP BY, JOINs and subqueries.

Calculate across rows without collapsing them. Running totals, rankings, and row comparisons that GROUP BY can't do.

Map Python types to SQL types. Learn CREATE TABLE with primary keys, foreign keys, and constraints like NOT NULL and DEFAULT.

INSERT, UPDATE, and DELETE with guardrails. Always use WHERE, test with SELECT first, and use transactions to undo mistakes.

Read EXPLAIN output to understand why queries are slow. Learn when to add indexes and when to stop worrying about performance.

Build a complete pipeline - fetch from an API, load into DuckDB, transform with SQL, export results. A capstone putting it all together.

String formatting in SQL is dangerous. Learn parameterized queries to keep user input safe and prevent SQL injection attacks.

It's not either/or. Use ORMs for CRUD and migrations; use raw SQL for analytics and complex queries. A practical decision guide.

Test queries like Python code using in-memory databases, fixtures, and pytest. Fresh data per test, no state leakage.

Quick tastes of CASE statements, JSON functions, date manipulation, set operations, and recursive CTEs. Enough to know what to learn next.

Celebrate your new skills with take-home challenges and resources for continued learning. You think in sets now.