OSI PI is a historian database. I had a task to connect a python application to this database. Unfortunately, the vendor doesn’t support Python (odd, they are named “PI”, but I digress). Additionally, the drivers provided by the company initially didn’t work. This post details how I was able to finally connect python to OSI PI. It may not be the most elegant, but it functions for the purposes of my applicationInitially I was attempting to connect using the pyodbc module. Unfortunately, OSI PI would return a message like this:
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)")
The vendor response was less than helpful regarding using ODBC. They did, however, mention that using OLEDB instead may prove more fruitful. The code below is how I got connected using the vendor provided OLDEB driver. The downside is that I also had to do this all through COM objects using win32com. I’m not knocking the module, because it is extremely useful and I’ve done some great things with it.
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