Python and SQL Together: Building a Data Pipeline (2026)
Python and SQL Together closes this cluster the way it should: not with a new isolated concept, but with a real pipeline that needs several old ones to work at once. This one gets built and run for real, extract, transform, load, verify, using nothing beyond Python's built-in sqlite3 and pandas. Every table shown below is genuine output, not a mockup.
Quick Answer: Python and SQL Together
Python and SQL work together in a data pipeline through three steps: extract data from a database with a SQL query into a pandas DataFrame, transform it with pandas (cleaning, joining, aggregating), and load the result back into a database table or file. A raw sqlite3 connection, with no SQLAlchemy required, is officially supported by pandas for exactly this. This article builds and runs that full pipeline, not just describes it.
One Pipeline, Built for Real
Fifteen articles, one pipeline. A shop's data lives in two SQLite tables, customers and orders, with a couple of missing order amounts left in on purpose.
import sqlite3 import pandas as pd conn = sqlite3.connect("shop.db")
No SQLAlchemy import, no connection string, no credentials. Passing a plain sqlite3 connection straight to pandas is one of three officially supported ways to read SQL into a DataFrame, and it sidesteps a real, well-documented category of version friction between pandas and SQLAlchemy that trips up even experienced users.
Extract: A SQL Join
The extraction step is a single SQL query, joining the two tables the way Article 8 covered doing inside pandas itself, just written in SQL instead:
query = """ SELECT c.customer_id, c.name AS customer, c.signup_date, o.item, o.amount, o.order_date FROM orders o JOIN customers c ON o.customer_id = c.customer_id ORDER BY o.order_date """ df = pd.read_sql_query(query, conn, parse_dates=["signup_date", "order_date"]) print(df)
customer_id customer signup_date item amount order_date 0 1 Alex 2025-11-02 mug 15.0 2026-02-01 1 1 Alex 2025-11-02 pen 5.0 2026-02-03 2 2 Sam 2025-12-15 notebook NaN 2026-02-04 3 2 Sam 2025-12-15 mug 15.0 2026-02-10 4 3 Priya 2026-01-08 pen 5.0 2026-02-11 5 1 Alex 2025-11-02 notebook 10.0 2026-02-14 6 2 Sam 2025-12-15 mug NaN 2026-02-18
parse_dates does the conversion Article 12 covered right at the extraction step, and the dtypes confirm exactly where it lands:
customer_id int64 customer str signup_date datetime64[us] item str amount float64 order_date datetime64[us]
Both text columns come back as pandas 3.0's dedicated str dtype, not the old object dtype, straight out of a SQL query. Both date columns land on microsecond resolution. Neither of those is a coincidence this cluster hasn't already explained, back in Article 6 and Article 12.
Transform: Missing Data Plus GroupBy
Two missing amounts made it through on purpose. This is where Article 7 and Article 10 stop being two separate lessons:
print(df["amount"].isna().sum()) # 2 df["amount"] = df.groupby("customer")["amount"].transform(lambda s: s.fillna(s.mean()))
transform() broadcasts each customer's own mean back onto their own missing rows in one line, the exact pattern Article 7 introduced and Article 10 applied to missing data specifically:
customer item amount 0 Alex mug 15.0 1 Alex pen 5.0 2 Sam notebook 15.0 3 Sam mug 15.0 4 Priya pen 5.0 5 Alex notebook 10.0 6 Sam mug 15.0
Sam's two missing amounts both land on 15.0, Sam's own average from the one real order that had a value. Nothing borrowed from Alex or Priya. Named aggregation, also from Article 7, builds the summary directly:
summary = df.groupby("customer").agg( total_spent=("amount", "sum"), avg_order=("amount", "mean"), order_count=("amount", "count"), first_order=("order_date", "min"), last_order=("order_date", "max"), ).reset_index()
customer total_spent avg_order order_count first_order last_order 0 Alex 30.0 10.0 3 2026-02-01 2026-02-14 1 Priya 5.0 5.0 1 2026-02-11 2026-02-11 2 Sam 45.0 15.0 3 2026-02-04 2026-02-18
Three columns named directly, no MultiIndex to flatten afterward, exactly as Article 7 covered.
Load: SQL and CSV
summary.to_sql("customer_summary", conn, if_exists="replace", index=False) summary.to_csv("customer_summary.csv", index=False) conn.commit()
Two destinations from one DataFrame: a new table inside the same database, and a plain CSV file, whichever the next step in a real workflow actually needs.
Verify: Reading It Back
A load that isn't checked is a guess. Reading the new table back is one query:
check = pd.read_sql_query("SELECT * FROM customer_summary", conn) print(check)
It matches the summary DataFrame exactly, row for row. That confirmation costs one query and catches a whole category of silent failure: a write that ran without error but didn't actually persist what was expected.
SQL and Python, Not SQL or Python
Nothing about this pipeline required choosing sides. The join happened in SQL, where it's a single readable clause. The missing-data handling and aggregation happened in pandas, where transform() and named aggregation express the logic more clearly than the equivalent SQL window functions would for someone still building fluency with both. Neither language did the other's job by force. Each one did the part it's actually better at.
Mistakes That Give Away a Beginner
- Reaching for SQLAlchemy out of habit, when a plain sqlite3 connection is officially supported and simpler for a self-contained project.
- Loading data with to_sql() and trusting it worked without reading anything back to confirm.
- Doing every transformation in pandas out of comfort, even the parts a single SQL join would express more clearly.
- Forgetting if_exists="replace" silently drops and recreates a table. The default, "fail", raises an error instead if the table already exists, safer for a first run.
- Letting the script end before commit() actually saves the written data.
Practice: Do These Three Yourself
Run it yourself
Build the same two-table SQLite database, run the extract, transform, and load steps above end to end, and confirm the summary table matches what's shown here.
Move one step
Take the customer-mean fill from the transform step and rewrite it as a SQL window function inside the extraction query instead, then compare the result.
Break the verify step
Change if_exists="replace" to if_exists="fail" on a table that already exists, run it again, and read the resulting error message closely.
Frequently Asked Questions
How do you connect Python to a SQL database?
For SQLite, sqlite3.connect() with no external library needed. For other databases, a driver plus either a raw connection or a SQLAlchemy engine, depending on what pandas' read_sql() is given.
Do you need SQLAlchemy to use pandas with SQL?
Not for SQLite specifically. pandas officially supports passing a raw sqlite3 connection directly to read_sql() and to_sql(). SQLAlchemy becomes necessary for most other database engines.
Should transformations happen in SQL or in pandas?
Either can work; the better choice depends on which one expresses the specific logic more clearly. A join or filter often reads cleaner in SQL. Row-aware operations like transform() or handling missing data often read cleaner in pandas.
How do you load a pandas DataFrame back into a SQL database?
Call df.to_sql("table_name", conn, if_exists="replace") to write it as a new or replaced table in the connected database.
What's the difference between an ETL and an ELT pipeline?
ETL transforms data before loading it into its destination. ELT loads the raw data first and transforms it afterward, inside the destination system. This article's pipeline is ETL: transform happens in pandas before the load step.
Is SQLite good enough for a real project, or only for practice?
Both. SQLite runs in production for smaller-scale or embedded use cases, not just tutorials. For a multi-user application with heavy concurrent writes, a server-based database is the better fit, but the pandas code covered here works the same way against either.
Conclusion: Nothing New, All of It Real
Fifteen articles, one pipeline, and every piece of it was already covered somewhere earlier in this cluster. The join is Article 8's idea, written in a different language. The missing-data fill is Article 7's transform() applied to Article 10's problem. The dtypes are Article 6's copy-on-write default and Article 12's resolution change, showing up automatically in a query result nobody had to think about twice. None of it was new. All of it was real, run start to finish rather than described in isolated pieces.
That's the actual point of building a pipeline instead of reading about one: the individual lessons stop being individual the moment they have to work together in the same twelve lines.
All 15 Articles, One Roadmap
From variables to a working pipeline. Revisit the full cluster, or start applying it to a real dataset of your own.
▶ Back to the Full Roadmap- pandas.read_sql_query, pandas API reference
- sqlite3, Python standard library documentation
- Review Publically, Pandas GroupBy and Aggregation
- Review Publically, Merging and Joining Pandas DataFrames
- Review Publically, Handling Missing Data in Pandas
- Review Publically, Working with Dates and Time Series in Pandas
- Review Publically, Python for Data Science: The Complete Guide