This release introduces two key new capabilities: transform (#114) and extract (#42).
Transform
SQLite's ALTER TABLE has several documented limitations. The table.transform() Python method and sqlite-utils transform CLI command work around these limitations using a pattern where a new table with the desired structure is created, data is copied over to it and the old table is then dropped and replaced by the new one.
You can use these tools to drop columns, change column types, rename columns, add and remove NOT NULL and defaults, remove foreign key constraints and more. See the transforming tables (CLI) and transforming tables (Python library) documentation for full details of how to use them.
Extract
Sometimes a database table - especially one imported from a CSV file - will contain duplicate data. A Trees table may include a Species column with only a few dozen unique values, when the table itself contains thousands of rows.
The table.extract() method and sqlite-utils extract commands can extract a column - or multiple columns - out into a separate lookup table, and set up a foreign key relationship from the original table.
The Python library extract() documentation describes how extraction works in detail, and Extracting columns into a separate table in the CLI documentation includes a detailed example.
Other changes
- The
@db.register_functiondecorator can be used to quickly register Python functions as custom SQL functions, see Registering custom SQL functions. (#162) - The
table.rows_where()method now accepts an optionalselect=argument for specifying which columns should be selected, see Listing rows.