4.0rc4

• Breaking change: table.extract() - and the sqlite-utils extract command - no longer extract rows where every extracted column is null. Those rows now keep a null value in the new foreign key column instead of pointing at an all-null record in the lookup table. When extracting multiple columns, rows are still extracted if at least one of the columns has a value. (#186)
• The extracts= option to table.insert() and friends no longer creates a lookup table record for None values - the column value stays null. Previously every batch of inserted rows containing a None value would add a duplicate null record to the lookup table.
• Fixed a bug where table.lookup() inserted a duplicate row on every call if any of the lookup values were None. Lookup values are now compared using IS so that None values match existing rows correctly.
• JSON output from the command-line tool no longer escapes non-ASCII characters, so sqlite-utils data.db "select '日本語' as text" now outputs [{"text": "日本語"}]. This matches how values were already stored by insert and how CSV/TSV output already behaved. A new --ascii option restores the previous behavior of escaping non-ASCII characters, for output destinations that cannot handle UTF-8 - see Unicode characters in JSON. The option is available on the query, rows, search, tables, views, triggers, indexes and memory commands. The convert --multi --dry-run preview and plugins output also no longer escape non-ASCII characters. (#625)
• --no-headers now omits the header row from --fmt and --table output, not just CSV and TSV output. (#566)
• table.insert_all(..., pk=...) now raises InvalidColumns if pk= names columns that do not exist in an existing table. Previously this behaved inconsistently, with single-row inserts raising a KeyError while other row counts succeeded. (#732)
• Fixed an IndexError from table.insert(..., pk=..., ignore=True) when an ignored insert followed writes to another table on the same connection. last_pk is now populated from the explicit primary key value instead of looking up a stale lastrowid. (#554)
• Fixed a bug where a failed write statement executed with db.execute() left the driver’s implicit transaction open. Every subsequent write then joined that phantom transaction, which nothing committed, so their work was silently rolled back when the connection was closed. The implicit transaction opened by a failed statement is now rolled back before the exception is raised. A failed write inside a transaction opened with db.begin() or db.atomic() leaves that transaction open and untouched, as before.
• Fixed a bug where transaction-control statements prefixed with an empty statement - db.query("; COMMIT") - or a UTF-8 byte order mark slipped past the check that rejects them, committing the caller’s open transaction before raising a confusing OperationalError. The keyword scanner used by db.query() and db.execute() now skips leading ; and byte order marks, matching what the sqlite3 driver tolerates before the first token, so these statements are rejected with a ValueError without being executed. The same fix means db.execute("; BEGIN") no longer auto-commits the transaction it just opened.
• Documented a limitation of db.query(): a PRAGMA statement that returns no rows raises a ValueError but still takes effect, because PRAGMA statements run outside the savepoint guard used to roll back other rejected statements. Use db.execute() for row-less PRAGMA statements.
• Fixed exception masking when a statement destroys the enclosing transaction. An error such as a RAISE(ROLLBACK) trigger or INSERT OR ROLLBACK conflict rolls back the whole transaction, destroying every savepoint - the cleanup in db.atomic() and db.query() then failed with OperationalError: no such savepoint (or cannot rollback - no transaction is active), hiding the original IntegrityError from code that tried to catch it. Cleanup now checks whether a transaction is still open first, so the original exception propagates.
• sqlite-utils migrate --list is now read-only even when the migrations file uses the legacy sqlite_migrate.Migrations class, whose listing methods create the _sqlite_migrations table as a side effect. The listing now runs inside a transaction that is rolled back.
• sqlite-utils insert ... --pk <missing column> and sqlite-utils extract <missing column> now show a clean Error: message instead of a raw Python traceback. The extract command also shows a clean error when pointed at a view.
• Fixed a bug where running table.extract() more than once against the same lookup table inserted duplicate rows for values containing null - SQLite unique indexes treat NULL values as distinct, so INSERT OR IGNORE alone could not dedupe them. Each repeat extract added another copy that nothing referenced. The insert now uses an IS-based NOT EXISTS guard so null-containing rows match existing lookup rows.
• db.add_foreign_keys() no longer silently ignores requested ON DELETE/ON UPDATE actions when a foreign key with the same columns already exists - it raises AlterError suggesting table.transform(), since the actions of an existing foreign key cannot be changed in place. Exact duplicates, including actions, are still skipped so repeated calls stay idempotent. The method also now validates that compound foreign keys have the same number of columns on both sides, instead of silently discarding the extra columns.
• db.ensure_autocommit_on() now raises TransactionError if called while a transaction is open. Assigning isolation_level commits any pending transaction as a side effect, so entering the block silently committed the caller’s open transaction and made a later rollback() a no-op.
• sqlite-utils migrate --stop-before now exits with an error if the named migration has already been applied. Previously the name passed validation but was only checked against pending migrations, so every migration after it was silently applied - the exact outcome --stop-before exists to prevent. Migrations.apply(db, stop_before=...) raises ValueError in the same situation, before applying anything.
• Fixed a regression where table.insert(..., pk=..., alter=True) raised InvalidColumns if the primary key column did not exist in the table yet. With alter=True the check now waits until the record keys are known, so a pk column supplied by the records is added by the alter as it was in 3.x. A pk column found in neither the table nor the records still raises InvalidColumns.
• Fixed a bug where inserting CSV or TSV data into an existing table rewrote that table’s column types to match the incoming file. Type detection is the default in 4.0, so sqlite-utils insert data.db places places.csv --csv against a table with a TEXT zip code column would convert the column to INTEGER and corrupt values with leading zeros - "01234" became 1234. Detected types are now only applied when the insert or upsert command creates the table.
• Fixed pks_and_rows_where() raising AttributeError when called on a view, and no longer double-quotes the synthesized rowid column in its generated SQL - SQLite turns a double-quoted identifier that does not resolve into a string literal, which on a view produced a confusing KeyError instead of the OperationalError raised in 3.x. Compound primary keys returned by this method now follow PRIMARY KEY declaration order.
• The foreign_keys= argument to create() and insert() accepts a mixed list of ForeignKey objects, tuples and column name strings again. In 4.0 pre-releases mixing ForeignKey objects with tuples raised a ValueError - a regression from 3.x, where ForeignKey was a namedtuple and passed the tuple checks.
• ForeignKey objects are hashable again. The 4.0 change from namedtuple to dataclass accidentally made them unhashable, breaking patterns like set(table.foreign_keys) that worked in 3.x. ForeignKey is now a frozen dataclass - immutable and hashable, like the namedtuple was.
• Fixed a bug where compound primary key columns were returned in table column order instead of PRIMARY KEY declaration order. For a table declared as CREATE TABLE other (b TEXT, a TEXT, PRIMARY KEY (a, b)) an implicit FOREIGN KEY (x, y) REFERENCES other was introspected as referencing (b, a) when SQLite resolves it as (a, b) - running transform() on such a table then rewrote the schema with the inverted column order, silently reversing the meaning of the constraint and causing foreign key errors on valid data. table.pks, compound foreign key guessing and transform() now all use the primary key declaration order, and transform() no longer reorders a compound PRIMARY KEY (b, a) into table column order.