SQLite to JSON Converter - A Python utility that automatically converts all tables from a SQLite database to JSON format.

Readme.md

SQLite to JSON Converter

A Python utility that automatically converts all tables from a SQLite database to JSON format.

Description

This tool provides a simple command-line interface to export all tables from a SQLite database file (.db) to JSON format. It can generate individual JSON files for each table or combine all tables into a single JSON file.

Features

• Export all tables automatically
• Option for pretty (indented) JSON output
• Save each table as a separate JSON file or combine all into one file
• Preserves column names as JSON property names
• Handles any SQLite database structure

Requirements

• Python 3.6+
• No external dependencies (uses only standard library modules)

Installation

Clone or download this repository, or just save the script as sqlite_to_json.py.

# Make the script executable (for Linux/Mac)
chmod +x sqlite_to_json.py

Usage

Basic usage:

python sqlite_to_json.py database.db

With options:

python sqlite_to_json.py database.db --output-dir ./exported_data --pretty --single-file

Command-line Arguments

Argument	Short	Description
db_path		Path to the SQLite database file (required)
--output-dir	-o	Directory to save JSON files (default: ./json_output)
--pretty	-p	Generate pretty (indented) JSON
--single-file	-s	Save all tables to a single JSON file

How It Works

The script:

1. Connects to the specified SQLite database
2. Retrieves a list of all tables
3. For each table:
   • Fetches all data and column names
   • Converts rows to JSON-compatible dictionaries
   • Preserves column name -> value relationships
4. Saves the data to the specified output directory
   • Either as separate files named after each table
   • Or as a single file with all tables

Example Output

When using separate files, each table will be saved as [table_name].json:

json_output/
├── users.json
├── products.json
└── orders.json

Sample content of a JSON file:

[
  {
    "id": 1,
    "name": "John Doe",
    "email": "[email protected]"
  },
  {
    "id": 2,
    "name": "Jane Smith",
    "email": "[email protected]"
  }
]

When using the --single-file option, all tables will be in all_tables.json:

{
  "users": [
    {
      "id": 1,
      "name": "John Doe",
      "email": "[email protected]"
    }
  ],
  "products": [
    {
      "id": 101,
      "name": "Laptop",
      "price": 999.99
    }
  ]
}

Use Cases

• Data migration between systems
• Creating backups in a human-readable format
• Exporting data for web applications
• Analyzing database structure and content
• Sharing data with systems that consume JSON

Notes

• Large databases may require significant memory during processing
• Binary data in the SQLite database may not convert properly to JSON
• Table names with special characters might cause issues

sqlite_to_json.py

#!/usr/bin/env python3
import argparse
import json
import os
import sqlite3
from pathlib import Path


def get_all_tables(conn):
    """Get all table names from the SQLite database."""
    cursor = conn.cursor()
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
    tables = cursor.fetchall()
    return [table[0] for table in tables]


def table_to_json(conn, table_name):
    """Convert a single table to a list of dictionaries (for JSON serialization)."""
    cursor = conn.cursor()
    cursor.execute(f"SELECT * FROM {table_name}")
    
    # Get column names
    columns = [description[0] for description in cursor.description]
    
    # Fetch all rows and convert to dictionaries
    results = []
    for row in cursor.fetchall():
        results.append(dict(zip(columns, row)))
    
    return results


def save_json_file(data, output_dir, table_name, pretty=False):
    """Save data as JSON to a file."""
    output_path = Path(output_dir) / f"{table_name}.json"
    
    with open(output_path, 'w', encoding='utf-8') as f:
        if pretty:
            json.dump(data, f, indent=2, ensure_ascii=False)
        else:
            json.dump(data, f, ensure_ascii=False)
    
    return output_path


def sqlite_to_json(db_path, output_dir, pretty=False, single_file=False):
    """Convert all tables in a SQLite database to JSON."""
    # Ensure output directory exists
    os.makedirs(output_dir, exist_ok=True)
    
    # Connect to the database
    conn = sqlite3.connect(db_path)
    
    # Get all tables
    tables = get_all_tables(conn)
    
    if not tables:
        print("No tables found in the database.")
        conn.close()
        return
    
    results = {}
    files_created = []
    
    # Process each table
    for table_name in tables:
        print(f"Processing table: {table_name}")
        data = table_to_json(conn, table_name)
        
        # Store data for single file mode or save individual files
        if single_file:
            results[table_name] = data
        else:
            output_path = save_json_file(data, output_dir, table_name, pretty)
            files_created.append(output_path)
    
    # Save all tables to a single file if requested
    if single_file:
        output_path = Path(output_dir) / "all_tables.json"
        with open(output_path, 'w', encoding='utf-8') as f:
            if pretty:
                json.dump(results, f, indent=2, ensure_ascii=False)
            else:
                json.dump(results, f, ensure_ascii=False)
        files_created.append(output_path)
    
    conn.close()
    return files_created


def main():
    parser = argparse.ArgumentParser(description="Convert SQLite database to JSON")
    parser.add_argument("db_path", help="Path to the SQLite database file")
    parser.add_argument(
        "--output-dir", "-o", default="./json_output", 
        help="Directory to save JSON files (default: ./json_output)"
    )
    parser.add_argument(
        "--pretty", "-p", action="store_true", 
        help="Generate pretty (indented) JSON"
    )
    parser.add_argument(
        "--single-file", "-s", action="store_true", 
        help="Save all tables to a single JSON file"
    )
    
    args = parser.parse_args()
    
    if not os.path.exists(args.db_path):
        print(f"Error: Database file '{args.db_path}' does not exist.")
        return
    
    files = sqlite_to_json(
        args.db_path, 
        args.output_dir, 
        pretty=args.pretty,
        single_file=args.single_file
    )
    
    if files:
        print(f"\nConversion completed successfully!")
        print(f"JSON files saved to: {os.path.abspath(args.output_dir)}")
        print(f"Files created: {len(files)}")


if __name__ == "__main__":
    main()