insert_all() silently corrupts text values after the first batch (e.g. "007" -> 7) #885

insert_all() derives column types from the first batch of 100 rows. If a column only contains integers in that batch, later rows with text values that look numeric are silently converted by SQLite's INTEGER affinity, losing leading zeros:

import sqlite_utils

db = sqlite_utils.Database(memory=True)
db["t"].insert_all([{"a": 1}] * 200 + [{"a": "007"}])
print(list(db["t"].rows)[-1])  # {'a': 7}

The same happens with insert() on an existing table, or with a smaller batch_size. No error or warning is raised, so ZIP codes, phone numbers or IDs can be corrupted without the user noticing.

The docs say types are inferred from the first batch, but not that later values can be silently altered. Possible options: a warning when a later value's Python type conflicts with the column type, an opt-in strict mode, or a note in the docs pointing to columns= / STRICT tables.

sqlite-utils 4.2.1, Python 3.12