Python SQLite – How to manually BEGIN and END transactions?

程式愈寫愈複雜，怕資料不一致，所以 connection 的 isolation_level 設到 None = auto commit mode.

雖然，沒有下 commit() 不會寫到 database 裡，但由於為了效能，我偷偷的把 connection 放在記憶體裡重覆使用，connection 沒有被 close() 掉，所以還在這一個 request 的這一個 begin trans 裡，這一個 request 裡的垃圾，會被queue  在 memory 裡，等下一個 request 的 會把沒有被 commit 的 trans commit 進 database 裡，解法是使用預設的 isolation 遇到 error 就要多執行一次 rollback() 把 queue 在 memory 裡的垃圾清掉。

但，這還是會遇到一個問題，就是其他關於Database 的 Object 被包裝起來，這些 Object 為確保table 一定存在，所以會先執行 create table if. 遇到 create table connect 就被 commit 了。 @_@;

---

I am trying to figure out how to properly override the auto-transaction when using SQLite in Python. When I try and run

cursor.execute("BEGIN;")
.....an assortment of insert statements...
cursor.execute("END;")

I get the following error:

OperationalError: cannot commit - no transaction is active

---

you have to set isolation level to None. Code example:

s = sqlite3.connect("./data.db")
s.isolation_level = None

try:
    c = s.cursor()
    c.execute("begin")
    ...
    c.execute("commit")
except:
    c.execute("rollback")

---

---

11.13.6. Controlling Transactions¶

By default, the sqlite3 module opens transactions implicitly before a Data Modification Language (DML) statement (i.e. INSERT/UPDATE/DELETE/REPLACE), and commits transactions implicitly before a non-DML, non-query statement (i. e. anything other than SELECT or the aforementioned).

So if you are within a transaction and issue a command like CREATE TABLE ..., VACUUM, PRAGMA, the sqlite3 module will commit implicitly before executing that command. There are two reasons for doing that. The first is that some of these commands don’t work within transactions. The other reason is that sqlite3 needs to keep track of the transaction state (if a transaction is active or not).

You can control which kind of BEGIN statements sqlite3 implicitly executes (or none at all) via the isolation_level parameter to the connect() call, or via the isolation_level property of connections.

If you want autocommit mode, then set isolation_level to None.

Otherwise leave it at its default, which will result in a plain “BEGIN” statement, or set it to one of SQLite’s supported isolation levels: “DEFERRED”, “IMMEDIATE” or “EXCLUSIVE”.

---

isolation_level

Get or set the current isolation level. None for autocommit mode or one of “DEFERRED”, “IMMEDIATE” or “EXCLUSIVE”. See section Controlling Transactions for a more detailed explanation.

---

commit()

This method commits the current transaction. If you don’t call this method, anything you did since the last call to commit() is not visible from other database connections. If you wonder why you don’t see the data you’ve written to the database, please check you didn’t forget to call this method.

rollback()

This method rolls back any changes to the database since the last call to commit().

close()

This closes the database connection. Note that this does not automatically call commit(). If you just close your database connection without calling commit() first, your changes will be lost!