Simonw Sqlite Utils Versions Save

• Support for creating tables in SQLite STRICT mode. Thanks, Taj Khattra. (#344)
  • CLI commands create-table, insert and upsert all now accept a --strict option.
  • Python methods that can create a table - table.create() and insert/upsert/insert_all/upsert_all all now accept an optional strict=True parameter.
  • The transform command and table.transform() method preserve strict mode when transforming a table.
• The sqlite-utils create-table command now accepts str, int and bytes as aliases for text, integer and blob respectively. (#606)

• The --load-extension=spatialite option and find_spatialite() utility function now both work correctly on arm64 Linux. Thanks, Mike Coats. (#599)
• Fix for bug where sqlite-utils insert could cause your terminal cursor to disappear. Thanks, Luke Plant. (#433)
• datetime.timedelta values are now stored as TEXT columns. Thanks, Harald Nezbeda. (#522)
• Test suite is now also run against Python 3.12.

• Fixed a bug where table.transform() would sometimes re-assign the rowid values for a table rather than keeping them consistent across the operation. (#592)

Adding foreign keys to a table no longer uses PRAGMA writable_schema = 1 to directly manipulate the sqlite_master table. This was resulting in errors in some Python installations where the SQLite library was compiled in a way that prevented this from working, in particular on macOS. Foreign keys are now added using the table transformation mechanism instead. (#577)

This new mechanism creates a full copy of the table, so it is likely to be significantly slower for large tables, but will no longer trigger table sqlite_master may not be modified errors on platforms that do not support PRAGMA writable_schema = 1.

A new plugin, sqlite-utils-fast-fks, is now available for developers who still want to use that faster but riskier implementation.

Other changes:

• The table.transform() method has two new parameters: foreign_keys= allows you to replace the foreign key constraints defined on a table, and add_foreign_keys= lets you specify new foreign keys to add. These complement the existing drop_foreign_keys= parameter. (#577)
• The sqlite-utils transform command has a new --add-foreign-key option which can be called multiple times to add foreign keys to a table that is being transformed. (#585)
• sqlite-utils convert now has a --pdb option for opening a debugger on the first encountered error in your conversion script. (#581)
• Fixed a bug where sqlite-utils install -e '.[test]' option did not work correctly.

This release introduces a new plugin system. (#567)

• Documentation describing how to build a plugin.
• Plugin hook: register_commands(cli), for plugins to add extra commands to sqlite-utils. (#569)
• Plugin hook: prepare_connection(conn). Plugins can use this to help prepare the SQLite connection to do things like registering custom SQL functions. Thanks, Alex Garcia. (#574)
• sqlite_utils.Database(..., execute_plugins=False) option for disabling plugin execution. (#575)
• sqlite-utils install -e path-to-directory option for installing editable code. This option is useful during the development of a plugin. (#570)
• table.create(...) method now accepts replace=True to drop and replace an existing table with the same name, or ignore=True to silently do nothing if a table already exists with the same name. (#568)
• sqlite-utils insert ... --stop-after 10 option for stopping the insert after a specified number of records. Works for the upsert command as well. (#561)
• The --csv and --tsv modes for insert now accept a --empty-null option, which cases empty strings in the CSV file to be stored as null in the database. (#563)
• New db.rename_table(table_name, new_name) method for renaming tables. (#565)
• sqlite-utils rename-table my.db table_name new_name command for renaming tables. (#565)
• The table.transform(...) method now takes an optional keep_table=new_table_name parameter, which will cause the original table to be renamed to new_table_name rather than being dropped at the end of the transformation. (#571)
• Documentation now notes that calling table.transform() without any arguments will reformat the SQL schema stored by SQLite to be more aesthetically pleasing. (#564)

• sqlite-utils will now use sqlean.py in place of sqlite3 if it is installed in the same virtual environment. This is useful for Python environments with either an outdated version of SQLite or with restrictions on SQLite such as disabled extension loading or restrictions resulting in the sqlite3.OperationalError: table sqlite_master may not be modified error. (#559)
• New with db.ensure_autocommit_off() context manager, which ensures that the database is in autocommit mode for the duration of a block of code. This is used by db.enable_wal() and db.disable_wal() to ensure they work correctly with pysqlite3 and sqlean.py.
• New db.iterdump() method, providing an iterator over SQL strings representing a dump of the database. This uses sqlite-dump if it is available, otherwise falling back on the conn.iterdump() method from sqlite3. Both pysqlite3 and sqlean.py omit support for iterdump() - this method helps paper over that difference.

• Examples in the CLI documentation can now all be copied and pasted without needing to remove a leading $. (#551)
• Documentation now covers Setting up shell completion for bash and zsh. (#552)

• 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.

• 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)

• 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.