SQLite to Structured JSON

Parse SQLite databases by exploring schemas first, then extracting data into structured JSON.

Step 1: Explore the Schema

Always start by understanding what tables exist and their structure.

List All Tables

SELECT name FROM sqlite_master WHERE type='table';

Inspect Table Schema

-- Get column names and types
PRAGMA table_info(TableName);

-- See CREATE statement
SELECT sql FROM sqlite_master WHERE name='TableName';

Find Primary/Unique Keys

-- Primary key info
PRAGMA table_info(TableName);  -- 'pk' column shows primary key order

-- All indexes (includes unique constraints)
PRAGMA index_list(TableName);

-- Columns in an index
PRAGMA index_info(index_name);

Step 2: Understand Relationships

Identify Foreign Keys

PRAGMA foreign_key_list(TableName);

Common Patterns

ID-based joins: Tables often share an ID column

-- Main table has ID as primary key
-- Related tables reference it
SELECT m.*, r.ExtraData
FROM MainTable m
LEFT JOIN RelatedTable r ON m.ID = r.ID;

Coordinate-based keys: Spatial data often uses computed coordinates

# If ID represents a linear index into a grid:
x = id % width
y = id // width

Step 3: Extract and Transform

Basic Pattern

import sqlite3
import json

def parse_sqlite_to_json(db_path):
    conn = sqlite3.connect(db_path)
    conn.row_factory = sqlite3.Row  # Access columns by name
    cursor = conn.cursor()

    # 1. Explore schema
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
    tables = [row[0] for row in cursor.fetchall()]

    # 2. Get dimensions/metadata from config table
    cursor.execute("SELECT * FROM MetadataTable LIMIT 1")
    metadata = dict(cursor.fetchone())

    # 3. Build indexed data structure
    data = {}
    cursor.execute("SELECT * FROM MainTable")
    for row in cursor.fetchall():
        key = row["ID"]  # or compute: (row["X"], row["Y"])
        data[key] = dict(row)

    # 4. Join related data
    cursor.execute("SELECT * FROM RelatedTable")
    for row in cursor.fetchall():
        key = row["ID"]
        if key in data:
            data[key]["extra_field"] = row["Value"]

    conn.close()
    return {"metadata": metadata, "items": list(data.values())}