Unable to retrieve results for field [{0}] TabPy error while using a python sql function in the flow

0
votes

I am trying to run SQL queries within a defined function. I want a pandas data frame as an output but I get a list though the data seems correct. Here's my code:

    import pandas as pd
    from pandasql import sqldf
    import pandasql as psql

    cars = {'Brand': ['Honda Civic','Toyota Corolla','Ford Focus','Audi A4'],
                'Price': [22000,25000,27000,35000]}
    df = pd.DataFrame(cars)
    
    
    def test_query(df):
        df = pd.DataFrame(df)
        q = """select * from df"""
    
        df2 = psql.sqldf(q, locals())
        return df2

   test_query(df)

This returns a fine pandas data frame that I actually need.

Now where the problem persists is that when I run this function on my data in Tableau Prep Builder the error I am getting is

Unable to retrieve results for field [{0}]

I looked up for this error and found that "you need to use a python command to return something where something is a list containing the appropriate return type. Otherwise, the values may exist in python but Tableau can't see them"

thus I used

 ReturnValues = []
 ReturnValues.append(df2)

still, I get the same error as Unable to retrieve results for field [{0}]

What could be the reason here.

2
pythonpandastableau-apitabpy
You can either take the first element of the return value dfs[0] or return df2 directly in your test_query function – ScootCork
I thought unlisting would do here but then It did not. So I have edited the question with a more granular level issue – Pankaj Kaundal

2 Answers

1
votes

It is returning list of dataframes, So just pick the first element of the list, you're done.

print(test_query(df)[0])
1
votes

There are 3 problems with your code.

  1. You are calling test_query function in the scope of definition of test_query function itself.

  2. to the function you are passing dataframe object itself. So you don't need to again create a new dataframe object from it.

  3. in the test_query function, you are creating ReturnValues variable of list datatype and then appending your query result to it. So instead of that, you can directly return df2