3.1.1

• Fixed failing test caused by optimize sometimes creating larger database files. (#209)
• Documentation now lives on https://sqlite-utils.datasette.io/
• README now includes brew install sqlite-utils installation method.

3.1

• New command: sqlite-utils analyze-tables my.db outputs useful information about the table columns in the database, such as the number of distinct values and how many rows are null. See Analyzing tables for documentation. (#207)
• New table.analyze_column(column) Python method used by the analyze-tables command - see Analyzing a column.
• The table.update() method now correctly handles values that should be stored as JSON. Thanks, Andreas Madsack. (#204)

3.0

This release introduces a new sqlite-utils search command for searching tables, see Executing searches. (#192)

The table.search() method has been redesigned, see Searching with table.search(). (#197)

The release includes minor backwards-incompatible changes, hence the version bump to 3.0. Those changes, which should not affect most users, are:

• The -c shortcut option for outputting CSV is no longer available. The full --csv option is required instead.
• The -f shortcut for --fmt has also been removed - use --fmt.
• The table.search() method now defaults to sorting by relevance, not sorting by rowid. (#198)
• The table.search() method now returns a generator over a list of Python dictionaries. It previously returned a list of tuples.

Also in this release:

• The query, tables, rows and search CLI commands now accept a new --tsv option which outputs the results in TSV. (#193)
• A new table.virtual_table_using property reveals if a table is a virtual table, and returns the upper case type of virtual table (e.g. FTS4 or FTS5) if it is. It returns None if the table is not a virtual table. (#196)
• The new table.search_sql() method returns the SQL for searching a table, see Building SQL queries with table.search_sql().
• sqlite-utils rows now accepts multiple optional -c parameters specifying the columns to return. (#200)

Changes since the 3.0a0 alpha release:

• The sqlite-utils search command now defaults to returning every result, unless you add a --limit 20 option.
• The sqlite-utils search -c and table.search(columns=[]) options are now fully respected. (#201)

3.0a0

This release introduces a new sqlite-utils search command for searching tables, see Executing searches. (#192)

The table.search() method has been redesigned, see Searching with table.search(). (#197)

The release includes minor backwards-incompatible changes, hence the version bump to 3.0. Those changes, which should not affect most users, are:

• The -c shortcut option for outputting CSV is no longer available. The full --csv option is required instead.
• The -f shortcut for --fmt has also been removed - use --fmt.
• The table.search() method now defaults to sorting by relevance, not sorting by rowid. (#198)
• The table.search() method now returns a generator over a list of Python dictionaries. It previously returned a list of tuples.

Also in this release:

• The query, tables, rows and search CLI commands now accept a new --tsv option which outputs the results in TSV. (#193)
• A new table.virtual_table_using property reveals if a table is a virtual table, and returns the upper case type of virtual table (e.g. FTS4 or FTS5) if it is. It returns None if the table is not a virtual table. (#196)
• The new table.search_sql() method returns the SQL for searching a table, see Building SQL queries with table.search_sql().
• sqlite-utils rows now accepts multiple optional -c parameters specifying the columns to return. (#200)

2.23

• table.m2m(other_table, records) method now takes any iterable, not just a list or tuple. Thanks, Adam Wolf. (#189)
• sqlite-utils insert now displays a progress bar for CSV or TSV imports. (#173)
• New @db.register_function(deterministic=True) option for registering deterministic SQLite functions in Python 3.8 or higher. (#191)

2.22

• New --encoding option for processing CSV and TSV files that use a non-utf-8 encoding, for both the insert and update commands. (#182)
• The --load-extension option is now available to many more commands. (#137)
• --load-extension=spatialite can be used to load SpatiaLite from common installation locations, if it is available. (#136)
• Tests now also run against Python 3.9. (#184)
• Passing pk=["id"] now has the same effect as passing pk="id". (#181)

2.21

• table.extract() and sqlite-utils extract now apply much, much faster - one example operation reduced from twelve minutes to just four seconds! (#172)
• sqlite-utils extract no longer shows a progress bar, because it's fast enough not to need one.
• New column_order= option for table.transform() which can be used to alter the order of columns in a table. (#175)
• sqlite-utils transform --column-order= option (with a -o shortcut) for changing column order. (#176)
• The table.transform(drop_foreign_keys=) parameter and the sqlite-utils transform --drop-foreign-key option have changed. They now accept just the name of the column rather than requiring all three of the column, other table and other column. This is technically a backwards-incompatible change but I chose not to bump the major version number because the transform feature is so new. (#177)
• The table .disable_fts(), .rebuild_fts(), .delete(), .delete_where() and .add_missing_columns() methods all now return self, which means they can be chained together with other table operations.

2.20

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_function decorator 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 optional select= argument for specifying which columns should be selected, see Listing rows.

2.19

• New sqlite-utils add-foreign-keys command for Adding multiple foreign keys at once. (#157)
• New table.enable_fts(..., replace=True) argument for replacing an existing FTS table with a new configuration. (#160)
• New table.add_foreign_key(..., ignore=True) argument for ignoring a foreign key if it already exists. (#112)

2.18

• table.rebuild_fts() method for rebuilding a FTS index, see Rebuilding a full-text search table. (#155)
• sqlite-utils rebuild-fts data.db command for rebuilding FTS indexes across all tables, or just specific tables. (#155)
• table.optimize() method no longer deletes junk rows from the *_fts_docsize table. This was added in 2.17 but it turns out running table.rebuild_fts() is a better solution to this problem.
• Fixed a bug where rows with additional columns that are inserted after the first batch of records could cause an error due to breaking SQLite's maximum number of parameters. Thanks, Simon Wiles. (#145)