search

simonw / sqlite-utils

Python CLI utility and library for manipulating SQLite databases
https://sqlite-utils.datasette.io
Apache License 2.0
1.62k stars 109 forks source link

Plugin hook for adding new output formats #578

Open simonw opened 1 year ago

simonw commented 1 year ago

What would it take to add a format hook? I'm still thinking about my GIS workflow, and being able to do sqlite-utils query ... --geojson would be nice. It's the one place my Datasette workflow is messy, having to do datasette . --get /path/to/query.geojson --setting max_rows_returned 10000 --load-extension spatialite. I know the current pattern is --csv, but maybe --format geojson is more future-proof.

https://discord.com/channels/823971286308356157/997738192360964156/1133076679011602432

simonw commented 1 year ago

The main blocker here is coming up with a design. The challenge is cleanly integrating it with the existing format options:

https://github.com/simonw/sqlite-utils/blob/8bee14588687b66c54c7a3dfae5de2f9cc7cac3f/docs/cli-reference.rst#L114-L132

I'd like to avoid breaking backwards compatibility.

The most obvious solution is to add a --format x option. This is slightly confusing as --fmt already exists.

Or... --fmt could be enhanced to handle plugins too - and the existing set of formats (from the tabulate integration) could be refactored to use the new hook.

If I ever do release sqlite-utils 4 I'm tempted to move that tabulate stuff into a plugin.

simonw commented 1 year ago

I'm currently leaning very slightly towards --format geojson - and explaining in the docs that --format is for formats added by plugins.

simonw commented 1 year ago

A related feature would be support for plugins to add new ways of ingesting data - currently sqlite-utils insert works against JSON, newline-JSON, CSV and TSV.

eyeseast commented 1 year ago

A related feature would be support for plugins to add new ways of ingesting data - currently sqlite-utils insert works against JSON, newline-JSON, CSV and TSV.

This is my goal, to have one plugin that handles input and output symmetrically. I'd like to be able to do something like this:

sqlite-utils insert data.db table file.geojson --format geojson
# ... explore and manipulate in Datasette
sqlite-utils query data.db ... --format geojson > output.geojson

This would work especially well with datasette-query-files, since I already have the queries I need saved in standalone SQL files.

eyeseast commented 1 year ago

I wonder if this should be two hooks: input and output. The current --csv (and --tsv) options apply to both. Haven't looked at how it's implemented. Or maybe it's one hook that returns a format for reading and for writing.