Parse SQLite databases into structured JSON data. Use when exploring unknown database schemas, understanding table relationships, and extracting map data as JSON.
Parse SQLite databases by exploring schemas first, then extracting data into structured JSON.
Always start by understanding what tables exist and their structure.
SELECT name FROM sqlite_master WHERE type='table';
-- Get column names and types
PRAGMA table_info(TableName);
-- See CREATE statement
SELECT sql FROM sqlite_master WHERE name='TableName';
-- 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);
PRAGMA foreign_key_list(TableName);
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
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())}
def safe_query(cursor, query):
try:
cursor.execute(query)
return cursor.fetchall()
except sqlite3.OperationalError:
return [] # Table doesn't exist
Use when items have natural unique keys:
{
"metadata": {"width": 44, "height": 26},
"tiles": {
"0,0": {"terrain": "GRASS", "feature": null},
"1,0": {"terrain": "PLAINS", "feature": "FOREST"},
"2,0": {"terrain": "COAST", "resource": "FISH"}
}
}
Use when order matters or keys are simple integers:
{
"metadata": {"width": 44, "height": 26},
"tiles": [
{"x": 0, "y": 0, "terrain": "GRASS"},
{"x": 1, "y": 0, "terrain": "PLAINS", "feature": "FOREST"},
{"x": 2, "y": 0, "terrain": "COAST", "resource": "FISH"}
]
}
x = id % width, y = id // width-- Sample data from any table
SELECT * FROM TableName LIMIT 5;
-- Count rows
SELECT COUNT(*) FROM TableName;
-- Find distinct values in a column
SELECT DISTINCT ColumnName FROM TableName;
-- Check for nulls
SELECT COUNT(*) FROM TableName WHERE ColumnName IS NULL;