# Inserting data into an already created table using python script

**URL:** <https://forum.inductiveautomation.com/t/inserting-data-into-an-already-created-table-using-python-script/64329>\
**Category:** Ignition\
**Tags:** scripting, ignition81, perspective\
**Created:** [September 21, 2022, 1:04pm UTC](https://forum.inductiveautomation.com/t/inserting-data-into-an-already-created-table-using-python-script/64329 "2022-09-21T13:04:31Z")\
**Posts on this page:** 15\
**Page:** 1

<div class="post-metadata">

**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\
**Post date:** [September 21, 2022, 1:04pm UTC](https://forum.inductiveautomation.com/t/inserting-data-into-an-already-created-table-using-python-script/64329/1 "2022-09-21T13:04:31Z")

</div>

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:

```auto
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:

```auto
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:  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/e/1e7d5c532cb610a8c800e9176b47879c276c4485.png)

---

<div class="post-metadata">

**Author:** ![justinedwards.jle](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justinedwards.jle/32/37854_2.png) [@justinedwards.jle](https://forum.inductiveautomation.com/u/justinedwards.jle)\
**Post date:** [September 21, 2022, 1:12pm UTC](https://forum.inductiveautomation.com/t/inserting-data-into-an-already-created-table-using-python-script/64329/2 "2022-09-21T13:12:56Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\
**Post date:** [September 21, 2022, 1:31pm UTC](https://forum.inductiveautomation.com/t/inserting-data-into-an-already-created-table-using-python-script/64329/3 "2022-09-21T13:31:54Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![justinedwards.jle](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justinedwards.jle/32/37854_2.png) [@justinedwards.jle](https://forum.inductiveautomation.com/u/justinedwards.jle)\
**Post date:** [September 21, 2022, 1:46pm UTC](https://forum.inductiveautomation.com/t/inserting-data-into-an-already-created-table-using-python-script/64329/4 "2022-09-21T13:46:20Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\
**Post date:** [September 21, 2022, 1:55pm UTC](https://forum.inductiveautomation.com/t/inserting-data-into-an-already-created-table-using-python-script/64329/5 "2022-09-21T13:55:30Z")

</div>

> [@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:  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/d/7d3c17294b4776ac7e9a2aa92d2343a3e5a69055.png)

---

<div class="post-metadata">

**Author:** ![justinedwards.jle](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justinedwards.jle/32/37854_2.png) [@justinedwards.jle](https://forum.inductiveautomation.com/u/justinedwards.jle)\
**Post date:** [September 21, 2022, 2:27pm UTC](https://forum.inductiveautomation.com/t/inserting-data-into-an-already-created-table-using-python-script/64329/6 "2022-09-21T14:27:33Z")

</div>

This tutorial by @JordanCClark seems pretty thorough:

> [@Combine two datasets](https://forum.inductiveautomation.com/t/combine-two-datasets/45855/11):
>
> 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:]) …

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [September 21, 2022, 2:42pm UTC](https://forum.inductiveautomation.com/t/inserting-data-into-an-already-created-table-using-python-script/64329/7 "2022-09-21T14:42:55Z")

</div>

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](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:

```python
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.

```auto
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
```

---

<div class="post-metadata">

**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\
**Post date:** [September 21, 2022, 3:38pm UTC](https://forum.inductiveautomation.com/t/inserting-data-into-an-already-created-table-using-python-script/64329/8 "2022-09-21T15:38:35Z")

</div>

> [@lrose](#):
>
> ```auto
> 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

---

<div class="post-metadata">

**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\
**Post date:** [September 21, 2022, 3:42pm UTC](https://forum.inductiveautomation.com/t/inserting-data-into-an-already-created-table-using-python-script/64329/9 "2022-09-21T15:42:24Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\
**Post date:** [September 21, 2022, 3:51pm UTC](https://forum.inductiveautomation.com/t/inserting-data-into-an-already-created-table-using-python-script/64329/10 "2022-09-21T15:51:35Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [September 21, 2022, 4:00pm UTC](https://forum.inductiveautomation.com/t/inserting-data-into-an-already-created-table-using-python-script/64329/11 "2022-09-21T16:00:51Z")

</div>

> [@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.

---

<div class="post-metadata">

**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\
**Post date:** [September 21, 2022, 4:06pm UTC](https://forum.inductiveautomation.com/t/inserting-data-into-an-already-created-table-using-python-script/64329/12 "2022-09-21T16:06:01Z")

</div>

Here is how the transform script looks:  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/6/e621093aa0fe312dd2e08d4f0afb56c2c0e5889f.png)  
The name query setup look s like this:

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/c/1c5f2d158641b459629622601f1582af95196770.png)  
And returns like this:  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/6/f6c2a352341607885cd8da6c1e11176cdc7b9f38.png)

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [September 21, 2022, 5:29pm UTC](https://forum.inductiveautomation.com/t/inserting-data-into-an-already-created-table-using-python-script/64329/13 "2022-09-21T17:29:30Z")

</div>

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:

```auto
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

```

---

<div class="post-metadata">

**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\
**Post date:** [September 29, 2022, 6:09pm UTC](https://forum.inductiveautomation.com/t/inserting-data-into-an-already-created-table-using-python-script/64329/14 "2022-09-29T18:09:02Z")

</div>

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

```auto
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?

---

<div class="post-metadata">

**Author:** ![mjohnson0238](https://avatars.discourse-cdn.com/v4/letter/m/4bbf92/32.png) [@mjohnson0238](https://forum.inductiveautomation.com/u/mjohnson0238)\
**Post date:** [September 29, 2022, 8:22pm UTC](https://forum.inductiveautomation.com/t/inserting-data-into-an-already-created-table-using-python-script/64329/15 "2022-09-29T20:22:32Z")

</div>

> [@lrose](#):
>
> ```auto
> from com.inductiveautomation.ignition.common import TypeUtilities
> table = TypeUtilities.datasetFromJSON
> 
> ```

Heres what got it:

```auto
	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

```