1

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

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
TheScottishKiwi
  • 23
  • 4
  • Please [edit] your question to include: (1) the client OS you are running, (2) the Driver= specified in your DSN, (3) the versions of pyodbc and SQLAlchemy you are using. – Gord Thompson Dec 01 '22 at 13:14
  • and what ODBC driver is your DSN using? – Gord Thompson Dec 01 '22 at 13:40
  • Try using `mssql+pyodbc://username:password@dsn_name?autocommit=True` – Gord Thompson Dec 01 '22 at 15:04
  • @GordThompson I tried `mssql+pyodbc://username:password@dsn_name?autocommit=True` and got a different error, so progress I suppose. I now get `DBAPIError: (pyodbc.Error) ('HY000', "[HY000] [PIOLEDB] Function name 'schema_name' is invalid. (DB_E_ERRORSINCOMMAND) (80040e14) (0) (SQLExecDirectW)") [SQL: SELECT schema_name()]` – TheScottishKiwi Dec 02 '22 at 09:43

1 Answers1

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.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418