# Insert into the database from 2 different sources

**URL:** <https://forum.inductiveautomation.com/t/insert-into-the-database-from-2-different-sources/51997>\
**Category:** Ignition\
**Tags:** perspective\
**Created:** [October 15, 2021, 4:50pm UTC](https://forum.inductiveautomation.com/t/insert-into-the-database-from-2-different-sources/51997 "2021-10-15T16:50:28Z")\
**Posts on this page:** 19\
**Page:** 1

<div class="post-metadata">

**Author:** ![pkhoshroo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pkhoshroo/32/26801_2.png) [@pkhoshroo](https://forum.inductiveautomation.com/u/pkhoshroo)\
**Post date:** [October 15, 2021, 4:50pm UTC](https://forum.inductiveautomation.com/t/insert-into-the-database-from-2-different-sources/51997/1 "2021-10-15T16:50:28Z")

</div>

Hello

I have a database table with 14 columns, I want to insert into this database from 2 different sources. (I am using perspective module)

**Source1:**

The data from 4 entry boxes:

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/6/366bd6c9fb4cda82937a319addc71a93030814b2.png)

**Source 2:**

The data from a dataset table (2 rows and 10 columns)

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/d/0d971301adfe7c27ac3fd941350ce2231661c9df.png)

**Final Databse:**

The database table after inserting these 2 sources would be:

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/a/1aa4e32137faad43773370c142555dc6056769b3.png)

I have tried different solutions but none of them were successful.

I am able to insert the 1st source into the database but I am not able to combine that with source 2 and send that back to the database, and I am using a message handler.

I greatly appreciate any information you could share with me.

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [October 15, 2021, 5:44pm UTC](https://forum.inductiveautomation.com/t/insert-into-the-database-from-2-different-sources/51997/2 "2021-10-15T17:44:21Z")

</div>

Assuming you have you are using `system.db.runPrepUpdate` (which you should be using over `system.db.runUpdateQuery`) and that you want to do this for every single row in your dataset and that the values of the columns are the same order as the columns in your query-

```auto
dataResource = somePath.to.dataResource.text # or whatever field you are getting from this componeont
dataPartSN = some.path.to.dataPartSN.text
DataPassFail = somepath
datausername = someusername

for row in range(someData.rowCount):
    values = [dataResouce, dataPartSN, DataPassFail, datausername]
    for col in range(someData.colCount):
        values.append(someData.getValueAt(row, col))
    system.db.runPrepUpdate("INSERT INTO someTable (col1, ..., col14) VALUES (?, ... ?)", values)

```

I think this should do it. Just make sure your this part

```auto
dataResource = somePath.to.dataResource.text # or whatever field you are getting from this componeont
dataPartSN = some.path.to.dataPartSN.text
DataPassFail = somepath
datausername = someusername

```

you set to the actual paths of your component fields that you want to save.

Edit: Just saw you said you were using a message handler. Any specific reason for that? Perspective scripts run on the gateway already, and if you’re just trying to securely run a SQL query, you could achieve that by making a Named Query.

---

<div class="post-metadata">

**Author:** ![pkhoshroo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pkhoshroo/32/26801_2.png) [@pkhoshroo](https://forum.inductiveautomation.com/u/pkhoshroo)\
**Post date:** [October 15, 2021, 6:01pm UTC](https://forum.inductiveautomation.com/t/insert-into-the-database-from-2-different-sources/51997/3 "2021-10-15T18:01:05Z")

</div>

Thank you very much for your reply!  
Appreciate it!!!

I tried your suggestion here:

```auto
	dataResource = "datasource.user"
	newPartSN= self.view.custom.part.id.sn
	newPassFail= self.view.custom.part.id.overall_pass_fail
	username= self.session.props.auth.user.userName
	
	data= self.view.custom.part.spec.data #Dataset [12R ? 10C]
	pds = system.dataset.toPyDataSet(data) #<PyDataset rows:12 cols:10>
	for row in range(pds.rowCount):
	    values = [dataResource, newPartSN, newPassFail, username]
	    for col in range(pds.colCount):
	        values.append(pds.getValueAt(row, col))
	    system.db.runPrepUpdate("INSERT INTO 000_process_data (resource, part_sn, overall_pass_fail, username, spec, spec_rev, name, description, state, findings, low_limit, process_value, high_limit, units) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", values)

```

But I get this error:  
the object has no attribute ‘colCount’

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [October 15, 2021, 6:10pm UTC](https://forum.inductiveautomation.com/t/insert-into-the-database-from-2-different-sources/51997/4 "2021-10-15T18:10:31Z")

</div>

My fault, should be `pds.columnCount`, I make that typo all the time in my own scripting and I passed it on to you.

---

<div class="post-metadata">

**Author:** ![pkhoshroo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pkhoshroo/32/26801_2.png) [@pkhoshroo](https://forum.inductiveautomation.com/u/pkhoshroo)\
**Post date:** [October 15, 2021, 6:47pm UTC](https://forum.inductiveautomation.com/t/insert-into-the-database-from-2-different-sources/51997/5 "2021-10-15T18:47:25Z")

</div>

So that did not work I got the error again,

I changed it to this:

```auto
	dataResource = "melt.user"
	newPartSN= self.view.custom.part.id.sn
	newPassFail= self.view.custom.part.id.overall_pass_fail
	username= self.session.props.auth.user.userName
    values = [dataResource, newPartSN, newPassFail, username]

    data= self.view.custom.part.spec.data #sample Dataset [12R ? 10C]
	pds = system.dataset.toPyDataSet(data)
	for index in range(len(pds)):
		rowData=list(pds[index])
		rowData.append([values,rowData])
		system.db.runPrepUpdate("INSERT INTO process_data (resource, part_sn, overall_pass_fail, username, spec, spec_rev, name, description, state, findings, low_limit, process_value, high_limit, units) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", rowData)

```

the only problem I have is to append values with rowData:

```auto
data= self.view.custom.part.spec.data #sample Dataset [12R ? 10C]
pds = system.dataset.toPyDataSet(data)
**rowData=list(pds[index])**
**values = [dataResource, newPartSN, newPassFail, username]**
**rowData.append([values,rowData])**

```

---

<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:** [October 15, 2021, 7:03pm UTC](https://forum.inductiveautomation.com/t/insert-into-the-database-from-2-different-sources/51997/6 "2021-10-15T19:03:59Z")

</div>

Don’t append it, ADD it.

```auto
values = [1,2,3]
rowData = [4,5,6]

print values + rowData

```

result:  
`[1,2,3,4,5,6]`

```auto
values = [1,2,3]
rowData = [4,5,6]

rowData += values
print rowData

```

result:  
`[4,5,6,1,2,3]`

The way you add them together is important, and must match the order of your columns, otherwise your data will not be in the columns you want it in or you will get an error because the datatype doesn’t match.

Also your script needs some adjusting, the whole point of converting to a pyDataset is to take advantage of the data structure. Using range(len(pds)) in your for loop defeats the purpose.

Your code should look like this:

```auto
	dataResource = "melt.user"
	newPartSN= self.view.custom.part.id.sn
	newPassFail= self.view.custom.part.id.overall_pass_fail
	username= self.session.props.auth.user.userName
    values = [dataResource, newPartSN, newPassFail, username]

    data= self.view.custom.part.spec.data #sample Dataset [12R ? 10C]
	pds = system.dataset.toPyDataSet(data)
	for row in pds:
		rowData= values + row
		system.db.runPrepUpdate("INSERT INTO process_data (resource, part_sn, overall_pass_fail, username, spec, spec_rev, name, description, state, findings, low_limit, process_value, high_limit, units) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", rowData)

```

---

<div class="post-metadata">

**Author:** ![pkhoshroo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pkhoshroo/32/26801_2.png) [@pkhoshroo](https://forum.inductiveautomation.com/u/pkhoshroo)\
**Post date:** [October 15, 2021, 7:24pm UTC](https://forum.inductiveautomation.com/t/insert-into-the-database-from-2-different-sources/51997/7 "2021-10-15T19:24:46Z")

</div>

Thank you very much! APPRECIATE it 🙂  
I got the error for that but I wrote this script based on your suggestion and it is now working:

```auto
	dataResource = "melt.user"
	newPartSN= self.view.custom.part.id.sn
	newPassFail= self.view.custom.part.id.overall_pass_fail
	username= self.session.props.auth.user.userName

	data= self.view.custom.part.spec.data #Dataset [12R ? 9C]
	pds = system.dataset.toPyDataSet(data) #<PyDataset rows:12 cols:9>
	
	for index in range(len(pds)):
		values = [dataResource, newPartSN, newPassFail, username]
		rowData=list(pds[index])
		rowData=values+rowData
		system.db.runPrepUpdate("INSERT INTO process_data (resource, part_sn, overall_pass_fail, username, spec, spec_rev, name, description, state, findings, low_limit, process_value, high_limit, units) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", rowData)

```

---

<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:** [October 15, 2021, 8:33pm UTC](https://forum.inductiveautomation.com/t/insert-into-the-database-from-2-different-sources/51997/8 "2021-10-15T20:33:23Z")

</div>

Glad you’ve got it working.

I would be remiss if I didn’t mention that re-writing the for loop will make this much more readable in the future, as it will actually say what you are doing. It will still function the same, but:

`for row in pds` is much more readable (and pythonic) than `for index in range(len(pds))`

Also, I couldn’t help myself, you can optimize the loop many times by removing un-needed function calls and by not making a prepQuery call for each row.

I would refactor the code to this:

```auto
	dataResource = "melt.user"
	newPartSN= self.view.custom.part.id.sn
	newPassFail= self.view.custom.part.id.overall_pass_fail
	username= self.session.props.auth.user.userName

	data= self.view.custom.part.spec.data
    values = [dataResource,newPartSN,newPassFail,username]
    psa = system.dataset.toPyDataSet(data)
    query = """INSERT INTO process_data
                        (resource,
                         part_sn,
                         overall_pass_fail,
                         username, spec,
                         spec_rev,
                         name,
                         description,
                         state,
                         findings,
                         low_limit,
                         process_value,
                         high_limit,
                         units)
                     VALUES %s"""
    valueArgs = ','.join(['(?,?,?,?,?,?,?,?,?,?,?,?,?,?)'] * data.rowCount)

    argList = [values + row for row in pds]

    system.db.runPrepUpdate(query % valueArgs,argList)

```

---

<div class="post-metadata">

**Author:** ![pkhoshroo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pkhoshroo/32/26801_2.png) [@pkhoshroo](https://forum.inductiveautomation.com/u/pkhoshroo)\
**Post date:** [October 18, 2021, 12:49pm UTC](https://forum.inductiveautomation.com/t/insert-into-the-database-from-2-different-sources/51997/9 "2021-10-18T12:49:36Z")

</div>

Thank you very much @lrose  
Appreciate the great information.

Although I got the error code cannot be compiled with this version:

org.python.core.PyIndentationError  
File “function:onMessageReceived”, line 8  
values = [dataResource, newPartSN, newPassFail, username]  
^  
IndentationError: unindent does not match any outer indentation level

Ignition v8.1.10 (b2021090812)  
Java: Azul Systems, Inc. 11.0.11

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [October 18, 2021, 3:02pm UTC](https://forum.inductiveautomation.com/t/insert-into-the-database-from-2-different-sources/51997/10 "2021-10-18T15:02:24Z")

</div>

@lrose used 4-space indenting at the beginning of his lines, which is different from the tab you are using. All indents need to match, whatever you use. 🙂

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/9/c9f65d5baceede39c5562eec0a73f1800ba60a4c.png)

---

<div class="post-metadata">

**Author:** ![pkhoshroo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pkhoshroo/32/26801_2.png) [@pkhoshroo](https://forum.inductiveautomation.com/u/pkhoshroo)\
**Post date:** [October 18, 2021, 3:11pm UTC](https://forum.inductiveautomation.com/t/insert-into-the-database-from-2-different-sources/51997/11 "2021-10-18T15:11:48Z")

</div>

Thank you, Jordan!  
I fixed that; however, I get another error:

line 26, in onMessageReceived  
TypeError: can only concatenate list (not “com.inductiveautomation.ignition.common.script.builtin.DatasetUtilities$PyDataSet$PyRow”) to list

Line 26: argList = [values + row for row in pds]

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [October 18, 2021, 3:49pm UTC](https://forum.inductiveautomation.com/t/insert-into-the-database-from-2-different-sources/51997/12 "2021-10-18T15:49:21Z")

</div>

You need to cast the row to a list:

```python
argList = [values + list(row) for row in pds]

```

But, that won’t work, as the above returns a list of lists, and you need a flat list. Try:

```python
argList = []
for row in pds:
	argList.extend(values + list(row))

```

---

<div class="post-metadata">

**Author:** ![pkhoshroo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pkhoshroo/32/26801_2.png) [@pkhoshroo](https://forum.inductiveautomation.com/u/pkhoshroo)\
**Post date:** [October 18, 2021, 4:50pm UTC](https://forum.inductiveautomation.com/t/insert-into-the-database-from-2-different-sources/51997/13 "2021-10-18T16:50:38Z")

</div>

It worked now!!! Thank you very much, Jordan. Appreciate it.

---

<div class="post-metadata">

**Author:** ![Rahul\_Bhakte1](https://avatars.discourse-cdn.com/v4/letter/r/c89c15/32.png) [@Rahul\_Bhakte1](https://forum.inductiveautomation.com/u/Rahul_Bhakte1)\
**Post date:** [September 21, 2023, 7:27am UTC](https://forum.inductiveautomation.com/t/insert-into-the-database-from-2-different-sources/51997/14 "2023-09-21T07:27:23Z")

</div>

Hello, I am facing the similar issue. That I want to insert entire dataset into database table.  
I followed the code by @lrose but getting below error.

Code:

```auto
data1 = system.tag.read("[default]TEST/Tag").value
pyData = system.dataset.toPyDataSet(data1)
query = "INSERT INTO MyTable (C1, C2, C3, C4, C5, C6, C7) VALUES %s"
                            
valueArgs = ','.join(['(?,?,?,?,?,?,?)'] * data1.getRowCount())
argList = [row for row in pyData]
system.db.runPrepUpdate(query % valueArgs, argList, 'MyDB')

```

Error:

```auto
Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Unable to decode arguments

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:351)

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:325)

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:278)

	at com.inductiveautomation.ignition.client.script.ClientDBUtilities._runPrepStmt(ClientDBUtilities.java:288)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:261)

	... 23 more

Caused by: java.io.InvalidClassException: failed to read class descriptor

```

I am not sure what I am missing here.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [September 21, 2023, 12:29pm UTC](https://forum.inductiveautomation.com/t/insert-into-the-database-from-2-different-sources/51997/15 "2023-09-21T12:29:10Z")

</div>

> [@Rahul\_Bhakte1](#):
>
> `argList = [row for row in pyData]`

This yields a list of lists. "Prep" queries need a flat list. Try this:

```auto
argList = [v for row in pyData for v in row]

```

Or this:

```auto
argList = [v for v in row for row in pyData]

```

(I can never remember which way they go. 🤷‍♂️ )

---

<div class="post-metadata">

**Author:** ![Rahul\_Bhakte1](https://avatars.discourse-cdn.com/v4/letter/r/c89c15/32.png) [@Rahul\_Bhakte1](https://forum.inductiveautomation.com/u/Rahul_Bhakte1)\
**Post date:** [September 21, 2023, 1:12pm UTC](https://forum.inductiveautomation.com/t/insert-into-the-database-from-2-different-sources/51997/16 "2023-09-21T13:12:18Z")

</div>

Awesome!!!  
That worked.  
Thank you so much for your help @pturmel

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [September 21, 2023, 1:25pm UTC](https://forum.inductiveautomation.com/t/insert-into-the-database-from-2-different-sources/51997/17 "2023-09-21T13:25:44Z")

</div>

Keep in mind that the number of question marks allowed in a query is limited by JDBC drivers. Some as low as ~2000 or so. You should include logic to make multiple inserts when a dataset is large.

---

<div class="post-metadata">

**Author:** ![Rahul\_Bhakte1](https://avatars.discourse-cdn.com/v4/letter/r/c89c15/32.png) [@Rahul\_Bhakte1](https://forum.inductiveautomation.com/u/Rahul_Bhakte1)\
**Post date:** [September 21, 2023, 1:41pm UTC](https://forum.inductiveautomation.com/t/insert-into-the-database-from-2-different-sources/51997/18 "2023-09-21T13:41:28Z")

</div>

Noted.  
Thank you very much again. That helped me a lot.

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [September 21, 2023, 1:54pm UTC](https://forum.inductiveautomation.com/t/insert-into-the-database-from-2-different-sources/51997/19 "2023-09-21T13:54:11Z")

</div>

First one. Same order as if you were nesting for loops:

```python
argList = []
for row in pyData:
    for v in row:
        argList.append(v)

argList = [v for row in pyData for v in row]

```

Or you could do this:

```python
from itertools import chain

argList = list(chain(*pyData))
# or
argList = list(chain.from_iterable(pyData))

```