How Python and SQL Work Together in Real-World Data Analysis

Modern data analysis rarely happens entirely inside one tool. In many real-world projects, data lives in a database, where SQL is used to retrieve and organize it. Python then helps analysts explore the results further, automate repeated tasks, perform more advanced calculations, and create visualizations.

The important point is that Python and SQL are not competitors. They solve different parts of the same workflow.

This article walks through a realistic example of how they can work together, from querying raw business data with SQL to analyzing the results with Python.

A Realistic Scenario: An Online Store Wants to Understand Its Sales

Imagine an online store that keeps its data in a database. It has three tables:

• customers
• orders
• order_items

The business team wants answers to questions such as:

• Which customers generated the most revenue?
• Which product categories performed best?
• How did sales change over time?
• Are there unusual changes that need investigation?

The database may contain thousands or millions of rows. Instead of exporting every row and processing everything in Python, it is usually more efficient to let SQL do the first stage of the work.

Step 1: Use SQL to Retrieve Only the Data You Need

Suppose the orders table contains order-level information and the order_items table contains individual products purchased in each order.

A SQL query can join the tables, filter the relevant date range, and calculate daily revenue:

SELECT
    o.order_date,
    SUM(oi.quantity * oi.unit_price) AS daily_revenue,
    COUNT(DISTINCT o.order_id) AS total_orders
FROM orders AS o
JOIN order_items AS oi
    ON o.order_id = oi.order_id
WHERE o.order_date >= '2026-01-01'
GROUP BY o.order_date
ORDER BY o.order_date;

This single query performs several important tasks.

First, the JOIN connects related information from two tables. The WHERE clause limits the analysis to the required period. The SUM() function calculates revenue, while COUNT(DISTINCT ...) counts unique orders. Finally, GROUP BY turns many individual transactions into a smaller daily summary.

This is where SQL is particularly useful: filtering, joining, grouping, and aggregating data close to where the data is stored.

Rather than moving an entire database into Python, the analyst receives a smaller and more relevant result set.

Step 2: Bring the SQL Results into Python

Python can connect to databases and work with query results. For a simple example, SQLite can be accessed through Python's built-in sqlite3 module.

The Python sqlite3 documentation describes the module's interface for working with SQLite databases. The pandas read_sql documentation explains how SQL query results can be loaded into a pandas DataFrame.

Here is a simplified example:

import sqlite3
import pandas as pd

connection = sqlite3.connect("store_data.db")

query = """
SELECT
    o.order_date,
    SUM(oi.quantity * oi.unit_price) AS daily_revenue,
    COUNT(DISTINCT o.order_id) AS total_orders
FROM orders AS o
JOIN order_items AS oi
    ON o.order_id = oi.order_id
WHERE o.order_date >= '2026-01-01'
GROUP BY o.order_date
ORDER BY o.order_date;
"""

df = pd.read_sql_query(
    query,
    connection,
    parse_dates=["order_date"]
)

connection.close()

At this point, df contains the results of the SQL query in a pandas DataFrame.

The division of work is clear: SQL prepared the data. Python now provides a flexible environment for analyzing it further.

Step 3: Use Python for Deeper Analysis

Suppose the business team wants to understand whether revenue is generally increasing and whether there are days with unusual performance.

Python can calculate a rolling average:

df["7_day_average"] = (
    df["daily_revenue"]
    .rolling(window=7)
    .mean()
)

This creates a smoother view of the data by calculating the average revenue over the previous seven days.

We can also calculate day-to-day percentage changes:

df["daily_change_pct"] = (
    df["daily_revenue"]
    .pct_change() * 100
)

Now the analyst can identify days where revenue changed sharply. For example:

unusual_days = df[
    df["daily_change_pct"].abs() > 30
]

print(unusual_days)

This does not automatically explain why revenue changed. Instead, it helps identify where further investigation may be useful. An analyst might then return to SQL and investigate those specific dates, products, customers, or regions.

Step 4: Create a Visualization

Python is also useful for turning query results into something easier to understand. For example:

import matplotlib.pyplot as plt

plt.plot(
    df["order_date"],
    df["daily_revenue"]
)

plt.xlabel("Date")
plt.ylabel("Revenue")
plt.title("Daily Revenue Over Time")
plt.xticks(rotation=45)
plt.tight_layout()
plt.show()

A chart can reveal patterns that are difficult to notice in a table of numbers. Perhaps revenue rises steadily before a seasonal event. Perhaps there is a sudden drop on one particular day. Perhaps weekends consistently perform differently from weekdays.

The chart does not replace SQL. It builds on the data that SQL has already prepared.

When Should Processing Happen in SQL?

A useful rule is to ask whether the database can perform the operation efficiently before moving the data elsewhere. SQL is often a good choice for:

• Filtering large datasets
• Selecting only required columns
• Joining related tables
• Grouping and aggregating data
• Calculating database-friendly summaries
• Reducing the amount of data transferred to Python

For example, if a database contains 20 million transactions but the analysis only needs monthly revenue by country, it usually makes little sense to load all 20 million rows into a local DataFrame first. A query can summarize the data inside the database:

SELECT
    country,
    strftime('%Y-%m', order_date) AS month,
    SUM(amount) AS revenue
FROM orders
GROUP BY country, month;

Python can then work with the much smaller result. This approach can reduce memory usage and unnecessary data transfer.

When Does Python Become More Useful?

Python becomes particularly valuable when the analysis needs flexibility beyond a standard database query. Examples include:

• Complex statistical analysis
• Data cleaning involving multiple steps
• Repeated analysis and automation
• Custom business logic
• Time-series analysis
• Machine learning
• Visualization
• Combining database data with files or APIs
• Generating reports automatically

For example, an analyst may retrieve monthly sales with SQL and then use Python to calculate growth rates, compare trends, create charts, and automatically export a report.

The pandas SQL I/O documentation provides additional guidance on working with SQL data in pandas, including database connections and reading SQL results.

If you want to go deeper on either end of this list, our descriptive statistics in data science guide and our machine learning portfolio projects guide both build directly on this kind of SQL-prepared dataset.

A Complete Example Workflow

A practical workflow might look like this.

SELECT
    c.customer_id,
    c.customer_segment,
    MAX(o.order_date) AS last_order_date,
    COUNT(o.order_id) AS total_orders,
    SUM(o.order_total) AS lifetime_value
FROM customers AS c
LEFT JOIN orders AS o
    ON c.customer_id = o.customer_id
GROUP BY
    c.customer_id,
    c.customer_segment;

df = pd.read_sql_query(query, connection)

df["last_order_date"] = pd.to_datetime(
    df["last_order_date"]
)

df["days_since_last_order"] = (
    pd.Timestamp.today()
    - df["last_order_date"]
).dt.days

def customer_status(days):
    if days <= 30:
        return "Active"
    elif days <= 90:
        return "At Risk"
    return "Inactive"

df["customer_status"] = (
    df["days_since_last_order"]
    .apply(customer_status)
)

The final result is more useful than either a raw SQL result or an isolated Python script. SQL retrieves and structures the information, while Python helps turn that information into analysis.

Connecting Python to Different Databases

SQLite is convenient for learning and small projects because it can be accessed through Python's standard sqlite3 module. In production environments, analysts may work with databases such as PostgreSQL, MySQL, or other systems.

The connection method may change, but the overall workflow remains similar:

• Establish a database connection
• Define a query
• Retrieve the required data
• Load it into a Python structure such as a DataFrame
• Perform additional analysis
• Communicate or store the results

For broader database workflows, pandas can work with database connections supported through appropriate database interfaces and drivers.

Common Mistakes When Moving Data Between SQL and Python

Using SQL and Python together is powerful, but several mistakes can make the workflow slower or harder to maintain.

Performance Considerations: SQL First or Python First?

There is no single answer for every project. The decision depends on the size of the data, the database, available computing resources, and the type of analysis. However, a practical pattern is:

For example, imagine a table with millions of events.

For larger result sets, pandas also supports reading SQL results in chunks, which can be useful when loading everything into memory at once is not practical.

Best Practices for Reproducible and Maintainable Workflows

A strong analysis should be understandable not only today, but also weeks or months later. Here are several useful practices.

• Keep SQL queries readable with meaningful table aliases and clear formatting.
• Separate configuration, such as database credentials, file paths, and environment settings, from analysis code.
• Use version control so it's easy to see what changed and why.
• Document assumptions, such as excluded refunded orders or an active-customers-only filter.
• Validate results by comparing totals and inspecting sample rows rather than trusting a query just because it ran.
• Automate repeated reports instead of rebuilding them manually every week.
• Keep the workflow modular: separate extraction, cleaning, analysis, visualization, and reporting into distinct stages.

The Real Skill Is Knowing Which Tool to Use When

Python and SQL work best together when each is used for the tasks it handles well.

SQL is effective for retrieving, filtering, joining, and aggregating data directly in a database. Python provides a flexible environment for deeper analysis, automation, visualization, and custom logic.

In a real-world workflow, the process may begin with SQL, continue in Python, and return to SQL when new questions emerge. The goal is not to choose one tool over the other. The goal is to create a workflow that is efficient, reproducible, and useful for answering a real question.

Frequently Asked Questions