1

Could anyone share a functional connection method to Wonderware's Historian using python3 on OSX (or linux)?

Historian is apparently a Microsoft SQL Server OLE DB (see pg102 of http://www.logic-control.com/datasheets/1/Historian/HistorianConcepts.pdf).

Another SO post suggests that the only python library available capable of connecting to an OLE DB is 'adodbapi' (Connecting to Microsoft SQL server using Python)

an attempt in code (using default RO credentials):

import adodbapi
ServerName = "ServerName"
MSQLDatabase = "Runtime"
username = "aaUser"
password = "pwUser"
conn = adodbapi.connect("PROVIDER=INSQL;Data Source={0};Database={1};trusted_connection=yes;UID={2};PWD{3};".format(ServerName,MSQLDatabase,username,password))

That gives an error:

adodbapi.apibase.OperationalError: (InterfaceError("Windows COM Error: Dispatch('ADODB.Connection') failed.",)...

The error is probably due to the absence and unavailability of the pywin32 package, which is apparently Windows only (Pywin32 (com objects) on Mac)

Tips appreciated. I highly suspect that the Microsoft vs Mac/Linux worlds just can't be bridged in this situation.

Community
  • 1
  • 1
Ziggy Eunicien
  • 2,858
  • 1
  • 23
  • 28

2 Answers2

4

You should be able to access an MS SQL Server database (e.g. Wonderware Historian database) with Python.

Things to check:

  • For ODBC, "Trusted Connection" setting should be "No". Trusted connection means that it tries to use Windows Authentication to log in. You want to use a username and password instead. I think for OLE DB you set "Integrated Security = SSPI" instead.
  • Connection String (Username, Server Hostname/IP, Database name, Correct Port, Syntax)
  • Port (make sure you use the correct port - may be a non-standard port)
  • Firewalls - make sure that any firewalls are set up to allow access
  • If you're using the hostname, make sure DNS is working (e.g. can you ping the server?)
  • You may need to install an ODBC driver for Linux and pyodbc. ODBC is a more open standard. As you've pointed out, OLEDB is COM-based (e.g. Windows-based) so I'm not sure if it would be compatible.
Taraz
  • 1,242
  • 13
  • 13
1

I am able to access a Wonderware server using Python3 both through sqlalchemy and pyodbc on Linux and Windows - I don't have a Mac so you're on your own there. I've read that there are other drivers available, but I don't have any experience with them. If you have any suggestions here I'd be glad to hear them.

This is how I modularize the functionality of pyodbc. Essentially, I've defined a function in our code that sets up the sql engine conncetion:

def get_conn():
    conn_pyodbc = pyodbc.connect(DSN=<myDSN>, UID=<user>, PWD=<pass>)
    return conn_pyodbc

And I use the connection as such:

def executeQuery(sql_query):
    with get_conn() as conn:
        df = pd.read_sql(sql_query, conn)

Using the context manager just seems like an easier way of handling opening and closing database connections.

As far as setting up the DSN, I needed to install the Microsoft ODBC driver, which was easy enough to do by following some links I found online. After a successful installation I edited the /etc/odbc.init and /etc/odbcinst.ini files manually such that they look like this now:

$ cat /etc/odbc.ini 
[myDSN]
Driver=ODBC Driver 13 for SQL Server
Description=Awesome server
Trace=No
Server=<serverIP>

and

$ cat odbcinst.ini 
[ODBC Driver 13 for SQL Server]
Description=Microsoft ODBC Driver 13 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.9.1
UsageCount=1

In my experience, you can use 'Trusted Connection', but that means that your computer will try to connect to the server using it's Windows credentials. These work if you are logged into a Windows machine that has access to the data in question. Running on Linux is a different story, so I keep to the user/password combo.

Anymore questions feel free to ask.

cbcoutinho
  • 634
  • 1
  • 12
  • 28