"'SQL_AUTOCOMMIT_OFF' … is not implemented" error with SQLAlchemy engine

Question

I have been connecting to a database using pyodbc:

DNS = 'PIODBC'
pi_connection = pyodbc.connect("DSN=" + DSN + ";UID=" + uid + ";PWD=" +pw, autocommit = True)
pd.read_sql_query(qry, pi_connection)

This uses a PI ODBC Driver apparently, which could well be a proprietary thing which lets me connect to and OSIsoft PI database. This has been working fine for me.

Recently I started getting the error message:

UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.

I still get what I want but I want to do things properly so I've considered using SQLAlchemy. I don't want to just suppress the error. The code I've used is:

from sqlalchemy import create_engine

engine_str = "mssql+pyodbc://"+uid+":"+pw+"@"+DSN
engine = create_engine(engine_str)

with engine.connect() as con:
    rs = con.execute(qry)

Unfortunately that throws up an actual error:

DBAPIError: (pyodbc.Error) ('HYC00', "[HYC00] The 'SQL_AUTOCOMMIT_OFF' for the 'SQL_ATTR_AUTOCOMMIT' is not implemented. (0) (SQLSetConnectAttr(SQL_ATTR_AUTOCOMMIT))")
(Background on this error at: https://sqlalche.me/e/14/dbapi)

There are a number of related questions that I have seen: here, here, here, here, and here but I am not getting it. I feel like I've tried a million variations of my code in line with what is being said in the answers to these questions but I haven't managed to move on from both the errors. I'm just not understanding it all I suppose.

I'm using Windows 10 (64 bit) and pyodbc 4.0.31 and SQLAlchemy 1.4.44

Answer 1

The OSIsoft ODBC driver for PI differs sufficiently from the MS SQL Server implementations that using mssql+pyodbc://… with SQLAlchemy is not going to work. For now you can continue using a plain DBAPI (pyodbc) connection for .read_sql_query() and just ignore the warnings from pandas.

For full support with pandas — e.g., for .to_sql() — you will need to find (or create) a proper third-party SQLAlchemy dialect for OSIsoft PI. The technical support group at OSIsoft may be able to help you with that.