database.execute_write_fn() should default to running in a transaction about datasette

[simonw]

I'm not going to do this for execute_isolated_fn() because that opens and then closes a whole database connection for you already, which should ensure any transactions are safely committed when it closes.

[simonw]

Did a quick test to check that this wouldn't break anything that DID have a with conn: - it looks like nesting those has no negative effect in SQLite in Python:

>>> with db:
...   with db:
...     with db:
...       db.execute('insert into foo (id) values (4)')
... 
<sqlite3.Cursor object at 0x1032f2ac0>
>>> db.execute('select * from foo').fetchall()
[(1,), (2,), (3,), (4,)]

[simonw]

Documented here: https://docs.datasette.io/en/latest/internals.html#await-db-execute-write-fn-fn-block-true-transaction-true

[simonw]

Now that I've made this change I can fix a bunch of places in the code that are using with conn: that no longer need to.

In reviewing those I also spotted that many of the JSON API write features - creating tables, inserting rows etc - were using execute_write_fn() without an explicit transaction. Those should be fixed now!

[simonw]

This change may be small but it's significant enough that I'm going to push a new alpha so I can start exercising it on Datasette Cloud.

[simonw]

Datasette 1.0a10 is out: https://docs.datasette.io/en/latest/changelog.html#a10-2024-02-17