5 ms·

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 i

by jph00 2mo ago

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/ 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 2mo ago
> changes how sqlite's transactions work What specifically are you referring to? The apswutils website also does not explain.
- dxdm 2mo ago
  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.Connection.autocommit 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 2mo ago
    > 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 2mo ago
      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.Connection.autocommit https://docs.python.org/3/library/sqlite3.html#sqlite3.Conne... [1] https://docs.python.org/3/library/sqlite3.html#sqlite3-transaction-control-autocommit https://docs.python.org/3/library/sqlite3.html#sqlite3-trans... [2] https://sqlite.org/lang_transaction.html#deferred_immediate_and_exclusive_transactions https://sqlite.org/lang_transaction.html#deferred_immediate_...
      - jmalicki 2mo ago
        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 2mo ago
  Pythons options are here: https://docs.python.org/3/library/sqlite3.html https://docs.python.org/3/library/sqlite3.html SQLite behavior is here: https://sqlite.org/lang_transaction.html https://sqlite.org/lang_transaction.html . The regular implicit transactions there plus explicit where needed aren’t supported in any python mode.
  - jmalicki 2mo ago
    > 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.

by jph00 2mo ago

jmalicki 2mo ago

> changes how sqlite's transactions work What specifically are you referring to? The apswutils website also does not explain.

dxdm 2mo ago
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.Connection.autocommit 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 2mo ago
  > 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 2mo ago
    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.Connection.autocommit https://docs.python.org/3/library/sqlite3.html#sqlite3.Conne... [1] https://docs.python.org/3/library/sqlite3.html#sqlite3-transaction-control-autocommit https://docs.python.org/3/library/sqlite3.html#sqlite3-trans... [2] https://sqlite.org/lang_transaction.html#deferred_immediate_and_exclusive_transactions https://sqlite.org/lang_transaction.html#deferred_immediate_...
    - jmalicki 2mo ago
      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 2mo ago
Pythons options are here: https://docs.python.org/3/library/sqlite3.html https://docs.python.org/3/library/sqlite3.html SQLite behavior is here: https://sqlite.org/lang_transaction.html https://sqlite.org/lang_transaction.html . The regular implicit transactions there plus explicit where needed aren’t supported in any python mode.
- jmalicki 2mo ago
  > 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.