How can I connect Python 2.6 to OSI PI?

Question

I am using pyodbc to manage my database connections. I am attempting to connect to an OSI PI database and receive this error:

pyodbc.Error: ('IM002', "[IM002] [OSI][PI ODBC][PI]PI-API Error <pilg_getdefserverinfo> 0 (0) (SQLDriverConnectW); [01000] [Microsoft][ODBC Driver Manager] The driver doesn't support the version of ODBC behavior that the application requested (see SQLSetEnvAttr). (0)")

After talking with the vendor, I got this response: Looks like pyodbc is written against ODBC 3.x. The OSI PI ODBC driver is using ODBC 2.0. The python ODBC driver manager will convert most ODBC 3 calls on the fly to ODBC 2 ones. Anything added to 3, however, will obviously fail. You would need to find some way to make sure that your only using 2.0 compliant ODBC calls. Currently their is not a PI ODBC driver that is compliant with ODBC 3.0.

My code is fairly simple as I'm just attempting to connect at this point:

import pyodbc
constr = 'DRIVER={PI-ODBC};SERVER=myserver;UID=MY_UID'
pyodbc.pooling=False
conn = pyodbc.connect(constr)           # Error at this line
conn.close()

Has anyone connected python to OSI PI? If so, how did you do so? If not and you still used data in the OSI database, how did you end up accessing it?

I had issues using the `R` `RODBC` package to connect to PIODBC. The symptom was that all queries were returning zero rows. The solution was to set `RODBC` to fetch a single row at a time (`rows_at_time = 1`) and also (`believeNRows = FALSE`) because the PI ODBC driver was messing up the row count. http://stackoverflow.com/q/7425100/176995 — Tommy O'Dell, Jun 12 '12 at 00:52

score 4 · Accepted Answer · answered Feb 01 '12 at 17:46

I figured out how to do this. I had to change from using pyodbc though. Instead, I'm doing this with win32com.

Example

from win32com.client import Dispatch

oConn = Dispatch('ADODB.Connection')
oRS = Dispatch('ADODB.RecordSet')

oConn.ConnectionString = "Provider=PIOLEDB;Data Source=<server>;User ID=<username>;database=<database>;Password=<password>"
oConn.Open()

if oConn.State == 0:
    print "We've connected to the database."
    db_cmd = """SELECT tag FROM pipoint WHERE tag LIKE 'TAG0001%'"""
    oRS.ActiveConnection = oConn
    oRS.Open(db_cmd)
    while not oRS.EOF:
        #print oRS.Fields.Item("tag").Value   # Ability to print by a field name
        print oRS.Fields.Item(0).Value        # Ability to print by a field location
        oRS.MoveNext()
    oRS.Close()
    oRS = None
else:
    print "Not connected"

if oConn.State == 0: 
    oConn.Close()
oConn = None

Notes:

This requires the PIOLEDB driver provided by OSISoft is installed on the machine that runs this code.
Performance doesn't seem horrible with this method. I was able to pull back several hundred thousand records with some of my other queries and it returned in an acceptable amount of time

Just following up on this after using it for a couple months. This is still the only way I've found to do this with python, but it seems to be very slow when I need to run a large number of queries. I suspect it is because I have to open/close the database connection for each query, but OSI PI/ADODB complains if I do not. Performance has not reached a point where I am forced to rewrite this yet. If/when I do I will follow up again. In the meantime others using this solution should be aware that it is slow when running many queries. — Andy, Apr 15 '12 at 01:24
I just wanted to note that with PI ODBC 2015 the pyodbc library works and is able to make connections to the DAS server. — erik-sn, Apr 22 '16 at 18:24

score -2 · Answer 2 · answered Jan 17 '12 at 16:38

-2

Try to use this http://pypi.python.org/pypi/ceODBC/2.0.1

answered Jan 17 '12 at 16:38

Denis

7,127
8
37
58

How can I connect Python 2.6 to OSI PI?

2 Answers2