Find orders without customers

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.

Set up your example

1. Save as sqlite-orphan-order-report.py.
2. Open a terminal in its folder.
3. Run python "sqlite-orphan-order-report.py" --help and prepare the listed input files.
4. Save the supplied sample.sql and create_sample_db.py together in the same folder.
5. For the included educational fixture, run python create_sample_db.py --output sample.db. It refuses to overwrite an existing file.
6. 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-orphan-order-report.py.

Run the script

python "sqlite-orphan-order-report.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

The complete script

#!/usr/bin/env python3
"""Use a LEFT JOIN to report sample-schema orders whose customer record is absent."""
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='Use a LEFT JOIN to report sample-schema orders whose customer record is absent.')
    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 o.id,o.customer_id,o.ordered_at,o.total FROM orders o LEFT JOIN customers c ON c.id=o.customer_id WHERE c.id IS NULL ORDER BY o.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

1. Use a LEFT JOIN to report sample-schema orders whose customer record is absent.
2. Arguments are explicit and expected input errors produce a message with a nonzero exit code.
3. 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

Adapt join keys for your schema. Unlike foreign_key_check, this query can test relationships even without a declared constraint.

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

Reference & next steps

• Python sqlite3 documentation ↗
• SQLite documentation ↗

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.