[jph00]

I'm a big fan of sqlite-utils, but I really don't like how Python (particularly 3.12+) changes how sqlite's transactions work -- the native behavior explained in the sqlite docs is much better IMO. I understand why Python had to change it (to be compatible with other databases) but I don't think it's a good model for sqlite.

Therefore, I created apsw-utils, a port of sqlite-utils to the amazingly-awesome apsw lib -- which is a really idiomatic sqlite lib for python. It's here: https://answerdotai.github.io/apswutils/

I've used it in lots of projects including in significant production stuff, and it's always worked great for me. IMO if you're serious about doing sqlite in python, at some point you'll probably want to check out apsw.

[jmalicki]

> changes how sqlite's transactions work

What specifically are you referring to? The apswutils website also does not explain.

[dxdm]

They're probably talking about the addition of the autocommit flag, which hides more fine-grained transaction control in favor of more uniform behavior across multiple databases:

https://docs.python.org/3/library/sqlite3.html#sqlite3.Conne...

You can still use previous behavior with "legacy" mode that lets you control when transactions are opened in which isolation level.

[jmalicki]

> hides more fine-grained transaction control

In what way does having autocommit=False hide more fine-grained transaction control?

autocommit=False gives full control to the programmer to do whatever they want.

[dxdm]

From the link in my previous post[0]:

> False: Select PEP 249-compliant transaction behaviour, implying that sqlite3 ensures a transaction is always open.

This means you don't get to control the isolation level of the transaction, because [1]:

> sqlite3 uses BEGIN DEFERRED statements when opening transactions.

If you want to use `IMMEDIATE` or `EXCLUSIVE` isolation level[2] for your sqlite transaction using the new flag, you have to set `autocommit=True` to be able to open the transaction yourself with `.execute("BEGIN IMMEDIATE")`.

However, with `autocommit=True`, the connection's `.commit()` and `.rollback()` methods will *silently do nothing* and you have to execute the respective raw SQL yourself to commit or abort your manually-opened transaction. This also concerns the context-manager behavior of the connection object, which will not commit or abort manual transactions on context exit in this case.

So, the autocommit flag becomes a little complicated and foot-gunny if you want more precise control over when exactly other readers or writers should get blocked by sqlite.

[0] https://docs.python.org/3/library/sqlite3.html#sqlite3.Conne...

[1] https://docs.python.org/3/library/sqlite3.html#sqlite3-trans...

[2] https://sqlite.org/lang_transaction.html#deferred_immediate_...

[jmalicki]

Thank you!

The link had some of the details, but not the intelligent thought linking them into a narrative, much appreciated! This is something super important I would have been foot-gunned on at some point.

[jph00]

Pythons options are here: https://docs.python.org/3/library/sqlite3.html

SQLite behavior is here: https://sqlite.org/lang_transaction.html . The regular implicit transactions there plus explicit where needed aren’t supported in any python mode.

[jmalicki]

> The regular implicit transactions there plus explicit where needed aren’t supported in any python mode

Specific examples would be extremely useful. You've done some work learning and deducing this stuff, others could learn if you would share and explain it.