python - Pyodbc - 确定是否有任何事务未决且需要提交

Question

我正在使用 pyodbc，并将“autocommit”设置为 false。我假设如果只使用 SELECT sql 语句(没有 INSERT 或 UPDATE)，那么调用“commit”函数将不会对数据库。

pyodbc 中是否有任何方法可以确定是否有任何未决的更改，如果我不调用“提交”，这些更改将会丢失？

我一直在考虑使用 pyodbc 'getinfo' 函数，它是 SQLGetInfo 的包装器，但找不到任何合适的参数。

实际上我想做的只是通知用户数据库中的数据将被更改，但我不想控制使用了哪些 SQL 语句(仅 SELECT 或还有 UPDATE & INSERT) 因为我需要检查的地方太多了。

Answer 1

不幸的是，pyodbc 没有内置函数来检查未决提交。

在 SQL Server(从 2008 开始)中，您可以使用 sys.dm_tran_database_transactions 表中的 database_transaction_state 字段检查数据库级别的待定提交。待提交的事务状态预计为 4。而没有待定提交的预计为 3。

来自documentation ，上述状态定义为:

3 = The transaction has been initialized but has not generated any log records.

4 = The transaction has generated log records.

下面的脚本检查了这个想法。

输出

Starting point, no pending transaction or commits
    @@TRANCOUNT = 0
    database_transaction_state = None
Disable autocommit
    @@TRANCOUNT = 1
    database_transaction_state = 3
Perform an Insert
    @@TRANCOUNT = 1
    database_transaction_state = 4
Explicit commit
    @@TRANCOUNT = 1
    database_transaction_state = 3
Enable autocommit
    @@TRANCOUNT = 0
    database_transaction_state = None

代码

import pyodbc

conn_param = {
    "DRIVER": "{ODBC Driver 13 for SQL Server}",
    "SERVER": "(localdb)\\ProjectsV13",
    "DATABASE": "master"
}

conn_string = ";".join(["{}={}".format(k, v) for k, v in conn_param.items()])

conn = pyodbc.connect(conn_string, autocommit=True)
cursor = conn.cursor()


def check():
    print("\t@@TRANCOUNT = {}".format(
        cursor.execute("select @@TRANCOUNT").fetchval()
    ))

    print("\tdatabase_transaction_state = {}".format(
        cursor.execute("""
        select database_transaction_state
        from sys.dm_tran_database_transactions 
        where transaction_id = CURRENT_TRANSACTION_ID() 
        and database_id = (
            select dbid from sys.sysprocesses where spid = @@SPID
        )""").fetchval()
    ))


cursor.execute("DROP TABLE IF EXISTS testTable")
cursor.execute("SELECT * INTO testTable FROM (VALUES (1), (2), (3)) as x(a)")

print("Starting point, no pending transaction or commits")
check()

print("Disable autocommit")
conn.autocommit = False
check()

print("Perform an Insert")
cursor.execute("INSERT INTO testTable VALUES (4)")
check()

print("Explicit commit")
conn.commit()
check()

print("Enable autocommit")
conn.autocommit = True
check()

cursor.execute("DROP TABLE IF EXISTS testTable")
cursor.close()
conn.close()