We could also explain. This is basic python, and you'll likely need to use some if you're going to use ignition. Might as well start now.
But the good news is that python is an easy language, and getting started doesn't take long.
First, I'm sorry about this bit of code... It's wrong. I shouldn't have posted it without trying it first, especially when doing something else at the same time.
It errors out because it's trying to extract the names AND the values from the values returned by the query... But the column names are not in the values.
So, this code tried to reference the second row of the table, which only has 1. That's why it raises an IndexError.
Now, this should work (and I tested it this time !):
values = [value.getValueAt(0, col_index) for col_index in xrange(value.columnCount)]
names = value.columnNames
return [
{
'name': name,
'value': val
} for name, val in zip(names, values)
]
So, how does it work ?
First, we extract the values from the first row.
There's a built-in function for extracting a value from a dataset, that looks like this:
ds.getValueAt(row_index, column_index)
Where ds is the name of the dataset. row_index and column_index start from 0 for the first one.
To get all the values in one row, we need to loop through it:
for col_index in xrange(ds.columnCount):
ds.getValueAt(0, col_index)
We generate the column indices with xrange(n), which produces all number between 0 and n (n not included). ds.columnCount returns the number of columns in the dataset. So xrange(ds.columnCount) will produce a number for each column, that we can use as index.
Then we use ds.getValueAt(0, col_index) to get the values in the first row.
That loop will actually do nothing, as we're not storing the values anywhere. To store them, we could create a list, and append each value to that list. Or, use a list comprehension, which is pretty much syntactic sugar offered by python to make simple loops:
values = [value.getValueAt(0, col_index) for col_index in xrange(value.columnCount)]
The things used there are the same as in the loop, except the for part is at the end.
This will produce a list of all the values, that we store in a variable named values
Next, we need the column names. Luckily for us, datasets have a built-in method that does just that:
ds.getColumnNames(). We can also use ds.columnNames, which does exactly the same thing.
names = ds.columnNames
Now we have the columns names, and the values of the first row. All that's left to do is to put them together to build an array of objects. Each object will contain key/value pairs, where the key will be the column name and the value will be... the value.
Be careful here, as the column name is NOT the column of the dataset from the query, but the column name of the new table we're building. It might be confusing.
Let's say the query returns this:
| foo | bar |
| 42 | 21 |
And we want a table that looks like this:
| name | value |
| foo | 42 |
| bar | 21 |
The array would be
[
{
'name': 'foo',
'value': 42
},
{
'name': 'bar',
'value': 21
}
]
Each object (called dictionaries in python, surrounded by curly braces {}) will be a row in the new table, and each key/value pair in them is a column.
To build that list dynamically, we can use another loop (or list comprehension).
names = ['foo', 'bar']
values = [42, 21]
table = [
{
'name': name,
'value': value
} for name, value in zip(names, values)
]
zip(names, values) will produce pairs, composed of one element from names and one element from values. Here, it will generate [('foo', 42), ('bar', 21)]
We get one pair at each iteration of the loop, which we can use to build the object.
Putting everything together, for a script transform, where the dataset returned by the query is named value:
values = [value.getValueAt(0, col_index) for col_index in xrange(value.columnCount)]
names = value.columnNames
return [
{
'name': name,
'value': val
} for name, val in zip(names, values)
]
I hope this is clear enough, if you need a more detailed explanation about something in particular, feel free to ask. Or to google it, python is very well documented on the internet.
Notes:
- You can change your query's return format to json, and use this script instead:
return [
{
'name': name,
'value': val
} for name, val in value[0].items()
]
The json return will be a list with only one element, a dictionary containing a key/value pair for each column. We use value[0] to get this single element, then call its .items() method to generate a list of of the key/value pairs inside it. The rest of the code is similar to the first solution.
- I over simplified a few things as to not make things too confusing for now.
- This code assumes the query ALWAYS return 1 row. Not 0, not 2.
If it returns an empty dataset (or empty list in the case of json), it will fail. If that's a possibility, you first need to ensure the returned data is not empty.
This is easily done with
if value.rowCount > 0:
do_your_thing
in the case of the dataset, or
if value:
do_your_thing
in the case of json.