来自Python3的sqlite3模块中的自动提交问题 [英] Problems with autocommit in sqlite3 module from Python3

问题描述

我无法在给定的数据库连接中关闭自动提交模式.根据我从sqlite3模块文档中了解到的信息，下面的python代码不应该引发任何AssertionError异常，但是确实如此.

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.

表"nomes"已存在于数据库中，并且包含"Nome"列

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 == [])

此处的Sqlite3文档: https://docs.python.org/2/library/sqlite3.html#connection-objects

Sqlite3 documentation here: https://docs.python.org/2/library/sqlite3.html#connection-objects

isolation_level

获取或设置当前的隔离级别.对于自动提交模式或"DEFERRED"，"IMMEDIATE"或"EXCLUSIVE"之一不适用.有关详细说明，请参见控制事务一节.

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.

我在这里想念什么?

推荐答案

独立于您使用的isolation_level，使用with语句意味着在with块结束之后，所有内容都会自动处理.

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.

看一下这个文档: https://docs. python.org/2/library/sqlite3.html#using-the-connection-as-a-context-manager

连接对象可用作自动自动运行的上下文管理器 提交或回滚事务.如果发生例外情况， 交易被回滚；否则，交易将被提交:

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:

这篇关于来自Python3的sqlite3模块中的自动提交问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！