Repository: simonw/sqlite-utils

Issue:
"Too many SQL variables" on large inserts
Reported here: https://github.com/dogsheep/healthkit-to-sqlite/issues/9

It looks like there's a default limit of 999 variables - we need to be smart about that, maybe dynamically lower the batch size based on the number of columns.

Discussion:

Patch:
diff --git a/sqlite_utils/db.py b/sqlite_utils/db.py
index ef55976..586014b 100644
--- a/sqlite_utils/db.py
+++ b/sqlite_utils/db.py
@@ -6,6 +6,8 @@ import itertools
 import json
 import pathlib
 
+SQLITE_MAX_VARS = 999
+
 try:
     import numpy as np
 except ImportError:
@@ -848,7 +850,17 @@ class Table:
         ), "Use either ignore=True or upsert=True, not both"
         all_columns = None
         first = True
-        for chunk in chunks(records, batch_size):
+        # We can only handle a max of 999 variables in a SQL insert, so
+        # we need to adjust the batch_size down if we have too many cols
+        records = iter(records)
+        # Peek at first record to count its columns:
+        first_record = next(records)
+        num_columns = len(first_record.keys())
+        assert (
+            num_columns <= SQLITE_MAX_VARS
+        ), "Rows can have a maximum of {} columns".format(SQLITE_MAX_VARS)
+        batch_size = max(1, min(batch_size, SQLITE_MAX_VARS // num_columns))
+        for chunk in chunks(itertools.chain([first_record], records), batch_size):
             chunk = list(chunk)
             if first:
                 if not self.exists:


Test patch:
diff --git a/tests/test_create.py b/tests/test_create.py
index 222a967..71b1583 100644
--- a/tests/test_create.py
+++ b/tests/test_create.py
@@ -500,6 +500,42 @@ def test_upsert_rows_alter_table(fresh_db, use_table_factory):
     ] == list(table.rows)
 
 
+def test_bulk_insert_more_than_999_values(fresh_db):
+    "Inserting 100 items with 11 columns should work"
+    fresh_db["big"].insert_all(
+        (
+            {
+                "id": i + 1,
+                "c2": 2,
+                "c3": 3,
+                "c4": 4,
+                "c5": 5,
+                "c6": 6,
+                "c7": 7,
+                "c8": 8,
+                "c8": 9,
+                "c10": 10,
+                "c11": 11,
+            }
+            for i in range(100)
+        ),
+        pk="id",
+    )
+    assert 100 == fresh_db["big"].count
+
+
+@pytest.mark.parametrize(
+    "num_columns,should_error", ((900, False), (999, False), (1000, True))
+)
+def test_error_if_more_than_999_columns(fresh_db, num_columns, should_error):
+    record = dict([("c{}".format(i), i) for i in range(num_columns)])
+    if should_error:
+        with pytest.raises(AssertionError):
+            fresh_db["big"].insert(record)
+    else:
+        fresh_db["big"].insert(record)
+
+
 @pytest.mark.parametrize(
     "columns,index_name,expected_index",
     (