pythonsqlsql-serverwonderware

How to query Wonderware live values with python?


I'd like to query live tag value from Wonderware historian with python. The following sql query works inside SQL server management studio and returns the live value of the tag:

USE Runtime
DECLARE @TempTable TABLE(Seq INT IDENTITY, tempTagName NVARCHAR(256)) 
INSERT @TempTable(tempTagName) VALUES ('TAG_A')
SELECT v_Live.TagName, DateTime, vValue
 FROM v_Live
LEFT JOIN @TempTable ON TagName = tempTagName
 WHERE v_Live.TagName IN ('TAG_A')
ORDER BY Seq

However, I get the error Previous SQL was not a query when I pass the query string above to cur.execute(). I am using pyodbc to connect to the SQL server.

with open_db_connection(server, database) as conn:
    cur = conn.cursor()
    query_string = textwrap.dedent("""USE Runtime
    DECLARE @TempTable TABLE(Seq INT IDENTITY, tempTagName NVARCHAR(256)) 
    INSERT @TempTable(tempTagName) VALUES ('TAG_A')
    SELECT v_Live.TagName, DateTime, vValue
    FROM v_Live
    LEFT JOIN @TempTable ON TagName = tempTagName
    WHERE v_Live.TagName IN ('TAG_A')
    ORDER BY Seq
    """)
    cur.execute(query_string)
    row = cur.fetchone()
    print(row[1])

Anyone has an idea why I get this error and how can I solve it?


Solution

  • I'm going to leave an answer based on the comment I left on your original post, but I recommend making these changes:

    1a: Do away with the temp table. It isn't doing anything for you besides generating a sequence ID. As your question stands right now, I don't see what benefit that has while generating more complexity:

    with open_db_connection(server, database) as conn:  
      cur = conn.cursor()  
      query_string = """  
        SELECT TagName, DateTime, vValue 
        FROM Runtime..v_Live  
        WHERE TagName IN ('TAG_A')   
      """  
      cur.execute(query_string)  
      row = cur.fetchone()  
      print(row[1])  
    

    1b: Assuming you're going to keep that temp table, here's syntax using INNER REMOTE JOIN:

    with open_db_connection(server, database) as conn:
      cur = conn.cursor()
      query_string = """
        DECLARE @TempTable TABLE (
          Seq INT IDENTITY, 
          tempTagName NVARCHAR(256)
        );
        INSERT INTO @TempTable (tempTagName) VALUES ('TAG_A');
    
        SELECT v_Live.TagName, DateTime, vValue
        FROM @TempTable
        INNER REMOTE JOIN v_Live ON TagName = tempTagName
        ORDER BY Seq;
      """
      cur.execute(query_string)
      row = cur.fetchone()
      print(row[1])
    

    2: Since I'm not a python dev, I was asking about using Stored Procs to achieve your ends because you could just wrap your TSQL in a proc to do the work:

    CREATE PROC QueryLiveData
      @Tags nvarchar(max)
    AS
      SET NOCOUNT ON
    
      DECLARE @TempTable TABLE (
        Seq INT IDENTITY, 
        tempTagName NVARCHAR(256)
      );
    
      INSERT INTO @TempTable (tempTagName) VALUES (@Tags)
    
      SELECT v_Live.TagName, DateTime, vValue
      FROM @TempTable
      INNER REMOTE JOIN v_Live ON TagName = tempTagName
      ORDER BY Seq
    GO
    

    ...or alternatively if you need to pass multiple tags you can comma separate them like TAG_A,TAG_B,TAG_C:

    CREATE PROC QueryLiveData
      @Tags nvarchar(max)
    AS
      SET NOCOUNT ON
    
      DECLARE @TempTable TABLE (
        Seq INT IDENTITY, 
        tempTagName NVARCHAR(256)
      );
    
      --Splits the @Tags on comma and inserts individual values to @TempTable
      WHILE LEN(@Tags) > 0 BEGIN
        IF CHARINDEX(',', @Tags) > 0 BEGIN        
          INSERT INTO @TempTable 
            SELECT LEFT(@Tags, CHARINDEX(',', @Tags ) - 1)
          SET @Tags = RIGHT(@Tags, LEN(@Tags) - CHARINDEX(',', @Tags))
        END ELSE BEGIN            
          INSERT INTO @TempTable VALUES (@Tags)
          SET @Tags = ''
        END
      END
    
      SELECT v_Live.TagName, DateTime, vValue
      FROM @TempTable
      INNER REMOTE JOIN v_Live ON TagName = tempTagName
      ORDER BY Seq
    GO
    

    Then for your python (I'm assuming you'd be able to clean up the query and use proper params in Python instead of a string passed like I have):

    with open_db_connection(server, database) as conn:
      cur = conn.cursor()
      query_string = """
        EXEC RunTime..QueryLiveData 'TAG_A,TAG_B,TAG_C'
      """
      cur.execute(query_string)
      row = cur.fetchone()
      print(row[1])