Inserting data into an already created table using python script

[mjohnson0238]

Can anyone help/show me how to insert data into a table I have already created? Normally I would just JOIN these two together but the are on 2 different SQL servers so I cannot(unless I can?)

Here is the Named Query I have made for the existing table:

SELECT 
	  VP.PERSONNUM AS 'BadgeNum'
	, FORMAT( VP.INPUNCHDTM, 'hh:mm') AS 'ClockINTime'
	, VP.PERSONFULLNAME AS 'Name'
	, HOMELABORLEVELNM5 AS 'Shift'
	, HOMELABORLEVELNM6 AS 'Dept'
	, HOMELABORLEVELDSC7 AS 'JobTitle'
	, HOMELABORLEVELDSC4 AS 'Supervisor'
FROM 
	VP_EMPCUREARNTIME AS VP 
	LEFT OUTER JOIN VP_EMPLOYEEV42 ON VP.PERSONNUM = VP_EMPLOYEEV42.PERSONNUM 
	LEFT OUTER JOIN TIMEZONE ON VP_EMPLOYEEV42.TIMEZONENAME = TIMEZONE.ENGLISH
WHERE 
	(VP.LABORLEVELNAME3 = '0065')
	AND VP.INPUNCHDTM is not Null
	AND VP.LABORLEVELNAME5 NOT IN ('9', 'S')
	AND (VP.STARTDTM > DATEADD(d, - 1, GETDATE()))
	AND (VP.GMTSTARTDTM <= DATEADD(S, - TIMEZONE.GMTOFFSET, GETDATE()))
	AND (VP.GMTENDDTM IS NULL OR VP.GMTENDDTM >= DATEADD(S, - TIMEZONE.GMTOFFSET, GETDATE()))
order by HOMELABORLEVELNM6, HOMELABORLEVELDSC7

I am thinking that I would add the data from the table that I want to add into the transform script. Here is the transform script I have in place already on that Named Query:

def transform(self, value, quality, timestamp):
	table = self.getSibling("Table_0").props.data
	accounted_badge_num = [B['Badge_Num'] for B in table]
	json_obj = []
	for V in value:
		py_dic = {"value":dict(V)}
		if py_dic["value"]['BadgeNum'] in accounted_badge_num:
			py_dic["value"]['isAccountedFor'] = True
			py_dic["style"]={"backgroundColor":"green"}
		else:
			py_dic["value"]['isAccountedFor'] = False
		json_obj.append(py_dic)

			
	return json_obj

Here is a couple rows from the new table I want to add to this table:

[justinedwards.jle]

Is BadgeNum the matching primary key in both SQL servers that you are wanting to JOIN on?

[mjohnson0238]

The Named Query table, we only have READ access to it. In the new table the BadgeNum is the key.

Since I dont have write access to the previous table, thats why i thought I would need to scpriting.

[justinedwards.jle]

Perhaps, I misunderstood. Are you wanting to combine two datasets with different columns but a matching primary key, or are you wanting to add rows from one dataset to another dataset with identical columns?

[mjohnson0238]

@justinedwards.jle

add rows from one dataset to another dataset with identical columns

This one. I would like to add rows to the table that has the change script on it. The rows I want to add are from this table:

[justinedwards.jle]

This tutorial by @JordanCClark seems pretty thorough:

Combine two datasets

You will need to iterate through each dataset. I find dictionaries useful for this. # Set up sample datasets data1 = [['t_stamp', 'val1'], [1, 123], [3, 456], [4, 789], [9, 345], [10, 678], [11, 901], [15, 234], [17, 567], [20, 890], ] data2 = [['t_stamp', 'val2'], [2, 987], [3, 654], [7, 321], [9, 876], [12, 543], [14, 210], [17, 765], [20, 432], [22, 109], ] dataset1 = system.dataset.toDataSet(data1[0], data1[1:]) …

[lrose]

If the columns match, don’t make it harder than it needs to be.

system.dataset.appendDataset()
https://docs.inductiveautomation.com/display/DOC81/system.dataset.appendDataset

Though it would probably make your life a little easier if you had the named queries return an actual dataset.

So, assuming that you have a named query to fetch the data from the new table you could do something like:

def transform(self, value, quality, timestamp):
	table = self.getSibling("Table_0").props.data
	newTable = system.db.runNamedQuery('getNewTableData')
	combinedDataset = system.dataset.appendDataset(table,newTable)
	combinedData = system.dataset.toPyDataset(combinedDataset)
	accounted_badge_num = [B['Badge_Num'] for B in combinedData]
	json_obj = []
	for V in value:
		py_dic = {"value":dict(V)}
		if py_dic["value"]['BadgeNum'] in accounted_badge_num:
			py_dic["value"]['isAccountedFor'] = True
			py_dic["style"]={"backgroundColor":"green"}
		else:
			py_dic["value"]['isAccountedFor'] = False
		json_obj.append(py_dic)

	return json_obj

Though I might write the for loop in the transform like this. I think this is cleaner and more readable. You do you.

def transform(self, value, quality, timestamp):
	table = self.getSibling("Table_0").props.data
	newTable = system.db.runNamedQuery('getNewTableData')
	combinedDataset = system.dataset.appendDataset(table,newTable)
	combinedData = system.dataset.toPyDataset(combinedDataset)
	accounted_badge_num = [B['Badge_Num'] for B in combinedData]
	json_obj = []
	for V in value:
		py_dic = {"value":dict(V)}
		py_dic["value"]['isAccountedFor'] = py_dic['value']['BadgeNum'] in accounted_badge_num
		if py_dic['value']['isAccountedFor']:
			py_dic["style"]={"backgroundColor":"green"}
		json_obj.append(py_dic)

	return json_obj

[mjohnson0238]

@lrose

def transform(self, value, quality, timestamp):
	table = self.getSibling("Table_0").props.data
	newTable = system.db.runNamedQuery('getNewTableData')
	combinedDataset = system.dataset.appendDataset(table,newTable)
	combinedData = system.dataset.toPyDataset(combinedDataset)
	accounted_badge_num = [B['Badge_Num'] for B in combinedData]
	json_obj = []
	for V in value:
		py_dic = {"value":dict(V)}
		if py_dic["value"]['BadgeNum'] in accounted_badge_num:
			py_dic["value"]['isAccountedFor'] = True
			py_dic["style"]={"backgroundColor":"green"}
		else:
			py_dic["value"]['isAccountedFor'] = False
		json_obj.append(py_dic)

	return json_obj

When I attempt this I get an error saying Line 4 1st argument cannot be coerced into Dataset

[mjohnson0238]

I may see the error, I think the new table doesnt have all the columns the original table does

[mjohnson0238]

It was missing a column, fixed that, but still same error. Do the columns need to be in the same order?
Like table cloumn 1,2,3,4,5 - named query to run column1,2,4,3,5?

[lrose]

@mjohnson0238

Do the columns need to be in the same order?
Like table cloumn 1,2,3,4,5 - named query to run column1,2,4,3,5?

I honestly don't know if the order is important or not. I'm assuming that line 4 is the runNamedQuery(). Make sure that the named query is configured to return a dataset, also be sure that you are providing the correct path and a set of parameters if any (an empty dictionary if their are no parameters). I just threw that together, I don't know anything about how you have authored the Named Query.

[mjohnson0238]

Here is how the transform script looks:

The name query setup look s like this:

And returns like this:

[lrose]

I suspect that the return format for the binding on the Table_0.props.data is set to json. So you could try setting that to dataset.

If you are using the json format in other places and can’t or don’t want to change it from json then you could try this:

def transform(self, value, quality, timestamp):
	from com.inductiveautomation.ignition.common import TypeUtilities
	table = TypeUtilities.datasetFromJSON(self.getSibling("Table_0").props.data)
	newTable = system.db.runNamedQuery('getNewTableData')
	combinedDataset = system.dataset.appendDataset(table,newTable)
	combinedData = system.dataset.toPyDataset(combinedDataset)
	accounted_badge_num = [B['Badge_Num'] for B in combinedData]
	json_obj = []
	for V in value:
		py_dic = {"value":dict(V)}
		if py_dic["value"]['BadgeNum'] in accounted_badge_num:
			py_dic["value"]['isAccountedFor'] = True
			py_dic["style"]={"backgroundColor":"green"}
		else:
			py_dic["value"]['isAccountedFor'] = False
		json_obj.append(py_dic)

	return json_obj

[mjohnson0238]

I have the script worked out to where I have joined the 2 tables:

styleAccountedFor = {'backgroundColor': "green"}
	MusterCall = system.db.runNamedQuery('MusterCall')
	SM_Salary = system.db.runNamedQuery('SM_Salary')
	combinedDataset = system.dataset.appendDataset(MusterCall,SM_Salary)
	combinedData = system.dataset.toPyDataSet(combinedDataset)
	
	headers = system.dataset.getColumnHeaders(combinedData)
		
	checkedInData = (self.getSibling("Table_0").props.data)
	accountedBadgeNum = [B['Badge_Num'] for B in checkedInData]
			
	pyObj = []
			
	for row in combinedData:
		pyDict = {}
		pyValueDict = {headers[i]: row[i] for i in range(len(row))}
		if pyValueDict['BadgeNum'] in accountedBadgeNum:
			pyValueDict['isAccountedFor'] = True
			pyDict["style"] = styleAccountedFor
		else:
			pyValueDict['isAccountedFor'] = False
			pyDict['value'] = pyValueDict
		pyObj.append(pyDict)
		
					
	return pyObj

I have an issue now that when I select the a row and press a button to make that row turn green, it removes the row from the table. The row should stay and turn green.

Any ideas?

[mjohnson0238]

@lrose

from com.inductiveautomation.ignition.common import TypeUtilities
	table = TypeUtilities.datasetFromJSON

Heres what got it:

	styleAccountedFor = {'backgroundColor': "green"}
	MusterCall = value #system.db.runNamedQuery('MusterCall')
	SM_Salary = system.db.runNamedQuery('SM_Salary')
	combinedDataset = system.dataset.appendDataset(MusterCall,SM_Salary)
	combinedData = system.dataset.toPyDataSet(combinedDataset)
	    
	headers = system.dataset.getColumnHeaders(combinedData)
	        
	checkedInData = (self.getSibling("Table_0").props.data)
	accountedBadgeNum = [B['Badge_Num'] for B in checkedInData]
	            
	pyObj = []
	            
	for row in combinedData:
	    pyDict = {}
	    pyValueDict = {headers[i]: row[i] for i in range(len(row))}
	    if pyValueDict['BadgeNum'] in accountedBadgeNum:
	        pyValueDict['isAccountedFor'] = True
	        pyDict['value'] = pyValueDict
	        pyDict["style"] = styleAccountedFor
	    else:
	        pyValueDict['isAccountedFor'] = False
	        pyDict['value'] = pyValueDict
	    pyObj.append(pyDict)
	        
	                    
	return pyObj