pyodbc autocommit does not appear to work with sybase and sqlalchemy
Question: 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.


Answer: 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.

