python sqlite3事务,来自Python3的sqlite3模块中的自动提交问题

I'm having trouble turning off the auto-commit mode in a given database connection. According to what I understood from the sqlite3 module documentation, the python code bellow should not raise any AssertionError exception, but it does.

The table "nomes" already exists in the database and contains the column "Nome"

import sqlite3

_PATH_DB = '/rhome/FNORO/tabelao/sofia.sqlite' # path to database

_ISOLATION_LEVEL = "EXCLUSIVE"

_TEST_NAME = "TEST1"

# delete name from database

with sqlite3.connect(_PATH_DB, isolation_level = _ISOLATION_LEVEL) as conn:

query = 'DELETE FROM nomes WHERE nome = "{}"'.format(_TEST_NAME)

conn.execute(query)

conn.commit()

# insert name _TEST_NAME without executing conn.commit()

with sqlite3.connect(_PATH_DB, isolation_level = _ISOLATION_LEVEL) as conn:

query = 'INSERT INTO nomes(nome) VALUES("{}")'.format(_TEST_NAME)

conn.execute(query)

# check if name already existis

with sqlite3.connect(_PATH_DB, isolation_level = _ISOLATION_LEVEL) as conn:

query = 'SELECT nome FROM nomes WHERE nome = "{}"'.format(_TEST_NAME)

res = conn.execute(query).fetchall()

assert (res == [])

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.

What am I missing here?

解决方案

Independently of the isolation_level you use, using the with statement means everything will be handled automatically after the with block is ended.

That means commiting any remaining open transactions.

Connection objects can be used as context managers that automatically

commit or rollback transactions. In the event of an exception, the

transaction is rolled back; otherwise, the transaction is committed: