Perspective scripting-how to reference a specific cell in a table in python

Ignition
,
1

Help please, I have been stuck for the last 2 hours.

Overview of what i am trying to do:
I have this table in Perspective- it is pulling from a SQL database

image
image478×326 4.39 KB

I am trying to configure a button to use a for loop to go though each row of this table.

on each row the loop would grab the SheetID cell value and then use that as a parameter to run a query. (it has to be done in python).

Here is the issue i am running into and I desperately need help on. How do I reference a specific cell value in this table???

the loop is simple
R=0
While R< num rows

  • grab sheet ID value (I can not do this)<------------(this is where i need help)!!!
  • run query pass sheet id as parameter ( i can do this)
  • R +=1

All I need is to know how to reference a specific cell (When it is NOT selected). If you can help thank you!!

Past Tries: This is where i am coming from (I do not need help on anything below this, i am just giving you background of where i am stuck)

I have tried using Event configuration on a label to get the label to display the cell value
##table = event.source.parent.getComponent("Table")
##value = table.data.getValueAt(0, "SheetID")
##self.props.text = value

2

Are you using a dataset or JSON for the table's data property?

This works with the default table component data.

def runAction(self, event):
	data = self.getSibling("Table").props.data
	for row in data:
		country = row['country']
		# Do something with country ...

Please see Wiki - how to post code on this forum.

3

Data set, the table is a dataset from a sql query

4

In the SQL Query binding window, set it to output JSON as the output format.
Then you can easily use python to iterate through the table.

5

That worked, thank you

7

What's the query that needs to be done for each row ?

8

For posterities sake, you don't have to use the JSON return format to accomplish this.

def runAction(self, event):
    data = system.dataset.toPyDataSet(self.getSibling("Table").props.data)
    for row in data:
         sheetID = row['SheetID']
         specificData = system.db.runNamedQuery('YourNamedQuery',{'SheetID':sheetID})

Or, if you don't want to use a PyDataSet it would look like this:

def runAction(self, event):
    data = self.getSibling("Table").props.data
    for row in range(data.rowCount):
        sheetId = data.getValueAt(row,'SheetID')
        specificData = system.db.runNamedQuery('YourNamedQuery',{'SheetID':sheetID})
9

Thank you, that will be helpful in the future

10

I already solved it but the query is to find all sheets with that SheetID and change an assigned bit value from 1 to 0 or 0 to 1.

11

If I understand correctly, I think you should be able to do it in one query with something like this:

ids = data.getColumnAsList(data.getColumnIndex('SheetId'))

q = """
update your_table
set column = ~column
where SheetId in ({})
""".format(','.join("?" for _ in ids))
system.db.runPrepUpdate(q, ids)