Firstly thanks to http://www.mayukhbose.com/python/ado/ado-connection.php
Here is an example of connecting to OSI PI piarchive database using OLEDB driver. It would be the similar for other OLEDB data sources as well.
#need to install pywin32 which seems to call windows ADO library or something like that
#conda install pywin32
from win32com.client import Dispatch
import pandas as pd
conn = Dispatch('ADODB.Connection')
#standard oledb connection string, may add uid and pwd as well
conn.ConnectionString = "Provider=PIOLEDB.1;Data Source=ServerURL;database=piarchive"
conn.Open()
sql = '''
SELECT tag, time, value
FROM [piarchive]..[picomp2]
WHERE tag = 'sinusoid'
AND time BETWEEN '*-1h' AND '*'
'''
(result, status) = conn.Execute(sql)
#iteratate through the result object to get all rows
result_list = []
while not result.EOF:
result_list.append([str(result.Fields("tag")), str(result.Fields("time")), str(result.Fields("value"))])
result.MoveNext()
conn.Close()
#save it in pandas for consumption later
df = pd.DataFrame(result_list, columns= ['tag', 'time', 'value'])