Database() now accepts all three Python transaction handling modes and
behaves identically in each. For autocommit=False connections - where
the driver holds an implicit transaction open at all times - the
library tracks transaction ownership itself:
- A new _explicit_transaction flag distinguishes transactions opened
with begin()/atomic() from the driver's implicit one, exposed as a
new db.in_transaction property (conn.in_transaction is always True
in this mode)
- begin() claims the driver's implicit transaction instead of
executing BEGIN, which the driver would reject; BEGIN/COMMIT/
ROLLBACK passed to db.execute() are routed through begin()/commit()/
rollback()
- Writes outside a user transaction commit the implicit transaction
immediately, preserving the library's auto-commit contract
- Row-returning statements outside a transaction fetch eagerly and
commit, so the implicit read transaction does not hold a shared
lock that blocks writes from other connections
- PRAGMA and VACUUM run in temporary driver autocommit mode
(ensure_autocommit_on() now flips conn.autocommit), since PRAGMAs
are silently ignored and VACUUM refused inside the implicit
transaction
A new pytest --sqlite-autocommit-false option runs the entire suite
in this mode, wired into CI alongside --sqlite-autocommit. Tests that
asserted on conn.in_transaction or wrote through db.conn without
committing now use the mode-aware library API instead.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01PFkrS2nuP8mo1jmT594VCd
SQLite 3.23.1 rejects a UTF-8 byte order mark before the first token,
so the BOM variant of the execute()-prefixed-BEGIN test now skips when
the SQLite version does not accept a leading BOM. And versions before
3.36 allowed selecting rowid from a view, returning NULL, rather than
raising an error - the pks_and_rows_where() view test now accepts
either behavior.
Refs https://github.com/simonw/sqlite-utils/issues/769#issuecomment-4900034150
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
query() and execute() classified statements using
sql.lstrip().upper().startswith(...), which a leading SQL comment
defeated: db.query('/* c */ COMMIT') inside db.atomic() committed
the caller's transaction and masked the ValueError with a 'no such
savepoint' error, comment-prefixed PRAGMAs ran inside the savepoint
guard where journal mode changes are refused, and a comment-prefixed
BEGIN passed to db.execute() was instantly auto-committed.
The new _first_keyword() helper skips leading whitespace and -- or
/* */ comments - the only things SQLite's tokenizer allows before
the first token - and returns that token for exact comparison, so
keyword detection now matches what SQLite itself will see.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Raw write statements previously opened an implicit transaction that
stayed open until something committed it - the write was visible on
the same connection, making it look saved, but was silently rolled
back when the connection closed. execute() now commits any implicit
transaction it opens, so raw writes behave like every other write
in the library: committed as soon as they run, unless an explicit
transaction (db.begin() or db.atomic()) is open, in which case they
join it.
Row-returning writes such as INSERT ... RETURNING via db.query()
commit once their rows have been iterated. atomic(), commit() and
rollback() now issue literal COMMIT/ROLLBACK statements, which have
identical semantics in every sqlite3 connection mode. The CLI bulk
command uses db.atomic() instead of 'with db.conn:'.
This simplifies the transaction contract to: everything commits
immediately unless you explicitly opened a transaction. Docs
updated throughout; changelog and upgrading guide cover the
breaking change for code that relied on rolling back uncommitted
execute() writes.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01UnLnhsH25Nnv7LHhekUfPd
Manual transaction control previously required mixing raw
db.execute("begin") strings with methods on db.conn. These thin
wrappers give the documentation and callers a single consistent
idiom. Docs updated to use them throughout.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01UnLnhsH25Nnv7LHhekUfPd
Documents that atomic() opens a deferred BEGIN, that calling it
while the connection is already inside a transaction produces a
savepoint whose changes only persist when the outer transaction
commits, and that Python 3.12+ autocommit=True/False connections
are not supported. Adds a test pinning the savepoint-inside-manual-
BEGIN behavior.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01UnLnhsH25Nnv7LHhekUfPd
