# Insert power table into DB table

**URL:** <https://forum.inductiveautomation.com/t/insert-power-table-into-db-table/18369>\
**Category:** Ignition\
**Created:** [June 8, 2018, 9:10pm UTC](https://forum.inductiveautomation.com/t/insert-power-table-into-db-table/18369 "2018-06-08T21:10:48Z")\
**Posts on this page:** 15\
**Page:** 1

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [June 8, 2018, 9:10pm UTC](https://forum.inductiveautomation.com/t/insert-power-table-into-db-table/18369/1 "2018-06-08T21:10:48Z")

</div>

How can you insert a power table dataset into a DB table. My plan is to use an Update query passing in the table headers as the DB columns and the detail rows as VALUES, but I don’t know how to iterate through the table to do this. Is there another way maybe to import the entire dataset at once by turning it into a csv or something?? Thanks

---

<div class="post-metadata">

**Author:** ![Stuart](https://avatars.discourse-cdn.com/v4/letter/s/cab0a1/32.png) [@Stuart](https://forum.inductiveautomation.com/u/Stuart)\
**Post date:** [June 8, 2018, 9:36pm UTC](https://forum.inductiveautomation.com/t/insert-power-table-into-db-table/18369/2 "2018-06-08T21:36:48Z")

</div>

WHen do you want this to happen? You can easily write a script that runs on a button press.

Will this dataset be changing? In other words, when you say insert, do you mean update? Inserting is creating a new row, updating is modifying an existing one.

I haven’t tested this, but this give you an idea of how I would approach the problem. This inserts for every row, which may cause performance problems if you’re tying to insert a huge number of rows. If you do have a huge number of rows, you can use the loop to build the query/parameters, and then run the insert at the end.

MySQL.

```auto
# Get the data property of the table
TableData = event.source.parent.getComponent('Power Table 1').data

# Convert to PyDataset (easier to loop through)
py_TableData = system.dataset.toPyDataSet(TableData)

# Start the transaction
txID = system.db.beginTransaction(timeout=5000)

# Loop through the rows in the dataset, and insert them into the database.
for row in py_TableData:
	
	# Get the row data using column name, or index. I suggest column name as indexs could change
	column1 = row['Dataset Column 1 Name'] 
	column2 = row[1] 
	column3 = row['Dataset Column 3 Name']
	
	# Build the query (python, triple quotes allow whitespace)
	sql = """
				INSERT INTO tbl_name
					(column1, column2, column3)
				VALUES
					(?, ?, ?)
			"""
	system.db.runPrepUpdate(sql, [column1, column2, column3], tx=txID)
	
# Commit and close the transaction
system.db.commitTransaction(txID)
system.db.closeTransaction(txID)

```

If this is a one time thing, then you’d be better off exporting the dataset to a csv, and importing directly into the database.

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [June 8, 2018, 10:08pm UTC](https://forum.inductiveautomation.com/t/insert-power-table-into-db-table/18369/3 "2018-06-08T22:08:11Z")

</div>

It’ll be done on button press. Yes it shouldbe an insert not update into mysql

---

<div class="post-metadata">

**Author:** ![Stuart](https://avatars.discourse-cdn.com/v4/letter/s/cab0a1/32.png) [@Stuart](https://forum.inductiveautomation.com/u/Stuart)\
**Post date:** [June 9, 2018, 12:07am UTC](https://forum.inductiveautomation.com/t/insert-power-table-into-db-table/18369/4 "2018-06-09T00:07:46Z")

</div>

Then I think the example I put above should do what you want.

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [June 11, 2018, 12:02pm UTC](https://forum.inductiveautomation.com/t/insert-power-table-into-db-table/18369/5 "2018-06-11T12:02:58Z")

</div>

Thanks, I will give this a try

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [June 11, 2018, 12:51pm UTC](https://forum.inductiveautomation.com/t/insert-power-table-into-db-table/18369/6 "2018-06-11T12:51:50Z")

</div>

[code]TableData = event.source.parent.getComponent(‘Table3’).data

```
# Convert to PyDataset (easier to loop through)
py_TableData = system.dataset.toPyDataSet(TableData)

# Start the transaction
txID = system.db.beginTransaction(timeout=5000)

# Loop through the rows in the dataset, and insert them into the database.
for row in py_TableData:
	
	# Get the row data using column name, or index. I suggest column name as indexs could change
	column1 = row['STYLE'] 
	column2 = row['QUANTITY']
	column3 = row['DESCRIPTION']
	column4 = row['5x5']
	column5 = row['4x8']
	column6 = row['RB']
	column7 = row['HW']
	column8 = row['OSB']
	# Build the query (python, triple quotes allow whitespace)
	sql = """
				INSERT INTO mill_serviceorder
					(column1, column2, column3, column4, column5, column6, column7, column8)
				VALUES
					(?, ?, ?, ?, ?, ?, ?, ?)
			"""
	system.db.runPrepUpdate(sql, [column1, column2, column3, column4, column5, column6, column7, column8], tx=txID)
	
# Commit and close the transaction
system.db.commitTransaction(txID)
system.db.closeTransaction(txID) [/code]

```

This the code I tried, and got an error. i’m attaching an example table and error message

 ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/c/c3bf6830d798dc863a1173657efe30ba4d446b9a.JPG) ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/c/c0696387e9112ea0966c0d812080ee122fc27c2c.JPG)

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [June 11, 2018, 1:13pm UTC](https://forum.inductiveautomation.com/t/insert-power-table-into-db-table/18369/7 "2018-06-11T13:13:50Z")

</div>

I forgot to change the db column values, changed them as below and it worked perfect!

```auto
INSERT INTO mill_serviceorder
	(STYLE, QUANTITY, DESCRIPTION, 5x5, 4x8, RB, HW, OSB)
```

Now I need to add one more piece to this puzzle to get it perfect…

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [June 11, 2018, 1:19pm UTC](https://forum.inductiveautomation.com/t/insert-power-table-into-db-table/18369/8 "2018-06-11T13:19:31Z")

</div>

I couldn’t run the script while in the actual client even while signed in as an admin. It works in the designer preview mode though??

Traceback (most recent call last):

File “event:actionPerformed”, line 9, in

java.lang.Exception: java.lang.Exception: Error executing system.db.beginTransaction(, 2, 5000)

```
caused by Exception: Error executing system.db.beginTransaction(, 2, 5000)
caused by GatewayException: Error executing function 'TxControl.beginTransaction': The current user does not have the required roles for this operation.
caused by GatewayFunctionException: The current user does not have the required roles for this operation.

```

Ignition v7.9.6 (b2018012914)  
Java: Oracle Corporation 1.8.0\_161

---

<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:** [June 11, 2018, 1:41pm UTC](https://forum.inductiveautomation.com/t/insert-power-table-into-db-table/18369/9 "2018-06-11T13:41:28Z")

</div>

Check your project properties – you need to enable legacy queries.

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [June 11, 2018, 1:55pm UTC](https://forum.inductiveautomation.com/t/insert-power-table-into-db-table/18369/10 "2018-06-11T13:55:36Z")

</div>

I had this problem before, and I figured it was because it’s not a named query, but I didn’t know you could turn on the legacy queries in the project properties. So that worked.

I have one last problem with this transaction. I have two db columns, ‘po’ and ‘day’, that need to be inserted along with the table data, but they are labels and are not in the table. How do I add that in? I tried running a named query to update the fields using where ‘po’ is null but it errors out. Any suggestions? Thanks

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [June 11, 2018, 2:15pm UTC](https://forum.inductiveautomation.com/t/insert-power-table-into-db-table/18369/11 "2018-06-11T14:15:24Z")

</div>

Got it! Thanks for help everyone

```auto
TableData = event.source.parent.getComponent('Table3').data
	
	# Convert to PyDataset (easier to loop through)
	py_TableData = system.dataset.toPyDataSet(TableData)

	po = event.source.parent.getComponent('PO Label').value
	day = event.source.parent.getComponent('DayColor').selectedStringValue
	
	# Start the transaction
	txID = system.db.beginTransaction(timeout=5000)
	
	# Loop through the rows in the dataset, and insert them into the database.
	for row in py_TableData:
		
		# Get the row data using column name, or index. I suggest column name as indexes could change
		column1 = row['STYLE'] 
		column2 = row['QUANTITY']
		column3 = row['DESCRIPTION']
		column4 = row['5x5']
		column5 = row['4x8']
		column6 = row['RB']
		column7 = row['HW']
		column8 = row['OSB']
		# Build the query (python, triple quotes allow whitespace)
		sql = """
					INSERT INTO mill_serviceorder
						(PO, DAY, STYLE, QUANTITY, DESCRIPTION, 5x5, 4x8, RB, HW, OSB)
					VALUES
						(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
				"""
		system.db.runPrepUpdate(sql, [po, day, column1, column2, column3, column4, column5, column6, column7, column8], tx=txID)
		
	# Commit and close the transaction
	system.db.commitTransaction(txID)
	system.db.closeTransaction(txID)
```

---

<div class="post-metadata">

**Author:** ![saurabhambildhuke08](https://avatars.discourse-cdn.com/v4/letter/s/91b2a8/32.png) [@saurabhambildhuke08](https://forum.inductiveautomation.com/u/saurabhambildhuke08)\
**Post date:** [February 4, 2019, 8:33am UTC](https://forum.inductiveautomation.com/t/insert-power-table-into-db-table/18369/12 "2019-02-04T08:33:32Z")

</div>

I am also facing the similar problem as i have one power table which has some column and rows. whenever the value in the table is updated/Current value i want to store that in SQL database. i have 20R, 15C and want t store all row and column data in a single insert query. Thank u

---

<div class="post-metadata">

**Author:** ![mcolson1590](https://avatars.discourse-cdn.com/v4/letter/m/e8c25b/32.png) [@mcolson1590](https://forum.inductiveautomation.com/u/mcolson1590)\
**Post date:** [May 11, 2022, 3:30pm UTC](https://forum.inductiveautomation.com/t/insert-power-table-into-db-table/18369/13 "2022-05-11T15:30:53Z")

</div>

Were Column4-8 bit’s in your sql? I seam to be having troubles as soon as I try to enter a bit. I’ve tried having column5 be true, false, 0 and 1. If I leave out this column, it works fine.

File “”, line 123, in   
INSERT INTO TENDERS  
(Unit, t\_stamp, tc\_tk2\_press, locob\_glyrtntemp, tc\_locob\_noflt)  
VALUES  
(?, ?, ?, ?, ?)  
, [Unit0, 2022-05-02 23:59:00.000, 173.43992910964087, 64.36174702807179, 0], , bd7819b4-4505-43d2-bf8f-319b0b26269c, false, false)

```
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)

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

at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.base/java.lang.reflect.Method.invoke(Unknown Source)

```

java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(  
INSERT INTO TENDERS  
(Unit, t\_stamp, tc\_tk2\_press, locob\_glyrtntemp, tc\_locob\_noflt)  
VALUES  
(?, ?, ?, ?, ?)  
, [Unit0, 2022-05-02 23:59:00.000, 173.43992910964087, 64.36174702807179, 0], , bd7819b4-4505-43d2-bf8f-319b0b26269c, false, false)

---

<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:** [May 11, 2022, 4:04pm UTC](https://forum.inductiveautomation.com/t/insert-power-table-into-db-table/18369/14 "2022-05-11T16:04:08Z")

</div>

Try this:

```python
query = "INSERT INTO TENDERS (Unit,t-stamp,tc_tk2_press,locob_glyrtntemp,tc_locob_noflt) VALUES (?,?,?,?,?)"

system.db.runPrepUpdate(query, [Unit0, 2022-05-02 23:59:00.000, 173.43992910964087, 64.36174702807179, False])

```

In python the built-ins for true and false are `True` and `False`. Though I would have expected 0 and 1 to work.

---

<div class="post-metadata">

**Author:** ![mcolson1590](https://avatars.discourse-cdn.com/v4/letter/m/e8c25b/32.png) [@mcolson1590](https://forum.inductiveautomation.com/u/mcolson1590)\
**Post date:** [May 11, 2022, 4:44pm UTC](https://forum.inductiveautomation.com/t/insert-power-table-into-db-table/18369/15 "2022-05-11T16:44:21Z")

</div>

Thank you “column5 = bool(float(row[‘tc\_locob\_noflt’])\>0.5)” worked. The value is coming from a CSV so I had to first convert to a float.