3.32

• New experimental sqlite-utils tui interface for interactively building command-line invocations, powered by Trogon. This requires an optional dependency, installed using sqlite-utils install trogon. There is a screenshot in the documentation. (#545)
• sqlite-utils analyze-tables command (documentation) now has a --common-limit 20 option for changing the number of common/least-common values shown for each column. (#544)
• sqlite-utils analyze-tables --no-most and --no-least options for disabling calculation of most-common and least-common values.
• If a column contains only null values, analyze-tables will no longer attempt to calculate the most common and least common values for that column. (#547)
• Calling sqlite-utils analyze-tables with non-existent columns in the -c/--column option now results in an error message. (#548)
• The table.analyze_column() method (documented here) now accepts most_common=False and least_common=False options for disabling calculation of those values.

3.31

• Dropped support for Python 3.6. Tests now ensure compatibility with Python 3.11. (#517)
• Automatically locates the SpatiaLite extension on Apple Silicon. Thanks, Chris Amico. (#536)
• New --raw-lines option for the sqlite-utils query and sqlite-utils memory commands, which outputs just the raw value of the first column of evy row. (#539)
• Fixed a bug where table.upsert_all() failed if the not_null= option was passed. (#538)
• Fixed a ResourceWarning when using sqlite-utils insert. (#534)
• Now shows a more detailed error message when sqlite-utils insert is called with invalid JSON. (#532)
• table.convert(..., skip_false=False) and sqlite-utils convert --no-skip-false options, for avoiding a misfeature where the convert() mechanism skips rows in the database with a falsey value for the specified column. Fixing this by default would be a backwards-incompatible change and is under consideration for a 4.0 release in the future. (#527)
• Tables can now be created with self-referential foreign keys. Thanks, Scott Perry. (#537)
• sqlite-utils transform no longer breaks if a table defines default values for columns. Thanks, Kenny Song. (#509)
• Fixed a bug where repeated calls to table.transform() did not work correctly. Thanks, Martin Carpenter. (#525)
• Improved error message if rows_from_file() is passed a non-binary-mode file-like object. (#520)

3.30

• Now tested against Python 3.11. (#502)
• New table.search_sql(include_rank=True) option, which adds a rank column to the generated SQL. Thanks, Jacob Chapman. (#480)
• Progress bars now display for newline-delimited JSON files using the --nl option. Thanks, Mischa Untaga. (#485)
• New db.close() method. (#504)
• Conversion functions passed to table.convert(...) can now return lists or dictionaries, which will be inserted into the database as JSON strings. (#495)
• sqlite-utils install and sqlite-utils uninstall commands for installing packages into the same virtual environment as sqlite-utils, described here. (#483)
• New sqlite_utils.utils.flatten() utility function. (#500)
• Documentation on using Just to run tests, linters and build documentation.
• Documentation now covers the Release process for this package.

3.29

• The sqlite-utils query, memory and bulk commands now all accept a new --functions option. This can be passed a string of Python code, and any callable objects defined in that code will be made available to SQL queries as custom SQL functions. See Defining custom SQL functions for details. (#471)
• db[table].create(...) method now accepts a new transform=True parameter. If the table already exists it will be transform to match the schema configuration options passed to the function. This may result in columns being added or dropped, column types being changed, column order being updated or not null and default values for columns being set. (#467)
• Related to the above, the sqlite-utils create-table command now accepts a --transform option.
• New introspection property: table.default_values returns a dictionary mapping each column name with a default value to the configured default value. (#475)
• The --load-extension option can now be provided a path to a compiled SQLite extension module accompanied by the name of an entrypoint, separated by a colon - for example --load-extension ./lines0:sqlite3_lines0_noread_init. This feature is modelled on code first contributed to Datasette by Alex Garcia. (#470)
• Functions registered using the db.register_function() method can now have a custom name specified using the new db.register_function(fn, name=...) parameter. (#458)
• sqlite-utils rows has a new --order option for specifying the sort order for the returned rows. (#469)
• All of the CLI options that accept Python code blocks can now all be used to define functions that can access modules imported in that same block of code without needing to use the global keyword. (#472)
• Fixed bug where table.extract() would not behave correctly for columns containing null values. Thanks, Forest Gregg. (#423)
• New tutorial: Cleaning data with sqlite-utils and Datasette shows how to use sqlite-utils to import and clean an example CSV file.
• Datasette and sqlite-utils now have a Discord community. Join the Discord here.

3.28

• New table.duplicate(new_name) method for creating a copy of a table with a matching schema and row contents. Thanks, David. (#449)
• New sqlite-utils duplicate data.db table_name new_name CLI command for Duplicating tables. (#454)
• sqlite_utils.utils.rows_from_file() is now a documented API. It can be used to read a sequence of dictionaries from a file-like object containing CSV, TSV, JSON or newline-delimited JSON. It can be passed an explicit format or can attempt to detect the format automatically. (#443)
• sqlite_utils.utils.TypeTracker is now a documented API for detecting the likely column types for a sequence of string rows, see Detecting column types using TypeTracker. (#445)
• sqlite_utils.utils.chunks() is now a documented API for splitting an iterator into chunks. (#451)
• sqlite-utils enable-fts now has a --replace option for replacing the existing FTS configuration for a table. (#450)
• The create-index, add-column and duplicate commands all now take a --ignore option for ignoring errors should the database not be in the right state for them to operate. (#450)

3.27

See also the annotated release notes for this release.

• Documentation now uses the Furo Sphinx theme. (#435)
• Code examples in documentation now have a "copy to clipboard" button. (#436)
• sqlite_utils.utils.utils.rows_from_file() is now a documented API, see Reading rows from a file. (#443)
• rows_from_file() has two new parameters to help handle CSV files with rows that contain more values than are listed in that CSV file's headings: ignore_extras=True and extras_key="name-of-key". (#440)
• sqlite_utils.utils.maximize_csv_field_size_limit() helper function for increasing the field size limit for reading CSV files to its maximum, see Setting the maximum CSV field size limit. (#442)
• table.search(where=, where_args=) parameters for adding additional WHERE clauses to a search query. The where= parameter is available on table.search_sql(...) as well. See Searching with table.search(). (#441)
• Fixed bug where table.detect_fts() and other search-related functions could fail if two FTS-enabled tables had names that were prefixes of each other. (#434)

3.26.1

• Now depends on click-default-group-wheel, a pure Python wheel package. This means you can install and use this package with Pyodide, which can run Python entirely in your browser using WebAssembly. (#429)

  Try that out using the Pyodide REPL:

  >>> import micropip
  >>> await micropip.install("sqlite-utils")
  >>> import sqlite_utils
  >>> db = sqlite_utils.Database(memory=True)
  >>> list(db.query("select 3 * 5"))
  [{'3 * 5': 15}]

3.26

• New errors=r.IGNORE/r.SET_NULL parameter for the r.parsedatetime() and r.parsedate() convert recipes. (#416)
• Fixed a bug where --multi could not be used in combination with --dry-run for the convert command. (#415)
• New documentation: Using a convert() function to execute initialization. (#420)
• More robust detection for whether or not deterministic=True is supported. (#425)

3.25.1

• Improved display of type information and parameters in the API reference documentation. #413

3.25

• New hash_id_columns= parameter for creating a primary key that's a hash of the content of specific columns - see Setting an ID based on the hash of the row contents for details. (#343)
• New db.sqlite_version property, returning a tuple of integers representing the version of SQLite, for example (3, 38, 0).
• Fixed a bug where register_function(deterministic=True) caused errors on versions of SQLite prior to 3.8.3. (#408)
• New documented hash_record(record, keys=...) function.