CODEWITHSUNDEEP
pythonodbcpyodbc
Roman
Roman

Reputation: 543

Pyodbc - determine if any transaction is pending and needs to be commited

I'm working with pyodbc with 'autocommit' set to false. I assume that if only SELECT sql statements are used (no INSERT or UPDATE) then a call to 'commit' function would do nothing with the DB.

Is there any way in pyodbc to determine if there is any changes pending that would be lost if I don't call 'commit'?

I have been thinking to use pyodbc 'getinfo' function which is a wrapper over SQLGetInfo, but can't find any suitable parameter for it.

Actually what I want to do is just notify a user that data in the DB are going to be changed, but I don't want to control what SQL statements have been used (only SELECT or also UPDATE & INSERT) because there are too many places where I would need to check it.

Upvotes: 3

Views: 2913

Answers (1)

Emer
Emer

Reputation: 3824

Unfortunately, pyodbc has not a build-in function to check pending commits.

In SQL Server (starting with 2008) you can check pending commits at database level by using the database_transaction_state field in sys.dm_tran_database_transactions table. The transaction's state with pending commit is expected to be 4. While one without pending commit is expected to be 3.

From the documentation, the mentioned states are defined as:

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

4 = The transaction has generated log records.

The following script checks this idea.

Output

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

Code

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()

Upvotes: 2

Related Questions