4 ms·
CLI tool and Python library for manipulating SQLite databases
- simonw 2y agoI'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/ 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#transforming-tables https://sqlite-utils.datasette.io/en/stable/cli.html#transfo... https://sqlite-utils.datasette.io/en/stable/python-api.html#python-api-transform https://sqlite-utils.datasette.io/en/stable/python-api.html#... A complex example from the docs: 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 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.