SNOW-2095102: Enable to set autocommit when creating connection

[janb-frontify]

What is the current behavior?

I'm using:

SQLAlchemy==2.0.40
snowflake-connector-python==3.15.0
snowflake-sqlalchemy==1.7.3

Currently when opening connection, no matter what, it runs:

ALTER SESSION SET autocommit=False

I have tried:

engine.connect().execution_options(autocommit=False)

And I also have it set on Engine level:
connect_args={"sslcompression": 0, "autocommit": True}

How can I change this, is this even possible? It seems like ALTER SESSION SET autocommit=False is currently run always, and then I have to explicitely change it.

What is the desired behavior?

Be able to set autocommit=True when creating connection so it does not run ALTER SESSION SET autocommit=False.

How would this improve snowflake-connector-python?

Makes it run less statements.

References and other background

No response

[sfc-gh-sghosh]

Hello @janb-frontify ,

Thanks for raising the issue, we are checking, will update.

Regards,
Sujan

[sfc-gh-sghosh]

Hello @janb-frontify ,

With the latest sqlalchemy 2.0.40, the autoCommit feature has been deprecated, so by default its false now.
https://docs.sqlalchemy.org/en/14/core/connections.html#library-level-e-g-emulated-autocommit

So, you have to explicitly use BEGIN and COMMIT to commit the transactions as below

`from snowflake.sqlalchemy import URL
from sqlalchemy import create_engine, text

engine = create_engine(URL(
account = 'account',
user = 'sujan',
password = 'xxxx,
database = 'DB',
schema = 'TEST',
warehouse = 'WH',
role='admin',
))

connection = engine.connect()

#with engine.connect().execution_options(autocommit=True) as connection:
try:
connection.execute(text("BEGIN"))
connection.execute(text("INSERT INTO MYCSVTABLE1 VALUES (88888, 'X', 'YZ')"))
connection.execute(text("INSERT INTO MYCSVTABLE1 VALUES (99999, 'Y', 'ZA')"))
connection.execute(text("COMMIT"))
print("Inserted rows")
except Exception as e:
connection.execute(text("ROLLBACK"))
print(f"An error occurred: {e}")
finally:
connection.close()

engine.dispose()`