Research Goal: Enhance sqlite-utils insert_all and upsert_all methods to support Python iterators yielding lists instead of only dicts, and measure the performance impact.

Successfully implemented list-based iteration support for sqlite-utils, enabling a more memory-efficient alternative to dict-based iteration for bulk data operations. The feature automatically detects whether the iterator yields lists or dicts, maintaining full backward compatibility.

Key Results:

• ✅ Implementation complete with 100% backward compatibility
• ✅ All 1001 existing tests pass
• ✅ 10 new tests added for list mode functionality
• ⚡ Performance improvements vary by column count (up to 21.6% faster for wide datasets)
• 📉 Memory efficiency gains from avoiding dict object creation

The enhanced methods now support two modes:

1. Dict Mode (Original Behavior)

db["people"].insert_all([
    {"id": 1, "name": "Alice", "age": 30},
    {"id": 2, "name": "Bob", "age": 25},
])

2. List Mode (New Feature)

def data_generator():
    # First yield: column names
    yield ["id", "name", "age"]
    # Subsequent yields: data rows
    yield [1, "Alice", 30]
    yield [2, "Bob", 25]

db["people"].insert_all(data_generator())

The implementation automatically detects the mode by inspecting the first yielded value:

• If it's a dict: proceeds with original dict-based logic
• If it's a list: validates it contains column names (strings), then treats subsequent lists as data rows
• Raises ValueError if the first list contains non-string values or if modes are mixed

All changes were made to /tmp/sqlite-utils/sqlite_utils/db.py:

1. insert_all method: Added list mode detection and column name extraction
2. insert_chunk method: Added list_mode parameter
3. build_insert_queries_and_params method: Added separate logic paths for list vs dict mode

See sqlite-utils-list-mode.diff for the complete 222-line diff.

Comprehensive benchmarks were executed across multiple scenarios:

• Various row counts: 10K, 20K, 50K, 100K
• Various column counts: 5, 8, 10, 15, 20
• Both INSERT and UPSERT operations
• Different batch sizes

All benchmarks used:

• Temporary SQLite databases
• String data for consistent comparison
• Python 3.11.14
• sqlite-utils modified version vs baseline

1. Column Count Matters: List mode excels with fewer columns (5-10), where dict overhead is significant
2. Crossover Point: Around 15+ columns, Python's dict optimizations make dict mode competitive or faster
3. Memory Efficiency: List mode avoids creating intermediate dict objects, reducing memory pressure
4. Large Datasets: Best improvements seen with 100K+ rows and 5-10 columns (typical for time series data)

Direct time comparison across scenarios

Speedup factors showing where list mode excels

Rows per second processed in each mode

Performance vs number of columns - showing the crossover effect

Created 10 comprehensive tests in test_list_mode.py:

1. ✅ test_insert_all_list_mode_basic - Basic list mode insertion
2. ✅ test_insert_all_list_mode_with_pk - Primary key support
3. ✅ test_upsert_all_list_mode - Upsert operations
4. ✅ test_list_mode_with_various_types - Multiple data types
5. ✅ test_list_mode_error_non_string_columns - Error handling for invalid column names
6. ✅ test_list_mode_error_mixed_types - Error handling for mixed list/dict
7. ✅ test_list_mode_empty_after_headers - Edge case: headers only
8. ✅ test_list_mode_batch_processing - Large dataset batching
9. ✅ test_list_mode_shorter_rows - Rows with missing values
10. ✅ test_backwards_compatibility_dict_mode - Backward compatibility

All tests pass: 10/10 new tests ✅, 1001/1001 existing tests ✅

Ideal scenarios:

• 📊 Time series data with few columns (timestamp, value, sensor_id)
• 📁 Processing CSV/TSV files (already in row format)
• 🔢 Numerical data streams with fixed schema
• 💾 Memory-constrained environments
• 🎯 Data pipelines where schema is known upfront

Example - Processing CSV-like data:

def csv_generator():
    yield ["timestamp", "temperature", "humidity", "sensor_id"]
    for line in sensor_data_stream:
        yield line.split(',')

db["sensor_readings"].insert_all(csv_generator())

Better for:

• 🔄 Data with varying schemas (different columns per row)
• 📚 Wide tables with many columns (15+)
• 🎨 When code readability/self-documentation is priority
• 🔍 When you're dynamically determining columns

Based on the research findings:

1. For CSV/data file imports: Use list mode with 5-10 column datasets for ~5-20% performance gain
2. For wide tables (15+ columns): Stick with dict mode for better performance
3. For mixed workloads: The automatic detection means no need to choose - use whichever is more natural
4. For memory-constrained scenarios: List mode provides better memory efficiency regardless of performance

• ✅ Zero breaking changes (100% backward compatible)
• ✅ Clear error messages for invalid usage
• ✅ Follows existing code patterns and style
• ✅ Comprehensive inline comments
• ✅ Type consistency maintained

• Empty iterators
• Headers without data
• Rows shorter than column list (NULL padding)
• Very large batches requiring split
• Mixed type detection and validation

sqlite-utils-iterator-support/
├── README.md                          # This file
├── notes.md                           # Development notes
├── sqlite-utils-list-mode.diff        # Git diff of changes (222 lines)
├── test_list_mode.py                  # Test suite (10 tests)
├── benchmark.py                       # Benchmark suite
├── benchmark_results.json             # Raw benchmark data
├── generate_charts.py                 # Chart generation script
├── chart_comparison.png               # Performance comparison chart
├── chart_speedup.png                  # Speedup analysis chart
├── chart_throughput.png               # Throughput comparison chart
└── chart_columns.png                  # Column count analysis chart

The list-based iterator support successfully enhances sqlite-utils with a more efficient data ingestion method for common use cases. While not universally faster (performance depends on column count), it provides:

1. Meaningful performance improvements for typical datasets (5-10 columns)
2. Memory efficiency gains by avoiding dict object creation
3. Better ergonomics for CSV/row-based data processing
4. 100% backward compatibility with existing code
5. Automatic mode detection requiring no API changes

The feature is production-ready and would benefit users processing large datasets, especially in memory-constrained environments or when working with pre-structured data formats.

• Table.insert_all() - Enhanced with list mode detection
• Table.upsert_all() - Inherits list mode support through insert_all
• Table.insert_chunk() - Added list_mode parameter
• Table.build_insert_queries_and_params() - Dual-path implementation

No new dependencies added. Uses only:

• Python 3.11+ (existing requirement)
• SQLite 3 (existing requirement)
• Existing sqlite-utils dependencies

• Best case: 21.6% improvement (100K rows, 5 columns)
• Typical case: 5-10% improvement (moderate row/column counts)
• Worst case: 35.9% regression (many columns, dict mode preferred)
• Average: ~3% improvement across all scenarios

Research completed: November 22, 2025 SQLite-utils version: 4.0a0 (main branch) Python version: 3.11.14