sqlite-utils insert --extract colname

[simonw]

Is there a reason I've not added --extract as an option for sqlite-utils insert next? There's a extracts= option for the various table.insert() etc methods - last line in this code block:

sqlite-utils/sqlite_utils/db.py

Lines 2483 to 2495 in 213a0ff

	def insert(
	self,
	record: Dict[str, Any],
	pk=DEFAULT,
	foreign_keys=DEFAULT,
	column_order: Optional[Union[List[str], Default]] = DEFAULT,
	not_null: Optional[Union[Set[str], Default]] = DEFAULT,
	defaults: Optional[Union[Dict[str, Any], Default]] = DEFAULT,
	hash_id: Optional[Union[str, Default]] = DEFAULT,
	alter: Optional[Union[bool, Default]] = DEFAULT,
	ignore: Optional[Union[bool, Default]] = DEFAULT,
	replace: Optional[Union[bool, Default]] = DEFAULT,
	extracts: Optional[Union[Dict[str, str], List[str], Default]] = DEFAULT,

[simonw]

Thought about this due to this issue, which should stay consistent with how the sqlite-utils CLI works:

• Ability to extract columns from item and item_version git-history#41

[simonw]

Rough prototype, seems to work:;

diff --git a/sqlite_utils/cli.py b/sqlite_utils/cli.py
index 771d432..431b93e 100644
--- a/sqlite_utils/cli.py
+++ b/sqlite_utils/cli.py
@@ -867,6 +867,12 @@ def insert_upsert_options(*, require_pk=False):
                 ),
                 load_extension_option,
                 click.option("--silent", is_flag=True, help="Do not show progress bar"),
+                click.option(
+                    "--extract",
+                    "extracts",
+                    multiple=True,
+                    help="Columns to extract to another table",
+                ),
             )
         ):
             fn = decorator(fn)
@@ -906,6 +912,7 @@ def insert_upsert_implementation(
     load_extension=None,
     silent=False,
     bulk_sql=None,
+    extracts=None,
 ):
     db = sqlite_utils.Database(path)
     _load_extensions(db, load_extension)
@@ -1008,6 +1015,8 @@ def insert_upsert_implementation(
         extra_kwargs["defaults"] = dict(default)
     if upsert:
         extra_kwargs["upsert"] = upsert
+    if extracts is not None:
+        extra_kwargs["extracts"] = extracts
 
     # Apply {"$base64": true, ...} decoding, if needed
     docs = (decode_base64_values(doc) for doc in docs)
@@ -1117,6 +1126,7 @@ def insert(
     truncate,
     not_null,
     default,
+    extracts,
 ):
     """
     Insert records from FILE into a table, creating the table if it
@@ -1174,6 +1184,7 @@ def insert(
             silent=silent,
             not_null=not_null,
             default=default,
+            extracts=extracts,
         )
     except UnicodeDecodeError as ex:
         raise click.ClickException(UNICODE_ERROR.format(ex))
@@ -1207,6 +1218,7 @@ def upsert(
     analyze,
     load_extension,
     silent,
+    extracts,
 ):
     """
     Upsert records based on their primary key. Works like 'insert' but if

[simonw]

Manual test run with that prototype:

% echo '{"foo": "bar", "id": 1}' | sqlite-utils insert insert-extract.db rows - --pk id --extract foo
% sqlite-utils dump insert-extract.db                                                                
BEGIN TRANSACTION;
CREATE TABLE [foo] (
   [id] INTEGER PRIMARY KEY,
   [value] TEXT
);
INSERT INTO "foo" VALUES(1,'bar');
CREATE TABLE [rows] (
   [foo] INTEGER REFERENCES [foo]([id]),
   [id] INTEGER PRIMARY KEY
);
INSERT INTO "rows" VALUES(1,1);
CREATE UNIQUE INDEX [idx_foo_value]
    ON [foo] ([value]);
COMMIT;

[simonw]

Ran into this bug again while writing tests for this:

• .extract() shouldn't extract null values #186