Convert Named query into an array

Ignition
1

https://docs.inductiveautomation.com/display/DOC80/Script+Transform

I have found this Doc but I don't know why It doesn't work for me. I want to display database values (time series) into apex chart so I had to bind the data with a transform to convert the query into an array

this is my code

	Data = system.db.runNamedQuery("level" )
	pyData = system.dataset.toPyDataSet(Data)
	
	newList = system.tag.read(pyData).value.getRowCount()
	for row in Data:
	    newDict = {}
	    for i in range(len(row)):
	        newDict[ header[i] ] = row[i]
	     	newList.append(newDict)
	return newList
type or paste code here
2

There are multiple issue with that script, which no longer matches the linked manual page's examples. Most notably this line:

newList = system.tag.read(pyData).value.getRowCount()
  1. pyData is not a tagPath, so system.tag.read() will not return a good value
  2. newList is expected to be a List and so, even had this line returned something,
    it would have been an integer, and then the call to newList.append() would have failed.
  3. Unless the list header is defined in a part of the script you haven't posted, then it is undifined and the script would fail there also.

What type of binding is this transform on? It seems odd to me to use a transform without using the value supplied to that transform.

To convert a PyDataset to a list of dictionaries you would need code something like this:

data = system.db.runNamedQuery("level")
pyData = system.dataset.toPyDataSet(data)

return [{c:row[idx] for idx,c  in enumerate(pyData.columnNames)} for row in pyData]

Note that this will not maintain the the order of you're columns from row to row.

I am curious why a query binding with a return format set to JSON wont work here though. I don't have the apex charts module, so perhaps there is a difference that I am unaware of.

3

Sorry for my coding (I am not used to scripting yet) :sweat_smile:

I am using the Apex chart module for the first time and from the docs it is accepting data with a certain format

I am getting my data from the database (named query) and I want to convert the named query data to a data can be shown on the Apex chart

4

According to the documentation found here:

You can assign an Ignition Dataset directly to the series property and it should work, no conversion needed.

1 Like