sqlite-utils insert: options for column types

[simonw]

The insert command currently results in string types for every column - at least when used against CSV or TSV inputs.

It would be useful if you could do the following:

• automatically detects the column types based on eg the first 1000 records
• explicitly state the rule for specific columns

--detect-types could work for the former - or it could do that by default and allow opt-out using --no-detect-types

For specific columns maybe this:

sqlite-utils insert db.db images images.tsv \
  --tsv \
  -c id int \
  -c score float

[simonw]

Should be consistent with the create-table command as much as possible:

$ sqlite-utils create-table mydb.db mytable \
    id integer \
    name text \
    age integer \
    is_good integer \
    --not-null name \
    --not-null age \
    --default is_good 1 \
    --pk=id

[simonw]

Annoyingly -c is currently a shortcut for --csv - so I'd have to do a major version bump to use that.

sqlite-utils/sqlite_utils/cli.py

Lines 601 to 603 in 726219c

	click.option("--nl", is_flag=True, help="Expect newline-delimited JSON"),
	click.option("-c", "--csv", is_flag=True, help="Expect CSV"),
	click.option("--tsv", is_flag=True, help="Expect TSV"),

Particularly annoying because I attempted to remove the -c shortcut in 2c00567#diff-76294b3d4afeb27e74e738daa01c26dd4dc9ccb6f4477451483a2ece1095902eL48 but forgot to remove it from the input options (I removed it from the output options).

[simonw]

If -c isn't available, maybe -t or --type would work for specifying column types:

sqlite-utils insert db.db images images.tsv \
  --tsv \
  --type id int \
  --type score float

or

sqlite-utils insert db.db images images.tsv \
  --tsv \
  -t id int \
  -t score float

[simonw]

I like -t or --type for this.

[eyeseast]

Just ran into this issue last night. I have a big table that's mostly numbers, but also a zip code column in a state where ZIP codes start with 0. Would be great to run something like this:

sqlite-utils insert data.db places file.csv --csv --detect-types --type zipcode text

Maybe I'll take a crack at this one.

[simonw]

I think --type is right without the -t shortcut, for consistency with sqlite-utils transform:

sqlite-utils/docs/cli-reference.rst

Lines 476 to 488 in 092f091

	Usage: sqlite-utils transform [OPTIONS] PATH TABLE
	Transform a table beyond the capabilities of ALTER TABLE
	Example:
	sqlite-utils transform mydb.db mytable \
	--drop column1 \
	--rename column2 column_renamed
	Options:
	--type <TEXT CHOICE>... Change column type to INTEGER, TEXT, FLOAT,
	REAL or BLOB