4 Replies Latest reply on Apr 17, 2015 8:34 PM by intercap

    Working with Pi Data from Python - Python version of Pi SDK?

    intercap

      Has anyone worked with Pi data from within python? If so, what API did you use and how well did it perform? We are integrating a python library, and expect to be accessing fairly large amounts of data, so we would prefer not to have the overhead associated with HTTP/S.

        • Re: Working with Pi Data from Python - Python version of Pi SDK?
          gavin.strack

          Hi Keith,

           

          I haven't used Python however I have started playing with PI data using JavaScript/JQuery.  The PI Web API is the interface of choice for this, and I would assume it would also be suitable for Python.

           

          There are a few posts that touch on using Python, such as AF SDK and Python (not IronPython) which may assist.

           

          Hope this helps!

           

          Gav

          • Re: Working with Pi Data from Python - Python version of Pi SDK?
            bshang

            Hi Keith,

             

            Good question. I'm also interested in what the PI Square community has discovered and their interest in integrating PI with Python.

             

            There are a few existing options for working with Python and PI. However, no Python module currently exists for implementing PI/AF SDK, so the approaches will appear more as "workarounds". First, I have a few questions:

             

            1) Is your code base primarily in Python (specifically, its C implementation) or .NET?

            2) What distribution of Python are you using? (e.g. python.org, Anaconda, Enthought, IronPython, etc.)

            3) Which Python library(s) are you integrating with? Will you be using any of the scientific stack (e.g. numpy, scipy, pandas, numba, scikit-learn)?

            4) What is the general use case?

             

            There are at least two main options:

            1) CPython (the "default" implementation of Python in C) - This is what is offered by python.org, Anaconda, and a few other popular distributions. For COM integration with CPython, one option is the pywin32 extension. For .NET integration with CPython, one option is Python for .NET

            2) IronPython (the .NET implementation of the Python language). I believe Microsoft now manages this distribution and can be accessed in Visual Studio 2013 (search for Python Tools for VS2013).

             

            The two approaches above are a bit complimentary.

             

            CPython/pywin32/Python.NETPython.NET is an integration of the C Python engine with the .NET runtime. It does not treat Python as a first-class CLR language as IronPython does. It allows you to use CLR classes inside existing Python code and use its native C-based extensions (C and FORTRAN numerical libraries) for higher execution speeds. So numpy/scipy integration will likely fare better with CPython (although I'm not sure if IronPython has extended support for these libraries). Python.NET "brings .NET to Python". Another option with CPython is pywin32, which integrates CPython with Windows COM.

             

            IronPython - This is a managed CLR implementation of Python, so it is more for incorporating features of Python into the .NET language stack. It "brings Python to .NET".

             

            Therefore, questions above are relevant to which implementation to choose.

             

            I will post some examples below. However, I think the answers to the above questions will help us further identify the best options to pursue.

             

             

             

            -----------

             

            Examples:

             

            CPython access to PI SDK via pywin32

             

            One option is to use pywin32 to access PI SDK objects in CPython. An example code is below.

            from win32com.client.dynamic import Dispatch
            import time
            
            PL = Dispatch("PISDK.PISDK")
            print("PISDK Version:", PL.PISDKVersion)
            print("Known Server List")
            print("-----------------")
            
            for server in PL.Servers:    
                if server.Name == PL.Servers.DefaultServer.Name:    
                    print("{0}\t:: DEFAULT::".format(server.Name))
                else:    
                    print(server.Name)
                tag = server.PIPoints("sinusoid")
                # or tag = server.PIPoints.Item("sinusoid")
                print("\t", tag.PointAttributes("tag").Value)    
                print("\t", tag.Data.Snapshot.Value)
                timestamp = time.localtime(tag.Data.Snapshot.TimeStamp.UTCseconds)
                print("\t", time.asctime(timestamp))
            
            
            

             

            Using the COM extension for CPython, we can also use PI OLEDB (COM technology)

            import win32com.client    
            
            conn = win32com.client.Dispatch(r'ADODB.Connection')    
            DSN = "Provider=PIOLEDB.1;Data Source=Jerome-PI1;Integrated Security=SSPI;Persist Security Info=False"
            conn.Open(DSN)    
            
            recordset = win32com.client.Dispatch(r'ADODB.Recordset')    
            recordset.Cursorlocation = 3
            recordset.Open("select tag, value from pisnapshot", conn)
            
            if recordset.RecordCount > 0:    
                print("You have a total of {0} tags, and these are their values\n".format( recordset.RecordCount ))   
                print("Tag, Snapshot Value")
                print("---------------------\n")
                while not recordset.EOF:
                   source = {field.Name : field.value for field in recordset.Fields}
                   print("{tag}, {value}".format(**source))  
                   recordset.MoveNext()
            else:    
                print("There are no tags")   
            conn.Close()
            
            
            

             

            Note AF SDK integration with CPython will be trickier, as it is a .NET service, so the COM extension pywin32 will not work. An option here is to try Python for .NET 

             

            IronPython access to AF SDK

             

            An example with AF SDK is below.

            # IronPyton
            import clr
            clr.AddReferenceByPartialName('osisoft.afsdk')
            from OSIsoft import AF
            import System
            # Get the first template element in the default database of the default pi server
            db = AF.PISystems().DefaultPISystem.Databases.DefaultDatabase
            elementTemplate = db.ElementTemplates[0]
            
            # You can off course specify specific elements
            # db = AF.PISystems()["luis-pi"].Databases["MetersToDistribution_000"]
            # elementTemplate = db.ElementTemplates["ScaleMeter010"]
            
            query = {
            "database": db,
            "searchRoot": None,
            "nameFilter": "*",
            "elemCategory": None,
            "elemTemplate": elementTemplate,
            "elemType": AF.Asset.AFElementType.Any,
            "attrNameFilter": "*",
            "attrCategory": None,
            "attrType": System.TypeCode.Empty,
            "searchFullHierarchy": True,
            "sortField": AF.AFSortField.Name,
            "sortOrder": AF.AFSortOrder.Ascending,
            "maxCount": 1000,
            }
            foundAttributes = AF.Asset.AFAttribute.FindElementAttributes(**query)
            
            for attribute in foundAttributes:
                data = {"element": attribute.GetPath(attribute.Element.Database)[:40],
                    "value":attribute.GetValue().Value,
            "timestamp":attribute.GetValue().Timestamp}
                print("{element} has value of {value} @ {timestamp}".format(**data))
            
            
            

             

             

             

             

             

             

             

            hackathon_sig.png

            Click here to register for the TechCon Programming hackathon on April 29, 2015!

            1 of 1 people found this helpful
              • Re: Working with Pi Data from Python - Python version of Pi SDK?
                ttelfer

                This is a the pure python implementation using the AF server. Much of the data regarding the SQL query string information can be found within the OLEDB Enterprise documentation. The following worked successfully in python 2.7.x (and I would imagine also in python 3.4.x) as follows:

                 

                from __future__ import print_function

                from datetime import datetime, timedelta

                from sys import exit, path

                import adodbapi


                def operational_data():

                  days_back = 7

                 

                  # Connect to the ODI PI server.
                  try:

                      conn_str = "Provider=PIOLEDBENT.1;Integrated Security=SSPI;" \

                      "Persist Security Info=False;Initial Catalog=ODI-AF;" \

                      "Data Source=DEV-AF"
                      db = adodbapi.connect(conn_str, timeout=10000)

                      cur = db.cursor()

                  except:

                       print("Failed to connect to database")

                 

                  operational_data = retrieve_operational_data(cur, days_back)

                 

                def retrieve_operational_data(cur, days_back):

                  # Composition of string can be ascertained from the OLEDB Enterprise manual.

                  sql = """SELECT eh.Path + eh.Name + ea.Path + ea.Name Path, ar.Time, ar.Value
                  FROM [ODI-AF].[Asset].[ElementHierarchy] eh
                  INNER JOIN [ODI-AF].[Asset].[ElementAttribute] ea on eh.ElementID=ea.ElementID
                  INNER JOIN [ODI-AF].Data.Archive ar ON ar.ElementAttributeID = ea.ID
                  WHERE eh.Path LIKE N'\%'
                  and ea.Name IN (N'%')
                  and ar.Time > N'*-{1}d'
                  OPTION (FORCE ORDER)"""

                  try:

                      cur.execute(sql.format(sql, days_back))

                      operational_data = cur.fetchall()

                      return operational_data

                  except:

                      error_msg = "Failed to retrieve information from the AF server."
                      print(error_msg)

                 

                  return []

                 

                  return operational_data

                 

                if __name__ == "__main__":

                  try:

                      operational()

                  except KeyboardInterrupt:

                      exception_message = "Ctrl-C pressed. Stopping..."
                     print(exception_message)

                1 of 1 people found this helpful