4

I am connecting to a sybase ASE 15 database from Python 3.4 using pyodbc and executing a stored procedure.

All works as expected if I use native pyodbc:

import pd
import pyodbc

con = pyodbc.connect('DSN=dsn_name;UID=username;PWD=password', autocommit=True)
df = pd.read_sql("exec p_procecure @GroupName='GROUP'", con)

[Driver is Adaptive Server Enterprise].

I have to have autocommit=True and if I do no I get the following error:

DatabaseError: Execution failed on sql 'exec ....': ('ZZZZZ', "[ZZZZZ] [SAP][ASE ODBC Driver][Adaptive Server Enterprise]Stored procedure 'p_procedure' may be run only in unchained transaction mode. The 'SET CHAINED OFF' command will cause the current session to use unchained transaction mode.\n (7713) (SQLExecDirectW)")

I attempt to achieve the same using SQLAlchemy (1.0.9):

from sqlalchemy import create_engine, engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql import text

url = r'sybase+pyodbc://username:password@dsn'
engine = create_engine(url, echo=True)
sess = sessionmaker(bind=engine).Session()

df = pd.read_sql(text("exec p_procedure @GroupName='GROUP'"),conn.execution_options(autocommit=True))

The error message is the same despite the fact I have specified autocommit=True on the connection. (I have also tested this at the session level but should not be necessary and made no difference).

DBAPIError: (pyodbc.Error) ('ZZZZZ', "[ZZZZZ] [SAP][ASE ODBC Driver][Adaptive Server Enterprise]....

Can you see anything wrong here?

As always, any help would be much appreciated.

user2579685
  • 319
  • 9
  • 19

2 Answers2

3

I had some problems with autocommit option. The only thing that worked for me was to change this option to True after establishing connection.

ConnString = 'Driver=%SQL_DRIVER%;Server=%SQL_SERVER%;Uid=%SQL_LOGIN%;Pwd=%SQL_PASSWORD%;'

SQL_CONNECTION = pyodbc.connect(ConnString)
SQL_CONNECTION.autocommit = True
Wojciech Jakubas
  • 1,499
  • 1
  • 15
  • 22
2

Passing the autocommit=True argument as an item in the connect_args argument dictionary does work:

connect_args = {'autocommit': True}
create_engine(url, connect_args=connect_args)

connect_args – a dictionary of options which will be passed directly to the DBAPI’s connect() method as additional keyword arguments.

user2579685
  • 319
  • 9
  • 19