How to query Wonderware live values with python?

Question

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?

Answer 1

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

Answer 2

I think pyodbc have problem executing the multi-statement SQL script, lets try to split them into multiple statement then executing them separately.

edit: the scope of the table variable @TempTable is limited to the batch it was declared in, lets use global temp table, dont forget to drop the global temp table after

edit2: in this case we can concatenate all of your SQL commands into a single string then execute it, that might work.

with open_db_connection(server, database) as conn:
    cur = conn.cursor()
    query_string = """
        USE Runtime;
        DECLARE @TempTable TABLE(Seq INT IDENTITY, tempTagName NVARCHAR(256));
        INSERT INTO @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])