0Pricing
Pandas & NumPy Academy · Lesson

Running SQL Queries from Pandas

Execute arbitrary SELECT statements with pd.read_sql_query and parameterise queries safely to avoid SQL injection.

Running SQL Queries from Pandas is a free Pandas & NumPy Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the Pandas & NumPy Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

pd.read_sql: The Unified Interface

Pandas provides three SQL reading functions: pd.read_sql() (generic wrapper), pd.read_sql_table() (reads a full table by name), and pd.read_sql_query() (executes arbitrary SQL). For most analytical workflows, pd.read_sql_query() is the most powerful because it lets you write any SELECT statement with filtering, joining, and aggregating before data reaches Pandas. Using SQL for heavy lifting and Pandas for the final analysis is often more efficient than loading everything and filtering in Python.

import pandas as pd
import sqlalchemy as sa

engine = sa.create_engine('sqlite:///ecommerce.db')

# Three equivalent patterns
df1 = pd.read_sql('SELECT * FROM orders LIMIT 100', con=engine)
df2 = pd.read_sql_table('orders', con=engine)  # full table
df3 = pd.read_sql_query('SELECT * FROM orders LIMIT 100', con=engine)

print(df3.head())
print(df3.columns.tolist())

Filtering at the Database Level

Always filter data in SQL rather than loading everything and filtering in Pandas. A database with proper indexes can execute a WHERE clause on millions of rows and return only thousands in milliseconds, while Pandas would need to load gigabytes of data first. The golden rule: push predicates to the database. Use WHERE for row filters, SELECT col1, col2 for column selection, and LIMIT during development to preview results quickly.

import pandas as pd
import sqlalchemy as sa

engine = sa.create_engine('sqlite:///sales.db')

# Filter and project at SQL level — only fetch what you need
query = '''
    SELECT order_id, customer_id, amount, status
    FROM orders
    WHERE status = 'completed'
      AND order_date >= '2024-01-01'
      AND amount > 50
    LIMIT 1000
'''

df = pd.read_sql_query(query, con=engine)
print(f'Rows: {len(df)}, Columns: {list(df.columns)}')

Aggregating in SQL vs Pandas

For simple group-level summaries over large tables, SQL aggregations outperform Pandas because the database engine can use indexes, parallel execution, and hash aggregation on disk. Use SQL for GROUP BY and SUM/COUNT/AVG when the table is large. Load the aggregated result (a small DataFrame) into Pandas for further analysis, visualisation, or combination with other data. For complex custom aggregations that SQL cannot express, load a filtered subset into Pandas and use groupby there.

import pandas as pd
import sqlalchemy as sa

engine = sa.create_engine('sqlite:///sales.db')

# Aggregate in SQL — returns a small result set
query = '''
    SELECT region,
           COUNT(*) AS order_count,
           ROUND(SUM(amount), 2) AS total_revenue,
           ROUND(AVG(amount), 2) AS avg_order_value
    FROM orders
    WHERE status = 'completed'
    GROUP BY region
    ORDER BY total_revenue DESC
'''

df = pd.read_sql_query(query, con=engine)
print(df)

JOINs in SQL Queries

SQL JOIN operations are more efficient than Pandas merge() for joins on large tables because the database can use indexed lookups. Write your join in SQL and receive a pre-joined, potentially filtered result in Pandas. For multi-table analysis, a single SQL query with multiple JOINs is typically faster than reading each table separately and merging in Pandas, especially when one table has millions of rows and the join reduces the result significantly.

import pandas as pd
import sqlalchemy as sa

engine = sa.create_engine('sqlite:///ecommerce.db')

query = '''
    SELECT o.order_id,
           c.customer_name,
           c.country,
           p.product_name,
           o.amount
    FROM orders o
    JOIN customers c ON o.customer_id = c.id
    JOIN products p ON o.product_id = p.id
    WHERE o.status = 'completed'
    LIMIT 500
'''

df = pd.read_sql_query(query, con=engine)
print(df.head())

Using Python Variables in Queries

To inject Python variables into SQL queries safely, use SQLAlchemy's text() with named parameters. Define placeholders with :param_name in the query string and pass a dictionary to the params argument of read_sql_query. This works for both single values and — with some databases — for lists. Avoid f-strings or % formatting to build query strings from variables; they are unsafe even for internal use.

import pandas as pd
import sqlalchemy as sa

engine = sa.create_engine('sqlite:///sales.db')

# Python variables to inject
min_amount = 200.0
start_date = '2024-01-01'
end_date = '2024-12-31'

query = sa.text('''
    SELECT * FROM orders
    WHERE amount > :min_amount
      AND order_date BETWEEN :start_date AND :end_date
''')

with engine.connect() as conn:
    df = pd.read_sql_query(query, con=conn,
                           params={'min_amount': min_amount,
                                   'start_date': start_date,
                                   'end_date': end_date})
print(f'{len(df)} orders found')

Using CTEs and Subqueries

Complex analyses often require Common Table Expressions (CTEs) or subqueries. These are fully supported by pd.read_sql_query — just pass the entire multi-clause SQL as the query string. CTEs (introduced with the WITH keyword) make complex queries more readable by naming intermediate results. This is useful for running-total calculations, ranking within groups, and multi-step filtering that would be verbose in Pandas.

import pandas as pd
import sqlalchemy as sa

engine = sa.create_engine('sqlite:///sales.db')

query = '''
    WITH monthly_revenue AS (
        SELECT strftime('%Y-%m', order_date) AS month,
               SUM(amount) AS revenue
        FROM orders
        WHERE status = 'completed'
        GROUP BY month
    )
    SELECT month,
           revenue,
           revenue - LAG(revenue) OVER (ORDER BY month)
               AS month_over_month_change
    FROM monthly_revenue
    ORDER BY month
'''

df = pd.read_sql_query(query, con=engine)
print(df.tail())

Reading with a DatetimeIndex

When reading time series data from a database, set the timestamp column as the DataFrame's index by passing index_col='date_column' and parse_dates=['date_column'] to read_sql_query. This gives you a DatetimeIndex directly, enabling Pandas time-based slicing (df['2024-01']), resampling, and rolling calculations without extra post-processing steps. The parse_dates argument tells Pandas to convert the column to datetime64.

import pandas as pd
import sqlalchemy as sa

engine = sa.create_engine('sqlite:///metrics.db')

df = pd.read_sql_query(
    'SELECT recorded_at, metric_value FROM daily_metrics ORDER BY recorded_at',
    con=engine,
    index_col='recorded_at',
    parse_dates=['recorded_at']
)
print(df.index.dtype)   # datetime64[ns]
print(df['2024-06'])    # Slice by month directly

Profiling Slow Queries

When a query is slow, add the SQL keyword EXPLAIN (or EXPLAIN QUERY PLAN in SQLite) before your SELECT to see the database's execution plan. Look for full table scans ('SCAN TABLE') where you would expect index lookups ('SEARCH TABLE'). Missing indexes on WHERE and JOIN columns are the most common cause of slow queries. Create the appropriate index in the database and re-check with EXPLAIN before re-running the Pandas pipeline.

import sqlalchemy as sa

engine = sa.create_engine('sqlite:///sales.db')

# Check if the query uses an index
with engine.connect() as conn:
    plan = conn.execute(sa.text(
        'EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 42'
    )).fetchall()
    for row in plan:
        print(row)
    # Look for 'SEARCH TABLE orders USING INDEX' — not 'SCAN TABLE'

Pagination for Large Result Sets

When iterating over a large result set interactively (e.g., processing one page of results at a time), use SQL LIMIT and OFFSET to implement pagination. Fetch N rows at a time, process them, then fetch the next N. While this is less efficient than the chunksize approach (which maintains a cursor), pagination is useful when rows must be shown incrementally in a report or when combining results from multiple queries.

import pandas as pd
import sqlalchemy as sa

engine = sa.create_engine('sqlite:///sales.db')
page_size = 10000
offset = 0

while True:
    query = sa.text(
        'SELECT * FROM orders ORDER BY order_id LIMIT :limit OFFSET :offset'
    )
    with engine.connect() as conn:
        df = pd.read_sql_query(query, con=conn,
                               params={'limit': page_size, 'offset': offset})
    if len(df) == 0:
        break
    print(f'Page at offset {offset}: {len(df)} rows')
    offset += page_size

Combining SQL Queries with Pandas Logic

The most powerful pattern is a hybrid pipeline: use SQL for coarse-grained filtering and aggregation, then use Pandas for fine-grained transformations that SQL expresses awkwardly (pivot tables, string parsing, apply functions, rolling windows). Read a manageable result set from SQL (thousands of rows), then chain Pandas operations on the resulting DataFrame. This combines the strengths of both tools while keeping the data flowing through a single Python process.

import pandas as pd
import sqlalchemy as sa

engine = sa.create_engine('sqlite:///sales.db')

# SQL: coarse filter and join
df = pd.read_sql_query('''
    SELECT o.customer_id, o.amount, o.order_date, c.country
    FROM orders o JOIN customers c ON o.customer_id = c.id
    WHERE o.status = 'completed'
''', con=engine, parse_dates=['order_date'])

# Pandas: rolling 30-day revenue per country
df = df.sort_values('order_date')
df['rolling_30d'] = (
    df.groupby('country')['amount']
    .transform(lambda x: x.rolling('30D').sum())
)
print(df.head())

Error Handling for Database Queries

Database queries can fail due to network timeouts, syntax errors, or lost connections. Wrap database calls in try-except blocks that catch sqlalchemy.exc.OperationalError for connection issues and sqlalchemy.exc.ProgrammingError for SQL syntax errors. Log the error with context (query, parameters) and either retry with exponential backoff or fail gracefully. In production pipelines, distinguishing transient errors (retry-able) from permanent errors (fix the SQL) is essential.

import pandas as pd
import sqlalchemy as sa

engine = sa.create_engine('sqlite:///sales.db')

try:
    df = pd.read_sql_query(
        'SELECT * FROM nonexistent_table',
        con=engine
    )
except sa.exc.OperationalError as e:
    print(f'Connection or table error: {e}')
except sa.exc.ProgrammingError as e:
    print(f'SQL syntax error: {e}')
except Exception as e:
    print(f'Unexpected error: {type(e).__name__}: {e}')

Quick Check

Test your understanding of Data Analysis concepts from this lesson.

Lesson Recap

In this lesson you learned: pd.read_sql_query() executes any SQL SELECT and returns a DataFrame, pushing filters and aggregations to SQL is more efficient than loading full tables into Pandas, and hybrid pipelines combine SQL for coarse data reduction with Pandas for fine-grained custom transformations. Next up we learn how to write DataFrames back into database tables.

Frequently asked questions

Is the “Running SQL Queries from Pandas” lesson free?

Yes — the full text of “Running SQL Queries from Pandas” is free to read here on the web, and the Pandas & NumPy Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the Pandas & NumPy Academy course, upgrade to CoddyKit PRO.

What will I learn in “Running SQL Queries from Pandas”?

Execute arbitrary SELECT statements with pd.read_sql_query and parameterise queries safely to avoid SQL injection. You practise Pandas & NumPy Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start Pandas & NumPy Academy?

No prior experience is required. Pandas & NumPy Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Running SQL Queries from Pandas” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this Pandas & NumPy Academy lesson?

Yes. Every Pandas & NumPy Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Connecting to a Database with SQLAlchemy
  2. Running SQL Queries from Pandas
  3. Writing DataFrames to Database Tables
  4. Pandas vs. SQL: Choosing the Right Tool
← Back to Pandas & NumPy Academy