Python OLEDB query

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'])