A common mistake among new data scientists is thinking machine learning only happens in Python. When I started out, I would load huge CSV files into Pandas, run heavy transformations, and often crash my computer due to memory issues. In real production settings, machine learning is more practical and less glamorous. To build an ML pipeline with Python and SQL, remember that these tools work best together, not against each other.
In practice, your data is rarely stored in tidy CSV files. Instead, it lives in databases, warehouses, or data lakes. Pulling large amounts of raw data over the network to process in Python is slow and costly. My approach is straightforward: use the database for filtering, aggregating, and joining data, and let Python handle complex transformations and model training.
Today, I’ll show you how to build a complete machine learning pipeline that connects SQL and Python smoothly.
Why Pair SQL with Python?
Before we get started, let’s look at how a typical production pipeline works. An ML pipeline is just a series of automated steps that turn raw data into something useful, train a model, and make predictions.
When building an ML pipeline with Python and SQL, each tool has its own role:
- Data Extraction (SQL): Here, you write queries to pull just the data you need. Instead of downloading everything, you can use SQL to filter out missing records, join tables for user profiles and transactions, or summarize daily metrics. SQL engines are built for these tasks.
- Preprocessing & Feature Engineering (Python): After bringing the filtered data into Python, usually as a Pandas DataFrame, you can do things that are harder in SQL. This includes scaling numbers, encoding categories, filling in missing values, or creating new features.
- Model Training & Evaluation (Python): Now, libraries like Scikit-Learn, XGBoost, or PyTorch come into play. You split your data, train your model, and test how well it works on new data.
- Prediction & Automation: Finally, you bundle all the steps from preprocessing to prediction into one object. This way, any new data that comes in will go through the same process as your training data.
Build an ML Pipeline With Python and SQL
To show how this works, we’ll build a regression model to predict housing prices. We’ll use Python’s sqlite3 library to set up a local SQL database, load the California Housing dataset, and run our pipeline.
Step 1: Setting up the Database and Extracting Data
First, we’ll create a database and write a SQL query to get our training data. In a real job, the database would already be set up (such as PostgreSQL, Snowflake, or BigQuery), and you’d connect to it with a library like SQLAlchemy or psycopg2:
import sqlite3
import pandas as pd
from sklearn.datasets import fetch_california_housing
# 1. Load the dataset
data = fetch_california_housing(as_frame=True)
df = data.frame
# 2. Create an in-memory SQLite database to simulate a production DB
conn = sqlite3.connect(':memory:')
df.to_sql('housing_data', conn, index=False)
# 3. Data Extraction using SQL
# We use SQL to filter out potential outliers right at the source
query = """
SELECT
MedInc, HouseAge, AveRooms, AveBedrms, Population, AveOccup,
Latitude, Longitude, MedHouseVal
FROM housing_data
WHERE HouseAge > 5 AND AveRooms < 15
"""
# Load the queried data directly into a Pandas DataFrame
extracted_df = pd.read_sql_query(query, conn)
print("Data extracted successfully. Shape:", extracted_df.shape)By using a WHERE clause in our SQL query, we quickly cut down the amount of data sent to Python by filtering out very new houses and properties with too many rooms. This is the right way to approach data extraction on the job.
Step 2: Defining the ML Pipeline
Now that we have the data in Python, we need to get it ready for our model. Early in my career, I used to transform the whole dataset before splitting it into training and test sets. This leads to data leakage, since the scaler learns from all the data, including the test set, which it shouldn’t see.
To avoid this, we put our preprocessing steps and model together in a Pipeline:
from sklearn.model_selection import train_test_split
from sklearn.pipeline import Pipeline
from sklearn.preprocessing import StandardScaler
from sklearn.ensemble import RandomForestRegressor
from sklearn.metrics import mean_squared_error, r2_score
# 1. Separate features (X) and target (y)
X = extracted_df.drop('MedHouseVal', axis=1)
y = extracted_df['MedHouseVal']
# 2. Train/Test Split
# We split the data BEFORE applying any transformations
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)
# 3. Build the Pipeline
# Step 1: Scale the numerical features
# Step 2: Train a Random Forest Regressor
ml_pipeline = Pipeline(steps=[
('scaler', StandardScaler()),
('model', RandomForestRegressor(n_estimators=100, random_state=42))
])Step 3: Training, Evaluation, and Prediction
Once the pipeline is set up, you can train the model just by calling .fit(). The pipeline will first scale X_train, then send it to the Random Forest model:
# 4. Train the model using the pipeline
ml_pipeline.fit(X_train, y_train)
# 5. Make predictions on the test set
# The pipeline automatically applies the scaling to X_test using the parameters learned from X_train
predictions = ml_pipeline.predict(X_test)
# 6. Evaluate the model
mse = mean_squared_error(y_test, predictions)
r2 = r2_score(y_test, predictions)
print(f"Mean Squared Error: {mse:.4f}")
print(f"R-squared Score: {r2:.4f}")See how simple the prediction step is. Since we used a pipeline, we don’t need to transform X_test by hand before making predictions. The Pipeline object takes care of everything. If we wanted to turn this model into an API, we’d just save the ml_pipeline object. When new data arrives, the pipeline will scale it and make a prediction automatically.
Want to Take This Pipeline Further?
Building an ML pipeline is even more valuable when you understand what happens beyond just the model. My book, From ML Algorithms to GenAI & LLMs, covers everything from basic machine learning to the modern concepts you’ll need for real-world projects.
After you’re comfortable with the ML workflow in this tutorial, I recommend checking out Data I/O and Preprocessing with Python and SQL. This course focuses on handling real-world data, pulling it from databases, and cleaning and transforming it with both Python and SQL. It’s a great next step to build on the skills you’ve learned here.
The Takeaway
If you remember one thing from this exercise, it’s that good machine learning engineering isn’t about creating the most complex algorithms from scratch. It’s about building strong, logical systems.
Learning to build an ML pipeline with Python and SQL helps you move beyond the notebook data scientist mindset and think like an AI engineer. You start to appreciate what each tool does best. SQL is like heavy machinery for moving and shaping raw data, while Python is your precise toolkit for finishing the job.
I hope you enjoyed this article on building an ML pipeline with Python and SQL. For more AI and machine learning tips, feel free to follow me on Instagram.
