Allow passing a file of code to "sqlite-utils convert"

[fgregg]

sqlite-utils is so nice, but the ergonomics of the multiline code in kind of tough. It's really hard (maybe impossible) to make the newlines play well with Makefiles.

it would be great to write your code fragment in a separate file and direct it into the sqlite-utils

either like

sqlite-utils convert my.db my_table my_column < custom_code.py

or

sqlite-utils convert my.db my_table my_column --custom-code=custom_code.py

Thanks, as ever, for these great tools!

[simonw]

There's a very non-obvious workaround for this at the moment. You can save your code in e.g. a file called transform.py - my test one looks like this:

def upper(value):
   return value.upper()

Then you can run the following to import and use that function:

PYTHONPATH=. sqlite-utils convert fixtures.db roadside_attractions name 'transform.upper(value)' --import transform

That PYTHONPATH=. bit is necessary because otherwise the script won't look in the current directory for that transform.py` module.

Now that I've written this down, it's obviously bad! I think your suggestion here is a good idea.

[simonw]

One challenge here: the current signature looks like this:

% sqlite-utils convert --help
Usage: sqlite-utils convert [OPTIONS] DB_PATH TABLE COLUMNS... CODE

CODE is a positional argument which comes last - and since COLUMNS can be one or more items, making CODE optional isn't easy.

[simonw]

One option: allow CODE to be a special value of - which means "read from standard input". It's a tiny bit of a hack but I think it would work here.

If you wanted to replace a column entirely with hyphens you would still be able to do this:

sqlite-utils convert my.db mytable col1 '"-"'

[simonw]

My first attempt at building this looked a little bit strange, because you would end up having a file like this convert.py:

value = value.upper()
return value

Which gets used like this:

cat convert.py | sqlite-utils convert my.db mytable col1 -

But... that convert.py code isn't actually valid Python - it's a weird thing where you have a partial snippet of Python code that gets wrapped in a function automatically.

It would be better if you could write convert.py as a valid Python file with a function in it, something like this:

def convert(value):
    value = value.upper()
    return value

[simonw]

I think the fix for this is to change the rules about what code is accepted in both the - mode and the literal code string mode: you can pass in a Python expression, OR a fragment that gets turned into a function, OR code that implements its own def convert(value) function. So this would work too:

sqlite-utils convert my.db mytable col1 '
def convert(value):
    return value.upper()
'

[simonw]

OK, this is implemented. Updated documentation is here: https://sqlite-utils.datasette.io/en/latest/cli.html#converting-data-in-columns

[simonw]

This won't be in a release for a little while, but you can install it to try it out using:

pip install https://github.com/simonw/sqlite-utils/archive/ee13f98c2c.zip

[fgregg]

wow! that's awesome! thanks so much, @simonw!