sqlite-utils transform should set empty strings to null when converting text columns to integer/float

[simonw]

/tmp % echo "id,age,weight\n1,3,2.5\n2,," | sqlite-utils insert test.db test - --csv
/tmp % sqlite-utils schema test.db                                                  
CREATE TABLE [test] (
   [id] TEXT,
   [age] TEXT,
   [weight] TEXT
);
/tmp % sqlite-utils transform test.db test --type age integer --type weight float   
/tmp % sqlite-utils schema test.db                                               
CREATE TABLE "test" (
   [id] TEXT,
   [age] INTEGER,
   [weight] FLOAT
);
/tmp % sqlite-utils rows test.db test
[{"id": "1", "age": 3, "weight": 2.5},
 {"id": "2", "age": "", "weight": ""}]

It would be neat if this resulted in the following instead:

 {"id": "2", "age": null, "weight": null}

Related Discord discussion: https://discord.com/channels/823971286308356157/823971286941302908/1019635490833567794

[simonw]

Frustratingly I think this counts as a backwards-incompatible change.

Could have it be opt-in with a new option / method parameter, and then change it to the default if I release a sqlite-utils 4.

[simonw]

No, I'm going to say that this is a bug - it's WEIRD having a integer or float column containing an empty string. I'm OK changing that - I very much doubt anyone is relying on this functionality.

So no need for a new option here - just fixing the bug is sensible.

[simonw]

New tests should go in: https://github.com/simonw/sqlite-utils/blob/main/tests/test_transform.py

I think the implementation fix needs to go near here:

sqlite-utils/sqlite_utils/db.py

Lines 1770 to 1775 in 0b315d3

	copy_sql = "INSERT INTO [{new_table}] ({new_cols})\n SELECT {old_cols} FROM [{old_table}];".format(
	new_table=new_table_name,
	old_table=self.name,
	old_cols=", ".join("[{}]".format(col) for col in old_cols),
	new_cols=", ".join("[{}]".format(col) for col in new_cols),
	)

The trick is going to be teaching that generated SQL to know which columns are integer or float and to convert "" to null as part of that operation.

[simonw]

It looks like SQLite has a SELECT NULLIF(value, '') function which returns null if that value is equal to ''.

We need to only apply that function to columns that we know to be of type integer or float though - text columns containing empty strings should not be rewritten to null.

[ar-jan]

text columns containing empty strings should not be rewritten to null.

I would actually appreciate an option to do just that for text columns as well.