`sqlite-utils bulk` command

[simonw]

The .executemany() method is a very efficient way to execute the same SQL query against a huge list of parameters.

sqlite-utils insert supports a bunch of ways of loading a list of dictionaries - from CSV, TSV, JSON, newline JSON and more thanks to:

• --lines and --text and --convert and --import #361

What if you could load a list of dictionaries and provide a SQL query with :named parameters that correspond to keys in those dictionaries instead?

This would need to be a new command - I thought about adding a --sql option to insert but that doesn't make sense as that command already requires a table name.

[simonw]

cat blah.csv | sqlite-utils bulk blah.db - \
   "insert into blah (:foo, :bar)" --csv

[simonw]

I tested the prototype like this:

sqlite-utils blah.db 'create table blah (id integer primary key, name text)' 
echo 'id,name
1,Cleo
2,Chicken' > blah.csv
sqlite-utils bulk blah.db 'insert into blah (id, name) values (:id, :name)' blah.csv --csv

[simonw]

Documentation: https://sqlite-utils.datasette.io/en/latest/cli.html#executing-sql-in-bulk