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.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
