Search⌘ K
AI Features
Home
Courses
Data Science Bootcamp

Connecting Python to SQL

Explore how to connect Python to SQL databases using SQLAlchemy and pandas. Learn when to perform data operations in SQL or pandas, how to load SQL query results into pandas DataFrames, and how to verify joined query results for correct analysis. Understand the trade-offs between moving data and flexible transformations to prepare analysis-ready data efficiently.

We'll cover the following...

A SQLite database can hold customer and order records near the source, while pandas supports flexible analysis after the records arrive in Python. For an analysis-ready orders table, we need customer_id, region, order_total, and order_date in one pandas DataFrame. The choice is not SQL versus pandas as competing tools. The useful question is where each operation should run, based on how much data it removes and how often the analysis will change.

SQLAlchemy is a Python library that provides an engine, meaning an object that manages a connection interface to a database. pandas.read_sql can use that engine to run a SQL query and return the query result as a DataFrame. With SQLite, the database can live entirely in memory, so the example needs no credentials or saved database file.

Load a joined query into pandas

The first step is to make the SQL result explicit before pandas receives it. A join combines the customers and orders tables through customer_id, then SELECT chooses exactly the four fields needed for later analysis.

Python
import pandas as pd
from sqlalchemy import create_engine, text
def main() -> None:
    engine = create_engine("sqlite+pysqlite:///:memory:")
    with engine.begin() as conn:
        conn.execute(
            text(
                """
                CREATE TABLE customers (
                    customer_id INTEGER PRIMARY KEY,
                    region TEXT NOT NULL
                );
                """
            )
        )
        conn.execute(
            text(
                """
                CREATE TABLE orders (
                    order_id INTEGER PRIMARY KEY,
                    customer_id INTEGER NOT NULL,
                    order_total REAL NOT NULL,
                    order_date TEXT NOT NULL,
                    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
                );
                """
            )
        )
        conn.execute(
            text(
                """
                INSERT INTO customers (customer_id, region) VALUES
                    (1, 'North'),
                    (2, 'South'),
                    (3, 'West');
                """
            )
        )
        conn.execute(
            text(
                """
                INSERT INTO orders (order_id, customer_id, order_total, order_date) VALUES
                    (101, 1, 120.50, '2025-01-03'),
                    (102, 1,  75.00, '2025-01-10'),
                    (103, 2, 210.25, '2025-02-02'),
                    (104, 3,  15.99, '2025-02-14');
                """
            )
        )
    query = """
        SELECT
            o.customer_id,
            c.region,
            o.order_total,
            o.order_date
        FROM orders AS o
        INNER JOIN customers AS c
            ON o.customer_id = c.customer_id
        ORDER BY o.order_date, o.order_id;
    """
    df = pd.read_sql(query, engine)
    print(df)
if __name__ == "__main__":
    main()

  • Lines 1–2: The code imports ...