Grid variable updating in python script

Matillion ETL
1

I have a list of values generated while pulling the data from REST API(Assume the list name to be X). I want to push this list values in grid variable(Assume Grid name -g and this grid has one column named -gc) so that I can use this grid values outside the python script for further execution.

I don't know how to proceed for the above problem.

Here is the code and example of list :-

X=['qwer','tyui','uiop','sdfgh']

for data in X:

 g.append(data)

if g !=[]:

 context.updateGridVariable('gc',g)

 

 

 

2

Hello @AkashBansal​

Sorry you have been unable to get your answer from our community users! I would recommend raising this on a support case with our amazing Solution Architects, they will be able to support you with this.

Please do keep us posted how you achieve this as I and the community would love to learn how!

Have a great day!

Joe

3

Hello @AkashBansal​ ,

The handling of grid variables in matillion python components have a value per column and a list per row and all this is in one list.

Something like this:

X = ['qwer','tyui','uiop','sdfgh']

gv = []

for data in X:

#reset the 'list'

g = []

print data

#add the correct amount of grid variable 'columns'

g.append(data)

print g

#adding to final grid variable list

gv.append(g)

print gv

#loop is done, updating grid variable

context.updateGridVariable('gc',gv)

#checking if grid variable was updated as expected.

print context.getGridVariable('gc')

4

Results of a query to a grid variable in Python/Jython are not straightforward and the documentation or examples just don't seem to exist. I had to use this method for my project instead of query results to grid like I normally do.

 

This is an example and it's the absolute minimum required to get a grid variable populated from a simple query using Python/Jython.

 

I was getting a cast exception PyTuple to PyList with my query results when attempting to update the grid variable I had setup to hold the contents of the query. The following works (Jython) and I don't get any cast problems when running the script. I'm sure there's a much better way of doing this but I've only been using Python/Jython for about a year whereas I've been using C# for over 10.

 

My grid variable (jv_grid_variable_example) has four columns and potentially many rows.

 

sql = "SELECT Col1, Col2, Col3, Col4 FROM DBName.Information_Schema.Tables WHERE TABLE_TYPE LIKE '%TABLE%'"

myCursor = context.cursor()

myCursor.execute(sql)

myResultSet = cursor.fetchall()

#results look like: ([u'Value1', u'Value2', u'Value3', u'Value4'], [u'Value1', u'Value2', u'Value3', u'Value4'], ...)

 

#put the results from the query into the proper type the grid variable expects (list)

myVariablePayload = [List(i) for i in myResultSet]

#results look like: [[u'Value1', u'Value2', u'Value3', u'Value4'], [u'Value1', u'Value2', u'Value3', u'Value4'], ...]

 

content.updateGridVariable('jv_grid_variable_example', myVariablePayload)

 

The example adds one more step to the normal process of populating variables with Python/Jython in the script task by turning the results into a list instead of a tuple. You can try on your own and use the print statements on the two variables with results to see the difference in the output.

 

Hopefully this saves some folks from searching instead of creating.

5

Thanks for this post @MarkRugs​ it helped a lot.

I did find one issue and it may be due to version, but I was unable to use [List(i) for i in myResultSet]. I received an error I was able to accomplish the same thing by looping over the structure.