2

In sqlite python, one has to run commit() to make sure a SQL statement is executed if auto-commit is not enabled. Auto-commit is enabled by doing something like this sqlite3.connect('sqlitedb.db', isolation_level=None)

Is it a good practice to enable auto-commit all the time? THis is to avoid bugs that can happen when one forgets to run commit().

What are some situations, if any, that auto-commit is better to be disabled?

I am using sqlite3 and python v3.6

guagay_wk
  • 26,337
  • 54
  • 186
  • 295
  • 2
    May I ask why the negative vote? What is wrong with the question? Good to know so that I can improve on asking questions in future. – guagay_wk Sep 14 '18 at 14:18
  • 2
    Generally, in the transactions related environments, you should not use auto-commit enabled as it can lead to inconsistency in the data. – mad_ Sep 14 '18 at 14:21
  • So, for data consistency, it is best practice to disable auto-commit all the time? Why does auto-commit lead to data inconsistency? – guagay_wk Sep 14 '18 at 14:23
  • My bad it should be atomicity. Have a quick read through https://stackoverflow.com/questions/12664837/database-atomicity-consistency – mad_ Sep 14 '18 at 14:32

1 Answers1

3

Autocommit should be disabled if multiple operations belong together logically to ensure that not only some of them are executed (atomicity).

Additionally it should be disabled if multiple operations are done consecutively in a short period of time for performance reasons.

For databases with concurrent access from different threads/processes additional consistency considerations apply but this usage is unlikely for Sqlite.

Michael Butscher
  • 10,028
  • 4
  • 24
  • 25