Summarize customer paid orders

Before you begin

Python 3.12 or later.
This report expects the sample customers/products/orders/order_items schema; adapt its SELECT for another schema.

What this script changesThe source database is opened using SQLite mode=ro with query_only enabled and a 20-second query progress limit. No source rows or schema are changed. Use a consistent backup if another application is actively writing. Reports can contain private database contents.

Set up your example

Save as sqlite-customer-paid-totals.py.
Open a terminal in its folder.
Run python "sqlite-customer-paid-totals.py" --help and prepare the listed input files.
Save the supplied sample.sql and create_sample_db.py together in the same folder.
For the included educational fixture, run python create_sample_db.py --output sample.db. It refuses to overwrite an existing file.
The fixture intentionally contains duplicate customer emails, one NULL email, and one orphan order so audit examples have visible findings.

Sample files in this download:

sample.sql
create_sample_db.py

Download the ZIP, extract it, and open a terminal inside the extracted folder. The source file is sqlite-customer-paid-totals.py.

Run the script

python "sqlite-customer-paid-totals.py"

If your system uses python3 instead of python, use that command for Python examples. Keep paths containing spaces in quotes. Required inputs and optional settings are described in the code and setup steps.

What to expect

Ada has two paid orders totaling 28; customers with none still appear with zero.

The complete script

#!/usr/bin/env python3
"""Join sample customers and paid orders to report counts and lifetime paid totals."""
import argparse
import sys
import json
from pathlib import Path
import sqlite3
import time
from contextlib import closing

def identifier(value):
    return '"'+value.replace('"','""')+'"'

def connect(path):
    path=path.resolve()
    if not path.is_file():raise ValueError('Database must already exist')
    connection=sqlite3.connect(path.as_uri()+'?mode=ro',uri=True,timeout=5)
    connection.row_factory=sqlite3.Row
    connection.execute('PRAGMA query_only=ON')
    deadline=time.monotonic()+20
    connection.set_progress_handler(lambda: int(time.monotonic()>deadline),10000)
    return connection

def table(connection,name):
    if not connection.execute("SELECT 1 FROM sqlite_schema WHERE type='table' AND name=?",(name,)).fetchone():raise ValueError('Unknown table: '+name)
    return identifier(name)

def column(connection,name,field):
    names=[row['name'] for row in connection.execute('PRAGMA table_info('+table(connection,name)+')')]
    if field not in names:raise ValueError('Unknown column: '+field)
    return identifier(field)

def json_default(value):
    if isinstance(value,bytes):return {'blob_hex':value.hex()}
    raise TypeError(type(value).__name__)

def emit(rows):
    print(json.dumps([dict(row) for row in rows],indent=2,default=json_default))

def main():
    parser = argparse.ArgumentParser(description='Join sample customers and paid orders to report counts and lifetime paid totals.')
    parser.add_argument("--database",type=Path,default=Path("sample.db"))
    args = parser.parse_args()
    try:
        with closing(connect(args.database)) as connection:
            emit(connection.execute("SELECT c.id,c.name,COUNT(o.id) AS paid_orders,ROUND(COALESCE(SUM(o.total),0),2) AS paid_total FROM customers c LEFT JOIN orders o ON o.customer_id=c.id AND o.status='paid' GROUP BY c.id,c.name ORDER BY paid_total DESC,c.id"))
    except sqlite3.Error as exc:
        raise RuntimeError(str(exc))

if __name__ == '__main__':
    try:
        main()
    except (OSError, ValueError, RuntimeError) as exc:
        print(f'Error: {exc}', file=sys.stderr)
        sys.exit(1)

Open source as plain text · Download source and sample files

How it works

Join sample customers and paid orders to report counts and lifetime paid totals.
Arguments are explicit and expected input errors produce a message with a nonzero exit code.
Results reflect your selected local data or the specific endpoint you choose; the example illustrates the result shape.

Make it your own

Adapt the task

Adjust the paid-status condition in the JOIN for your application; currency and refund rules need explicit design.

Adjust presentation

Edit final print statements or json.dumps indentation to adjust the report layout. Keep input validation and exclusive-create file modes intact.

Change one setting at a time, run it again on sample data, and compare the output. The customization guide walks through this process.

If something goes wrong

An input or dependency is unavailable.

This report expects the sample customers/products/orders/order_items schema; adapt its SELECT for another schema.

Actual results differ from the example.

Inputs and system state determine values. Review each finding in context; a report is not proof of system health or a complete security assessment.

Reference & next steps

These official references explain the underlying tools. The Scriptage example and guide are original instructional content. See verification notes for what has been checked and the limits of those checks.

Copies a template for your own notes. It does not send anything.