sqlite-utils bulk --batch-size option

[simonw]

Could add support for --batch-size as seen in insert/upsert too - causing it to break the list up into batches and commit for each one.

Originally posted by @simonw in #391 (comment)

[simonw]

Relevant code:

sqlite-utils/sqlite_utils/cli.py

Lines 1014 to 1018 in a9fca7e

	# For bulk_sql= we use cursor.executemany() instead
	if bulk_sql:
	with db.conn:
	db.conn.cursor().executemany(bulk_sql, docs)
	return

[simonw]

Help for insert says:

  --batch-size INTEGER      Commit every X records

[simonw]

Can use this utility function:

sqlite-utils/sqlite_utils/utils.py

Lines 322 to 325 in a9fca7e

	def chunks(sequence, size):
	iterator = iter(sequence)
	for item in iterator:
	yield itertools.chain([item], itertools.islice(iterator, size - 1))

[simonw]

Manually tested it like this:

# Create database with an empty "lines" table
sqlite-utils create-table bulk-test.db lines line text
# Stream records every 0.5s, commit every 5 records
stream-delay docs/python-api.rst -d 500 | \
  sqlite-utils bulk bulk-test.db 'insert into lines (line) values (:line)' - \
  --lines --batch-size 5

Running datasette bulk-test.db showed that records would show up about every 2.5s five at a time.