Question about transaction

[zlev-intel]

Hi, I'm executing (exec query) the stored procedure, which have the transaction inside. I'm setting autocommit to false, but when I see results it looks like the transaction is opened by PYODBC too.
I found in internet the following:

• "That was from SQL server's perspective. In python, set autocommit = False will start an explicit tran,which equivalent to BEGIN TRANSACTION. "
  Is it right? How can I prevent opening transaction by pyodbc?

Please first make sure you have looked at:

• Documentation: https://github.com/mkleehammer/pyodbc/wiki
• Other issues

Environment

To diagnose, we usually need to know the following, including version numbers. On Windows, be
sure to specify 32-bit Python or 64-bit:

• Python: 3.8.10
• pyodbc: latest
• OS: Linux
• DB: SqlServer
• driver: mssql 17

Issue

Often it is easiest to describe your issue as "expected behavior" and "observed behavior".

[v-chojas]

If you turn off autocommit, then it will create an explicit transaction that you need to commit manually.

What is the actual problem the transaction is causing?

[zlev-intel]

I have a transaction inside stored procedure, so it caused nested transaction...

[gordthompson]

How can I prevent opening transaction by pyodbc?

Use autocommit=True. That will avoid the "outer" transaction and still allow the "inner" transaction(s) to work as expected:

import pyodbc

cnxn = pyodbc.connect(
    "DSN=mssql_199;UID=scott;PWD=tiger^5HHH", autocommit=True
)
crsr = cnxn.cursor()

table_name = "discussion_1164"
crsr.execute(f"DROP TABLE IF EXISTS {table_name}")
crsr.execute(
    f"CREATE TABLE {table_name} (id int primary key, txt varchar(50))"
)
crsr.execute(f"INSERT INTO {table_name} (id, txt) VALUES (1, 'original text')")

sql = f"""\
SET NOCOUNT ON;
BEGIN TRANSACTION;
UPDATE {table_name} SET txt = 'updated text' WHERE id = 1;
ROLLBACK;
BEGIN TRANSACTION;
INSERT INTO {table_name} (id, txt) VALUES (2, 'inserted text');
COMMIT;
"""
crsr.execute(sql)

print(crsr.execute(f"SELECT * FROM {table_name}").fetchall())
# [(1, 'original text'), (2, 'inserted text')]