Targeted queries over compressed SQLite

Upnaam can query a gzip-compressed SQLite database without writing the full decompressed database to disk. This matters for the Bihar ration source: its two archive parts occupy about 3.30 GB, while the logical SQLite database is 56,741,351,424 bytes.

The generic adapter treats ordered archive parts as one compressed byte stream, builds an indexed-gzip seek index, and exposes decompressed byte ranges to SQLite through a read-only APSW virtual file system. The index is built once and reused. For the Bihar source it is 417,660,907 bytes, or about 398 MiB.

Install the optional dependencies:

pip install "upnaam[compressed-sqlite]"

Then issue ordinary targeted SQL:

from pathlib import Path

from upnaam.compressed_sqlite import open_gzip_sqlite

parts = [
    Path("ration_cards.sqlite.gz.001"),
    Path("ration_cards.sqlite.gz.002"),
]
index = Path("ration_cards.sqlite.gzidx")

with open_gzip_sqlite(parts, index) as connection:
    rows = connection.execute(
        "SELECT id, members_qty, sub_table FROM family_members_tables WHERE id = ?",
        ("00000001",),
    ).fetchall()

The connection is always read-only. Each index has a JSON sidecar recording the ordered source-part hashes, decompressed size, index hash, and implementation revision. Upnaam refuses to use a stale, partial, or unverified index. Pass rebuild_index=True only when deliberately replacing the index after a source change.

This mechanism makes indexed point lookups practical. The Bihar database has primary-key indexes on family_members_tables.id and ration_card_details.ration_card_number; queries on those keys are targeted lookups. It does not make an unindexed predicate cheap: a filter such as ration_card_details.village_id = ? still scans that table. Nor does the adapter alter the data or turn the ration transcription into a surname label. Source-specific name selection remains a separate pipeline stage.