Problem with autocommit in SQL Server

[Michael Kvyatkovskiy]

Hello.
The task is to execute stored procedure which writes data to database
in SQL Server 2008 using SQLAlchemy 0.7.1 in Python 2.7.2. SQLAlchemy
uses pyodbc 2.1.8. When trying to use SQLAlchemy, data is not written
to database:

from sqlalchemy.engine import create_engine
from sqlalchemy.orm import create_session
e = create_engine('mssql+pyodbc://user:password@localhost/db')
s = create_session(bind=e, autocommit=True)
s.execute("exec schema.ProcedureName 'param1', 'param2'")

SQLAlchemy log output:

INFO:sqlalchemy.engine.base.Engine:exec schema.ProcedureName 'param1',
'param2'
INFO:sqlalchemy.engine.base.Engine:()

Also I tried to manually begin transaction and commit after procedure
execution:

from sqlalchemy.engine import create_engine
from sqlalchemy.orm import create_session
e = create_engine('mssql+pyodbc://user:password@localhost/db')
s = create_session(bind=e, autocommit=True)
s.begin()
s.execute("exec schema.ProcedureName 'param1', 'param2'")
s.commit()

SQLAlchemy log:

INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)
INFO:sqlalchemy.engine.base.Engine:exec schema.ProcedureName 'param1',
'param2'
INFO:sqlalchemy.engine.base.Engine:()
INFO:sqlalchemy.engine.base.Engine:COMMIT

Commit seems to be done, but again no data written to database.

When using raw pyodbc with autocommit connection option, everything is
ok, data is successfully written to db:

import pyodbc
c = pyodbc.connect("DRIVER={SQL Server}; SERVER=localhost;
DATABASE=db; UID=user; PWD=password", autocommit=True)
c.execute("exec schema.ProcedureName 'param1', 'param2'")

What should I do solve this problem?

Thank you.

[Michael Bayer]

On Jul 8, 2011, at 3:58 AM, Michael Kvyatkovskiy wrote:

> Hello.
> The task is to execute stored procedure which writes data to database
> in SQL Server 2008 using SQLAlchemy 0.7.1 in Python 2.7.2. SQLAlchemy
> uses pyodbc 2.1.8. When trying to use SQLAlchemy, data is not written
> to database:
>
> from sqlalchemy.engine import create_engine
> from sqlalchemy.orm import create_session
> e = create_engine('mssql+pyodbc://user:password@localhost/db')
> s = create_session(bind=e, autocommit=True)
> s.execute("exec schema.ProcedureName 'param1', 'param2'")

The Session level autocommit is not the "autocommit" you're looking for here - that only refers to whether or not the Session keeps a transaction open and if flush() operations commit automatically.

SQLAlchemy's "statement autocommit" patterns look like:

engine.connect().execution_options(autocommit=True).execute("my statement")
engine.execute(text("my statement").execution_options(autocommit=True))

where the point is only to mark a statement as "this statement modifies data", and a COMMIT should be sent if no transaction is already in progress (note the DBAPI is always in a transaction unless you use that non-standard pyodbc "autocommit" flag you used below).

http://www.sqlalchemy.org/docs/core/connections.html?highlight=execution_options#sqlalchemy.engine.base.Connection.execution_options
http://www.sqlalchemy.org/docs/core/expression_api.html#sqlalchemy.sql.expression.Executable.execution_options
http://www.sqlalchemy.org/docs/core/connections.html?highlight=execution_options#understanding-autocommit

Also the docs on Session level autocommit, which I do not recommend using, have been updated:

http://www.sqlalchemy.org/docs/orm/session.html#autocommit-mode

> Also I tried to manually begin transaction and commit after procedure
> execution:
>
> from sqlalchemy.engine import create_engine
> from sqlalchemy.orm import create_session
> e = create_engine('mssql+pyodbc://user:password@localhost/db')
> s = create_session(bind=e, autocommit=True)
> s.begin()
> s.execute("exec schema.ProcedureName 'param1', 'param2'")
> s.commit()
>
> SQLAlchemy log:
>
> INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)
> INFO:sqlalchemy.engine.base.Engine:exec schema.ProcedureName 'param1',
> 'param2'
> INFO:sqlalchemy.engine.base.Engine:()
> INFO:sqlalchemy.engine.base.Engine:COMMIT
>
> Commit seems to be done, but again no data written to database.

This one I have no idea what the issue is. The Session is using a single connection from the time of begin() to the time of commit() - the sequence should match the DBAPI version I have below...

>
> When using raw pyodbc with autocommit connection option, everything is
> ok, data is successfully written to db:
>
> import pyodbc
> c = pyodbc.connect("DRIVER={SQL Server}; SERVER=localhost;
> DATABASE=db; UID=user; PWD=password", autocommit=True)
> c.execute("exec schema.ProcedureName 'param1', 'param2'")

You're doing something new here, which is using the pyodbc level "autocommit" feature (you can pass that to create_engine() if you'd like though its probably not a good idea in the bigger scheme of things). A test against traditional DBAPI behavior which SQLAlchemy uses would be:

c = pyodbc.connect(..., autocommit=False)
cursor = c.cursor()
cursor.execute("my procedure")
cursor.close()
c.commit()

The above sequence should be exactly what occurs with your Session .begin()/.execute()/.commit() sequence.