Error 1671 when returning data from python

[JBrown0110110]

Greetings,

I followed this guide here:

https://knowledge.ni.com/KnowledgeArticleDetails?id=kA03q000000oyaHCAQ&l=en-US

And I was attempting to send a few parameters to python from Labview, run a query using pyodbc, and then return the results into an array.

Here is my Labview program:

As the guide shows I created a cluster of my datatytpes and put them into a 1D array. 

I tried to remove the strings and just do simple integer returns, but that yields the same error. I'm not sure why Labview seems to be erroring for no reason on this data return. Any advice or critique would be greatly appreciated.

Here is what I am returning from Python, it is a list of tuples similar to the guide above:

[('default', 4128, 'Record Length Ch 1', 128, 'samples', 'RW', 'init', 'Initial entry'), ('default', 4136, 'Input Dynamic Range Ch 1', 0, 'Vpp', 'RW', 'init', 'Initial entry'), ('default', 4384, 'Record Length Ch 2', 128, 'samples', 'RW', 'init', 'Initial entry'), ('default', 4392, 'Input Dynamic Range Ch 2', 0, 'Vpp', 'RW', 'init', 'Initial entry'), ('default', 4640, 'Record Length Ch 3', 128, 'samples', 'RW', 'init', 'Initial entry'), ('default', 4648, 'Input Dynamic Range Ch 3', 0, 'Vpp', 'RW', 'init', 'Initial entry'), ('default', 4896, 'Record Length Ch 4', 128, 'samples', 'RW', 'init', 'Initial entry'), ('default', 4904, 'Input Dynamic Range Ch 4', 0, 'Vpp', 'RW', 'init', 'Initial entry'), ('default', 5152, 'Record Length Ch 5', 128, 'samples', 'RW', 'init', 'Initial entry'), ('default', 5160, 'Input Dynamic Range Ch 5', 0, 'Vpp', 'RW', 'init', 'Initial entry'), ('default', 5408, 'Record Length Ch 6', 128, 'samples', 'RW', 'init', 'Initial entry'), ('default', 5416, 'Input Dynamic Range Ch 6', 0, 'Vpp', 'RW', 'init', 'Initial entry'), ('default', 5664, 'Record Length Ch 7', 128, 'samples', 'RW', 'init', 'Initial entry'), ('default', 5672, 'Input Dynamic Range Ch 7', 0, 'Vpp', 'RW', 'init', 'Initial entry'), ('default', 5920, 'Record Length Ch 8', 128, 'samples', 'RW', 'init', 'Initial entry'), ('default', 5928, 'Input Dynamic Range Ch 8', 0, 'Vpp', 'RW', 'init', 'Initial entry')]

Here is a snippet of my Python:

#This is the main script of the GPAS database connectivity

#import modules

import pyodbc

from datetime import datetime

#set the connection info

conn_str = "DSN=GPAS"

#connect to database and create cursor

cnxn = pyodbc.connect(conn_str)

crsr = cnxn.cursor()

####################################################################################################################

####################################################################################################################

def CAEN_DT5730_read_profile(Profile_Name, Mode, Group_Name😞

    #build the query

    query = """

            WITH RankedEntries AS (

                SELECT

                    *,

                    ROW_NUMBER() OVER (

                        PARTITION BY Register_Address

                        ORDER BY Timestamp DESC

                    ) AS RowNum

                FROM

                    GP_CAEN_DT5730_Profile

                WHERE

                    Profile_Name LIKE ?

                    AND Mode LIKE ?

                    AND Group_Name LIKE ?

            )

            SELECT

                Profile_Name,

                Register_Address,

                Register_Name,

                Register_Value,

                Units,

                Mode,

                Group_Name,

                Change_Log

            FROM

                RankedEntries

            WHERE

                RowNum = 1

        """

    #execute the query

    try:

        crsr.execute(query, Profile_Name, Mode, Group_Name)

    except Exception as e:

        print(f"Error executing query: {e}")

        return []

    # Fetch all

    return crsr.fetchall()

[Jay14159265]

That example looks dubious, did you try implementing the example to see if it actually works. 

[JBrown0110110]

Hello thank you for your response.

I did check it and the code in the example does indeed work as listed.

To add more information, upon further testing I've found that if I have python print the return from the database and then copy that and paste it as a constant and send that to labview, it works. But if I return that from the function, it dies. Something in how pyodbc handles the data labview doesn't like.

[JBrown0110110 · Accepted solution]

If you're seeing this and trying to figure out how to pass pyodbc results to Labview, here was what I ended up doing. I noticed that if I assigned the printed out version of the list of tuples and assigned it to a variable, and then passed that to Labview, it worked flawless. My only conclusion is that the fetchall is more than a list of tuples as it claims it is and appears to be, thus Labview can not properly process the incoming data. YOU MUST REBUILD AND SANITIZE THE ENTIRE LIST. Here is my example code (after query execution):

[Jay14159265]

---

@JBrown0110110 wrote:

Hello thank you for your response.

I did check it and the code in the example does indeed work as listed.

To add more information, upon further testing I've found that if I have python print the return from the database and then copy that and paste it as a constant and send that to labview, it works. But if I return that from the function, it dies. Something in how pyodbc handles the data labview doesn't like.

---

Well it's good that you got it working. For the example code, I find this line below interesting as it does not actually reference the TestList object, it just has the string "TestList". How is that supposed to work? Maybe the LabVIEW Python API is doing some magic there. Any way, if it works, it works and that is that. 

TestTuple = (3, 5, "TestList")

[JBrown0110110]

It's just using the string "testlist" as an example of the string. In that example it's building a touple, then puts two of those into a list called x, then returns x. I did something similar by dumping:

[('default', 4128, 'Record Length Ch 1', 128, 'samples', 'RW', 'init', 'Initial entry'), ('default', 4136, 'Input Dynamic Range Ch 1', 0, 'Vpp', 'RW', 'init', 'Initial entry'), ('default', 4384, 'Record Length Ch 2', 128, 'samples', 'RW', 'init', 'Initial entry'), ('default', 4392, 'Input Dynamic Range Ch 2', 0, 'Vpp', 'RW', 'init', 'Initial entry'), ('default', 4640, 'Record Length Ch 3', 128, 'samples', 'RW', 'init', 'Initial entry'), ('default', 4648, 'Input Dynamic Range Ch 3', 0, 'Vpp', 'RW', 'init', 'Initial entry'), ('default', 4896, 'Record Length Ch 4', 128, 'samples', 'RW', 'init', 'Initial entry'), ('default', 4904, 'Input Dynamic Range Ch 4', 0, 'Vpp', 'RW', 'init', 'Initial entry'), ('default', 5152, 'Record Length Ch 5', 128, 'samples', 'RW', 'init', 'Initial entry'), ('default', 5160, 'Input Dynamic Range Ch 5', 0, 'Vpp', 'RW', 'init', 'Initial entry'), ('default', 5408, 'Record Length Ch 6', 128, 'samples', 'RW', 'init', 'Initial entry'), ('default', 5416, 'Input Dynamic Range Ch 6', 0, 'Vpp', 'RW', 'init', 'Initial entry'), ('default', 5664, 'Record Length Ch 7', 128, 'samples', 'RW', 'init', 'Initial entry'), ('default', 5672, 'Input Dynamic Range Ch 7', 0, 'Vpp', 'RW', 'init', 'Initial entry'), ('default', 5920, 'Record Length Ch 8', 128, 'samples', 'RW', 'init', 'Initial entry'), ('default', 5928, 'Input Dynamic Range Ch 8', 0, 'Vpp', 'RW', 'init', 'Initial entry')]

into a variable I called Test and then passing that to Labview. Surprisingly, that worked. Which is when I was able to determine it was an issue with pyodbc's structure of a list of tuples and not Labview or Python specifically having the issues.