I've been working on this for almost six years now. The initial idea was to solve the "get stuff into a SQLite database" problem as effectively as possible, because my https://datasette.io/ project was only useful if you first get your data into SQLite.
It's since grown to handle all manner of manipulations. Possibly the most useful is its support for advanced schema alterations via the "transform" CLI command (and accompanying table.transform() Python method):
sqlite-utils transform fixtures.db roadside_attractions \
--rename pk id \
--default name Untitled \
--column-order id \
--column-order longitude \
--column-order latitude \
--drop address
This addresses one of the most frequent complaints people have about SQLite - that it doesn't support a range of table alter operations beyond simple things like adding a new column.
sqlite-utils transform is an implementation of the pattern described in the SQLite docs - https://www.sqlite.org/lang_altertable.html#otheralter - where you create a new empty table with the modified schema, then copy the old data across and rename the tables as part of a single transaction.
I've been working on this for almost six years now. The initial idea was to solve the "get stuff into a SQLite database" problem as effectively as possible, because my https://datasette.io/ project was only useful if you first get your data into SQLite.
It's since grown to handle all manner of manipulations. Possibly the most useful is its support for advanced schema alterations via the "transform" CLI command (and accompanying table.transform() Python method):
https://sqlite-utils.datasette.io/en/stable/cli.html#transfo...
https://sqlite-utils.datasette.io/en/stable/python-api.html#...
A complex example from the docs:
This addresses one of the most frequent complaints people have about SQLite - that it doesn't support a range of table alter operations beyond simple things like adding a new column.sqlite-utils transform is an implementation of the pattern described in the SQLite docs - https://www.sqlite.org/lang_altertable.html#otheralter - where you create a new empty table with the modified schema, then copy the old data across and rename the tables as part of a single transaction.