Course index · Previous · Next

Use SQLite and Reconcile SQL Results with pandas

Module 7: Reliable Analysis Workflows · Lecture 25 of 32

Move a small analytical table into SQLite and calculate the same KPI in SQL and pandas. Use agreement as a useful cross-check while remembering that two implementations can share the same mistaken business definition.

What you will learn

Understand the problem

Push computation to the appropriate boundary

A database can filter and aggregate near stored data, reducing what must be transferred into memory. pandas is convenient for exploratory transformations, custom checks, and visualization. Neither tool is inherently more correct. Define the population and metric first, then choose the execution engine that fits volume, operational constraints, and maintainability.

SQLite is a local teaching database

Python's sqlite3 module supports a database in memory, so this example needs no separate server or credentials. We copy only the columns used by the query, preserving integer monetary values. Real systems may enforce types and transactions differently, and SQL syntax can differ across SQLite, PostgreSQL, and other engines.

Bind values, do not interpolate them

The status exclusion is supplied as a query parameter. Parameter binding keeps a value separate from SQL syntax and avoids errors caused by quoting or malicious input. Table and column identifiers cannot generally be substituted as ordinary bound values; when dynamic identifiers are necessary, choose them from a controlled allowlist rather than concatenating arbitrary input.

Compare meaning as well as values

The SQL and pandas versions must exclude the same statuses, group by the same normalized channel, and sum the same net_paise column. Compare sorted keys and exact integer totals. Agreement is evidence against implementation drift, but not proof that the shared source or metric contract is correct.

Run the worked example

Download and extract the course kit linked below. Open a terminal in the extracted folder, complete the setup in Lecture 2, and run python run_course.py --lesson 25 --examples-only. Each script starts from the shared dataset; it does not depend on a notebook cell from another lecture. All retail records are fictional and amounts are integer paise unless a chart explicitly says INR.

from pathlib import Path
import numpy as np
import pandas as pd
from course_data import load_orders, load_raw, load_customers, clean_orders, ROOT, OUT

import sqlite3
from contextlib import closing
orders = load_orders()
with closing(sqlite3.connect(':memory:')) as connection:
    orders[['order_id', 'channel', 'status', 'net_paise']].to_sql('orders', connection, index=False)
    sql = pd.read_sql_query(
        'SELECT channel, COUNT(*) AS orders, SUM(net_paise) AS net_paise '
        'FROM orders WHERE status <> ? GROUP BY channel ORDER BY channel',
        connection, params=('cancelled',))
expected = orders.loc[orders['status'].ne('cancelled')].groupby('channel').agg(
    orders=('order_id', 'size'), net_paise=('net_paise', 'sum')).sort_index()
actual = sql.set_index('channel').sort_index()
assert actual.index.tolist() == expected.index.tolist()
assert actual['orders'].tolist() == expected['orders'].tolist()
assert actual['net_paise'].tolist() == expected['net_paise'].tolist()
print(sql.to_string(index=False))

Verified output

channel  orders  net_paise
  email      75    8045280
organic      75    8311030
   paid      73    7596310
unknown       1     159800

Walk through the result

The context manager controls the connection lifetime for this short example, and all aggregation occurs before the SQL result is loaded back into a DataFrame. Parameters supply the cancelled status as a value. Comparing lists after sorting avoids confusing harmless dtype differences with monetary differences. The exact paise comparison is stronger than comparing rounded rupee displays.

Common mistakes and diagnosis

Do not load a huge raw table into pandas merely to discard almost all of it. Do not build SQL by inserting user-provided strings. NULL grouping and join semantics can differ from pandas missing-value behavior, so test those cases explicitly. COUNT(column) ignores NULL while COUNT(*) counts rows. Floating-point money columns can produce differences that integer minor-unit storage avoids.

Practice assignment

Add an SQL query that returns overall non-cancelled order count and net revenue. Compare its AOV with the pandas result. Explain why AVG(net_paise) over all statuses would not implement this course's AOV contract.

Worked solution and explanation

Add the following code below the worked example, or run the matching file in the solutions folder. Compare the result with the business definition before trusting a passing assertion.

with closing(sqlite3.connect(':memory:')) as connection:
    orders[['status', 'net_paise']].to_sql('orders', connection, index=False)
    total = pd.read_sql_query('SELECT COUNT(*) AS n, SUM(net_paise) AS total FROM orders WHERE status <> ?',
                              connection, params=('cancelled',)).iloc[0]
eligible = orders.loc[orders['status'].ne('cancelled')]
assert np.isclose(total['total'] / total['n'], eligible['net_paise'].sum() / len(eligible))

Check your understanding

What does SQL/pandas agreement fail to prove?

Answer guide

Both implementations can use the same incomplete export, wrong status rule, duplicated grain, or incorrectly defined amount. Agreement checks implementation consistency. Source validation, metric review, coverage evidence, and independent row-level examples remain necessary to establish that the result answers the intended question.

Reference and next step

Download the complete course kit: datasets, all examples, solutions, and capstone.

Return to the full Data Analysis with Python and pandas curriculum.

Course index · Previous · Next