Python and SQL together

Use SQL and Python together: let the database filter and total the data, then bring the smaller result into Python to reshape, combine, and polish it. SQL is the standard language for asking a database questions, and pandas is the Python tool for working with tables.

Imagine you need monthly sales by region for a report. Pulling every single sale into Python first is slow and can run out of memory, while doing the final reshaping and combining in SQL alone gets awkward fast. Splitting the job between the two plays to each tool’s strengths.

This post closes the core path of the Python for analytics series. By now you can open a notebook (a document that mixes code with its results), shape and merge tables, clean missing values, run a small set of cleaning steps in order, and export your results. Here you put Python and SQL on the same team, and you learn to be careful with AI-written code that only looks right.

Two tools, one question

Start with the decision and not the language. That is the habit from Analytics foundations. Once you know what one row of your result stands for and which filters apply, ask three questions: where does the data live, how big is it, and who must maintain the logic?

If your company’s data warehouse (the central database your company reports from) already holds a curated table and your filter is “last 90 days for one brand,” SQL (or a shared metrics layer built on SQL) is usually faster, cheaper, and easier to govern. If you are combining a customer-system export, a Google Ads export, and a finance sheet that someone adjusted by hand, pandas may be the least bad way to join them until engineering builds a real pipeline (a set of steps you run in order to clean or move data). The spreadsheets series already warned you about unofficial ledgers. Python does not make a bad multi-file process trustworthy, and it only makes the process easier to automate.

Which tool fits which job

The pattern is this: big, shared, repetitive work goes on the SQL side, and awkward, multi-source, exploratory work goes on the Python side. The two overlap in places. Arguments about which tool is purer waste time, while arguments about row counts and who owns the logic save money.

Push filters to SQL

A classic mistake is to run SELECT * FROM enormous_table, load the whole result into pandas, and only then filter it. Your laptop becomes a very expensive network cable. Instead, push your filter conditions and your column list down into the query so the database does that work.

import sqlite3
import pandas as pd

# Demo database in memory (stand-in for warehouse / Postgres / BigQuery)
con = sqlite3.connect(":memory:")

# Seed a tiny "warehouse" table
seed = pd.DataFrame(
    {
        "order_id": [1, 2, 3, 4, 5, 6],
        "brand": ["A", "A", "B", "A", "B", "A"],
        "order_date": [
            "2024-05-01",
            "2024-06-02",
            "2024-06-03",
            "2024-06-10",
            "2024-04-01",
            "2024-06-20",
        ],
        "amount": [10.0, 15.0, 40.0, 12.0, 8.0, 22.0],
        "region": ["East", "East", "West", "West", "East", "East"],
    }
)
seed.to_sql("orders", con, index=False, if_exists="replace")

# Good: filter and select in SQL
sql = """
SELECT
  order_id,
  brand,
  order_date,
  amount,
  region
FROM orders
WHERE brand = ?
  AND order_date >= ?
"""

params = ("A", "2024-06-01")
orders_a = pd.read_sql_query(sql, con, params=params)
print(orders_a)
print("rows pulled", len(orders_a))

Example output:

Even in SQLite (a small database that runs inside a single file), the habit is right: pass values as parameters, name the columns you want, and put the filters in the query. On BigQuery, Snowflake, Redshift, or Postgres, the same idea prevents you from scanning data you do not need. Your Python step should receive a table sized for the decision when possible, and not a souvenir copy of production.

Pull totals, then polish in Python

Example hybrid result table:

Another strong hybrid is to let SQL do the heavy grouping and adding up, then use pandas for presentation logic, a second merge, or a quick what-if scenario.

agg_sql = """
SELECT
  region,
  COUNT(*) AS orders,
  SUM(amount) AS revenue
FROM orders
WHERE order_date >= ?
GROUP BY region
ORDER BY revenue DESC
"""

by_region = pd.read_sql_query(agg_sql, con, params=("2024-06-01",))

# Python polish: share of revenue, friendly labels, export-ready columns
by_region["revenue_share"] = by_region["revenue"] / by_region["revenue"].sum()
by_region["revenue_share_pct"] = (by_region["revenue_share"] * 100).round(1)
by_region["region_label"] = by_region["region"].fillna("Unknown")

print(by_region)

# Optional: merge a small mapping table that lives only as a CSV
region_owner = pd.DataFrame(
    {
        "region": ["East", "West"],
        "owner": ["East lead", "West lead"],
    }
)
handoff = pd.merge(by_region, region_owner, how="left", on="region", validate="one_to_one")
print(handoff)

Example:

SQL computed the durable totals. Python attached a human-made mapping and percentage formatting for a slide or CSV handoff, which the export post in this series covered. Neither tool had to do everything.

A realistic split of responsibilities

• SQL and the warehouse: the official tables everyone agrees on, access control, large scans, approved metrics, and scheduled builds.
• Python: glue between several files, prototypes, custom scoring, complex text cleanup, one-off investigations, and packaging exports.
• Sheets: still fine for tiny models and what-if questions from stakeholders, as long as you remember the risks the spreadsheets series described.

When a Python prototype settles down and three teams depend on it every week, that is a signal to promote the logic into tested SQL or a proper scheduled job. It is not a signal to keep adding notebook cells forever.

AI-generated code: useful draft, untrusted finish

AI models can draft SQL and pandas code quickly. They also invent join keys, forget what one row should stand for, and filter the wrong date column with total confidence. Treat generated code like a junior teammate’s first pass, which is helpful but not authoritative.

Use this review checklist before you run anything on real data:

• What a row means: ask what one output row stands for, and say the answer out loud (analysts call this the grain of the table).
• Keys: check whether the join keys are unique on the side you assume, and whether validate= would pass.
• Filters: look at the time zone, whether the end date is included, deleted rows, and test accounts.
• Missing values: ask whether WHERE status = 'active' quietly drops rows with a missing status that you still need.
• Duplicated rows after a merge: ask whether the merge could multiply revenue by matching one row to several.
• Scope: ask whether the query scans far more history than the question needs.
• Secrets: never paste production passwords into a chat tool, and use environment variables and approved clients instead.

If you use AI to draft SQL, compare it to the habits you learned in the SQL series: name your columns, join on purpose, and check row counts. If you use AI to draft pandas, compare it to the earlier posts in this Python series on merge validation, converting data types with a report of what failed, and export notes. For broader learning paths and related tutorials, start at the Learn hub.

A small end-to-end workflow

Here is a sketch you can adapt:

1. Write the business question in one sentence, and write what one result row stands for in another.
2. Draft SQL that returns the smallest table that still answers the question, using filters, chosen columns, and maybe totals.
3. Pull the result into pandas with read_sql or your warehouse client.
4. Run your cleaning steps only for the problems SQL did not already fix.
5. Merge any local reference files with validate and print the row counts before and after.
6. Summarize the result or build a what-if scenario in Python.
7. Export with a note describing the data, and store the SQL text next to the pipeline.

def pull_brand_orders(con, brand: str, start_date: str) -> pd.DataFrame:
    sql = """
    SELECT order_id, brand, order_date, amount, region
    FROM orders
    WHERE brand = ?
      AND order_date >= ?
    """
    df = pd.read_sql_query(sql, con, params=(brand, start_date))
    df["order_date"] = pd.to_datetime(df["order_date"], errors="coerce")
    df["amount"] = pd.to_numeric(df["amount"], errors="coerce")
    print(f"pulled brand={brand} rows={len(df)} null_amounts={df['amount'].isna().sum()}")
    return df

def region_share(df: pd.DataFrame) -> pd.DataFrame:
    g = (
        df.groupby("region", dropna=False, as_index=False)
        .agg(orders=("order_id", "count"), revenue=("amount", "sum"))
    )
    g["revenue_share"] = g["revenue"] / g["revenue"].sum()
    return g.sort_values("revenue", ascending=False)

sample = pull_brand_orders(con, "A", "2024-06-01")
print(region_share(sample))
con.close()

The recipe is small functions, SQL at the edge, pandas in the middle, and printed checks for trust. It is how a grown-up hybrid workflow (the sequence of steps you follow from raw data to finished result) looks.

Ownership without bureaucracy

Hybrid workflows fail when nobody owns the metric definition. If Finance certifies revenue in the warehouse, your pandas notebook should use that certified version and not invent a cousin metric with a friendlier filter. If no certified table exists, say so in the handoff with a line like “prototype definition, not official revenue.” Being clear beats arguing over territory.

A lightweight rule that works on many teams is that SQL models hold the definitions that several people will reuse next month, while Python holds investigations and glue that may never run again. When a Python path runs every week and three stakeholders depend on it, schedule a conversation about promoting it. Promotion might mean a tool like dbt, which builds and tests SQL tables on a schedule. It might mean a scheduled SQL job, or simply a reviewed script with tests. The point is to decide on purpose and not by tool fashion.

Document the split in the same place you document the pipeline. Write down which filters live in SQL, which enrichments live in Python, and which number is allowed on a slide that leaves the company. That one paragraph prevents duplicate logic better than a long architecture debate.

Common mistakes

• Downloading everything “just in case.” Your computer’s memory (RAM) and your cloud bill both suffer.
• Rebuilding certified metrics in pandas with slightly different filters, and then arguing with Finance about the gap.
• Putting business logic in a calculated field of a business intelligence (BI) tool and again in a notebook, written differently each time.
• Trusting AI-written joins without checking whether one row now matches many.
• Building SQL as a text string with no parameters by gluing user input into it, which risks security holes in apps and plain mess in analytics.
• Stopping at a chart with no saved query or pipeline, so the next person has to start from zero.

Practice

Pick one weekly question that you currently answer with a giant sheet export. Rewrite it in three parts: a SQL query that returns a tight extract, a pandas step that finishes the story, and a CSV plus a short note describing it. Time yourself, then compare the result against the old process. The goal is not fewer tools but fewer surprises.

Series notes and what comes next

This is Part 10 of Python for analytics, the last post on the core path. If you followed the whole series, you now have a practical spine of skills:

• Choosing Python when it earns its keep.
• Setting up your tools without tears.
• Treating a DataFrame as a table.
• Selecting, filtering, and sorting rows.
• Adding up groups of rows with groupby.
• Merging tables while watching what one row stands for.
• Handling missing data and data types.
• Building a pipeline you can rerun.
• Exporting and handing off your results.
• Partnering with SQL and reviewing generated code.

Stretch goals for when you are ready are plotting for analysis, meaning one chart toolkit used for both exploring and explaining, and notebooks versus scripts for sharing work with teammates. Those topics deepen the craft, but you do not need them to do useful work on Monday. Many analysts deliver real value with clean tables, honest joins, and clear handoffs. They do it long before they master a charting library.

Keep your SQL sharp with the SQL series, and keep your spreadsheet habits clean with From spreadsheets to real data. Browse everything on the Learn hub. And when a number matters, still ask what problem you are solving before you open either a query window or a notebook.

Quick recap

• SQL wins on large, shared, approved transformations, and Python wins on messy glue and exploration.
• Push filters and heavy totals down into the database, and pull back tables sized for the decision.
• read_sql plus pandas polish is a standard hybrid and not a compromise.
• AI drafts need a review of row meaning, keys, filters, and duplicated rows before you trust them.
• You finished the core Python path, and plotting and packaging styles are optional next steps.

Sources

• pandas read_sql_query: https://pandas.pydata.org/docs/reference/api/pandas.read_sql_query.html
• Python sqlite3 module: https://docs.python.org/3/library/sqlite3.html
• pandas comparison with SQL: https://pandas.pydata.org/docs/getting_started/comparison/comparison_with_sql.html
• SQLite documentation: https://www.sqlite.org/docs.html
• Analytics Made Simple, SQL series: https://analyticsmadesimple.com/series/sql/
• Analytics Made Simple, Learn hub: https://analyticsmadesimple.com/learn/

Merch on Etsy

Open store →

Naive Bayes Coffee Mug View on Etsy → Apache Iceberg T-Shirt View on Etsy → Standard Deviation T-Shirt View on Etsy → Standard Deviation White Ceramic Mug View on Etsy →