# Efficiently Move UDT Array Data to SQL

**URL:** <https://forum.inductiveautomation.com/t/efficiently-move-udt-array-data-to-sql/26062>\
**Category:** Ignition\
**Created:** [June 24, 2019, 5:26pm UTC](https://forum.inductiveautomation.com/t/efficiently-move-udt-array-data-to-sql/26062 "2019-06-24T17:26:45Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![brendan.moldenhauer](https://avatars.discourse-cdn.com/v4/letter/b/87869e/32.png) [@brendan.moldenhauer](https://forum.inductiveautomation.com/u/brendan.moldenhauer)\
**Post date:** [June 24, 2019, 5:26pm UTC](https://forum.inductiveautomation.com/t/efficiently-move-udt-array-data-to-sql/26062/1 "2019-06-24T17:26:45Z")

</div>

I’m new to Igniton and Python, so sorry in advance.

I am trying to “efficiently” move data in “chunks” from an array in a Controllogix PLC to SQL. I had the below code working, but then later learned about the overhead involved.

```auto
if newValue.value:
	i = 0
	while i < 240:
		tStamp = system.tag.read("[default]Plt4_MchSig/Unit0Flip/Unit0Flip_"+str(i)+"_/tStamp").value
		Temp = system.tag.read("[default]Plt4_MchSig/Unit0Flip/Unit0Flip_"+str(i)+"_/Temperature").value
		Vibrtn = system.tag.read("[default]Plt4_MchSig/Unit0Flip/Unit0Flip_"+str(i)+"_/Vibration").value
		VoltsL1 = system.tag.read("[default]Plt4_MchSig/Unit0Flip/Unit0Flip_"+str(i)+"_/L1Voltage").value
		VoltsL2 = system.tag.read("[default]Plt4_MchSig/Unit0Flip/Unit0Flip_"+str(i)+"_/L2Voltage").value
		VoltsL3 = system.tag.read("[default]Plt4_MchSig/Unit0Flip/Unit0Flip_"+str(i)+"_/L3Voltage").value
		AmpsL1 = system.tag.read("[default]Plt4_MchSig/Unit0Flip/Unit0Flip_"+str(i)+"_/L1Amps").value
		AmpsL2 = system.tag.read("[default]Plt4_MchSig/Unit0Flip/Unit0Flip_"+str(i)+"_/L2Amps").value
		AmpsL3 = system.tag.read("[default]Plt4_MchSig/Unit0Flip/Unit0Flip_"+str(i)+"_/L3Amps").value

		system.db.runPrepUpdate("INSERT INTO Unit0 (PLCtStamp, temp, vib, VoltsL1, VoltsL2, VoltsL3, AmpsL1, AmpsL2, AmpsL3) VALUES (?,?,?,?,?,?,?,?,?)", [tStamp, Temp, Vibrtn, VoltsL1, VoltsL2, VoltsL3, AmpsL1, AmpsL2, AmpsL3])
		
		i += 1

```

I found this in a separate posting. It looks like what I would need to do, but I am struggling to convert it over.

```auto
def writeRollData(dsReducedData,recordNumber):
import system
pyData = system.dataset.toPyDataSet(dsReducedData)
block_id = system.tag.getTagValue("[PMIServer]GaugeBandTester/block_id")

valueString = “”
args = []
for row in pyData:
xVal = row[“distance”]
yVal = row[“height”]
valueString += “(?,?,?),”
args.append(block_id)
args.append(xVal)
args.append(yVal)

if valueString != “”:
valueString[:-1]
system.db.runPrepUpdate(“INSERT INTO GuageBand_RollData (block_id, xVal, yVal) VALUES %s” % valueString, args, “PMIServer”)
return

```

Any help in either converting things over, or suggestions on a better method would be greatly appreciated.

Thanks in advance.

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [June 24, 2019, 7:18pm UTC](https://forum.inductiveautomation.com/t/efficiently-move-udt-array-data-to-sql/26062/2 "2019-06-24T19:18:31Z")

</div>

A block transaction group would probably be the most maintainable way to solve this, but if you want to do it in code:

```auto
if newValue.value:
	databaseName = "" #must be populated in gw scope
	baseTagPath = "[default]Plt4_MchSig/Unit0Flip/Unit0Flip_%(index)s_/%(tag)s"
	query = "INSERT INTO Unit0 (PLCtStamp, temp, vib, VoltsL1, VoltsL2, VoltsL3, AmpsL1, AmpsL2, AmpsL3) VALUES %(values)s"

	tags = {"tStamp, Temperature", "Vibration", "L1Voltage", "L2Voltage", "L3Voltage", "L1Amps", "L2Amps", "L3Amps"}
	tagcount = 240

	tagPathList = []
	for i in range(tagcount):
		for tag in tags:
			tagPathList.append(baseTagPath % (i, tag))

	qualifiedValueList = system.tag.readAll(tagPathList)
	
	#option 1 - many individual queries, but bundled into a transaction:
	# txId = system.db.beginTransaction(timeout=5000, database=databaseName)
	# chunkLength = len(tags)
	# chunks = [data[chunkLength*i:chunkLength*(i+1)] for i in range(len(data)/chunkLength + 1)]
	# for chunk in chunks:
	# system.db.runPrepUpdate(query % "(?,?,?,?,?,?,?,?,?)", chunk, txId=txId)
	# system.db.commitTransaction(txId)
	# system.db.closeTransaction(txId)	
	
	#option 2 - one big query
	for i in range(tagcount):
		values = ", ".join("(?,?,?,?,?,?,?,?,?)") # create a long values statement
		# eg "VALUES = (?,?,?,?,?,?,?,?,?), (?,?,?,?,?,?,?,?,?), (?,?,?,?,?,?,?,?,?)"

	system.db.runPrepUpdate(query % values, qualifiedValueList, database=databaseName)

```

---

<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:** [June 24, 2019, 7:25pm UTC](https://forum.inductiveautomation.com/t/efficiently-move-udt-array-data-to-sql/26062/3 "2019-06-24T19:25:50Z")

</div>

EDIT: @paul-griffith posted as I was typing. I separated my lines out a bit since you’re new to Python. Still uses the One Big Query idea.

```python
if newValue.value:
 tagList = []
 questionMarksList = []
 for i in range(240):  
   # Make a list of tag names that we can use to read them all at once.
   # Add tStamp to List
   	tagList.append("[default]Plt4_MchSig/Unit0Flip/Unit0Flip_"+str(i)+"_/tStamp")
   
   # Add Temperature to List
   	tagList.append("[default]Plt4_MchSig/Unit0Flip/Unit0Flip_"+str(i)+"_/Temperature")
   
   # Add Vibration to List
   	tagList.append("[default]Plt4_MchSig/Unit0Flip/Unit0Flip_"+str(i)+"_/Vibration")
   
   # Add VoltsL1 to List
   	tagList.append("[default]Plt4_MchSig/Unit0Flip/Unit0Flip_"+str(i)+"_/L1Voltage")
   
   # Add VoltsL2 to List
   	tagList.append("[default]Plt4_MchSig/Unit0Flip/Unit0Flip_"+str(i)+"_/L2Voltage")
   
   # Add VoltsL3 to List
   	tagList.append("[default]Plt4_MchSig/Unit0Flip/Unit0Flip_"+str(i)+"_/L3Voltage")
   
   # Add AmpsL1 to List
   	tagList.append("[default]Plt4_MchSig/Unit0Flip/Unit0Flip_"+str(i)+"_/L1Amps")
   
   # Add AmpsL2 to List
   	tagList.append("[default]Plt4_MchSig/Unit0Flip/Unit0Flip_"+str(i)+"_/L2Amps")
   
   # Add AmpsL3 to List
   	tagList.append("[default]Plt4_MchSig/Unit0Flip/Unit0Flip_"+str(i)+"_/L3Amps")
  
   # Add question marks for use in runPrepUpdate later. (no blockID)
   questionMarksList.append("(?,?,?,?,?,?,?,?,?)")

 # Read all tags in tagList
 tagsIn = system.tags.readAll(tagList)
 
 # Create query string.
 query = "INSERT INTO Unit0 (PLCtStamp, temp, vib, VoltsL1, VoltsL2, VoltsL3, AmpsL1, AmpsL2, AmpsL3) VALUES " + ','.join(questionMarksList)
 values = [tag.value for tag in tagsIn]
   	
   # Finally, use runPrepUpdate to write to the database. 
 system.db.runPrepUpdate(query, values, "myDatabaseConnectionName")
```

---

<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:** [June 24, 2019, 7:28pm UTC](https://forum.inductiveautomation.com/t/efficiently-move-udt-array-data-to-sql/26062/4 "2019-06-24T19:28:06Z")

</div>

One more, that is similar, but adds a block id (else, how can you tell one from the other 239?):

```python
if newValue.value:
  tagList = []
  questionMarksList = []
  for i in range(240):  
    # Make a list of tag names that we can use to read them all at once.
    # Add tStamp to List
		tagList.append("[default]Plt4_MchSig/Unit0Flip/Unit0Flip_"+str(i)+"_/tStamp")
    
    # Add Temperature to List
		tagList.append("[default]Plt4_MchSig/Unit0Flip/Unit0Flip_"+str(i)+"_/Temperature")
    
    # Add Vibration to List
		tagList.append("[default]Plt4_MchSig/Unit0Flip/Unit0Flip_"+str(i)+"_/Vibration")
    
    # Add VoltsL1 to List
		tagList.append("[default]Plt4_MchSig/Unit0Flip/Unit0Flip_"+str(i)+"_/L1Voltage")
    
    # Add VoltsL2 to List
		tagList.append("[default]Plt4_MchSig/Unit0Flip/Unit0Flip_"+str(i)+"_/L2Voltage")
    
    # Add VoltsL3 to List
		tagList.append("[default]Plt4_MchSig/Unit0Flip/Unit0Flip_"+str(i)+"_/L3Voltage")
    
    # Add AmpsL1 to List
		tagList.append("[default]Plt4_MchSig/Unit0Flip/Unit0Flip_"+str(i)+"_/L1Amps")
    
    # Add AmpsL2 to List
		tagList.append("[default]Plt4_MchSig/Unit0Flip/Unit0Flip_"+str(i)+"_/L2Amps")
    
    # Add AmpsL3 to List
		tagList.append("[default]Plt4_MchSig/Unit0Flip/Unit0Flip_"+str(i)+"_/L3Amps")
   
    # Add question marks for use in runPrepUpdate later.
    questionMarksList.append("(?,?,?,?,?,?,?,?,?,?)")

  # Read all tags in tagList
  tagsIn = system.tags.readAll(tagList)
  
		
  # Create query
  query = "INSERT INTO Unit0 (blockID, PLCtStamp, temp, vib, VoltsL1, VoltsL2, VoltsL3, AmpsL1, AmpsL2, AmpsL3) VALUES " + ','.join(questionMarksList)
  valueList = [tag.value for tag in tagsIn]
  
  # OK this is a bit tricky. we have a list of values from the tags, but we need
  # to intersperse the blockIDs in there. So, we'll make a new list.
  values = []
  for i in range(240):
    # Since there are 240 blocks, we'll start by adding the blockID to the final list. 
    values.append(i)
    for j in range(9):
      # Here, we add the 9 tag values to the final list.
      # i*9 is used so that each time, we get the next 9 values
      values.append(i*9 + j)

	# Finally, use runPrepUpdate to write to the database. 
  system.db.runPrepUpdate(query, values, "myDatabaseConnectionName")
```

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [June 24, 2019, 7:42pm UTC](https://forum.inductiveautomation.com/t/efficiently-move-udt-array-data-to-sql/26062/5 "2019-06-24T19:42:56Z")

</div>

@JordanCClark’s suggestions are a lot more readable - mine is pretty code-golf-ey, so maybe not the best thing to put down and try to pick up again five years from now when you’ve added a new line 🙂

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [June 24, 2019, 8:00pm UTC](https://forum.inductiveautomation.com/t/efficiently-move-udt-array-data-to-sql/26062/6 "2019-06-24T20:00:34Z")

</div>

Better version of mine without the bugs that will prevent it from working even a little bit:

```auto
if newValue.value
    databaseName = "" # must be populated in gw scope
    baseTagPath = "[default]Plt4_MchSig/Unit0Flip/Unit0Flip_%(index)s_/%(tag)s"
    query = "INSERT INTO Unit0 (PLCtStamp, temp, vib, VoltsL1, VoltsL2, VoltsL3, AmpsL1, AmpsL2, AmpsL3) VALUES %(values)s"

    tags = {"tStamp, Temperature", "Vibration", "L1Voltage", "L2Voltage", "L3Voltage",
            "L1Amps", "L2Amps", "L3Amps"}
    tagcount = 240

    tagPathList = []
    for i in range(tagcount):
        for tag in tags:
            tagPathList.append(baseTagPath % {"index": i, "tag": tag})

    qualifiedValueList = system.tag.readAll(tagPathList)

    # option 1 - many individual queries, but bundled into a transaction:
    # txId = system.db.beginTransaction(timeout=5000, database=databaseName)
    # chunkLength = len(tags)
    # chunks = [qualifiedValueList[chunkLength*i:chunkLength*(i+1)] for i in range(tagcount//chunkLength + 1)]
    # for chunk in chunks:
    # system.db.runPrepUpdate(query % {"values": "(?,?,?,?,?,?,?,?,?)"}, [qv.value for qv in chunk], txId=txId)
    # system.db.commitTransaction(txId)
    # system.db.closeTransaction(txId)  

    # option 2 - one big query
    values = ", ".join(["(?,?,?,?,?,?,?,?,?)"] * tagcount) # create a long values statement
    # eg "VALUES = (?,?,?,?,?,?,?,?,?), (?,?,?,?,?,?,?,?,?), (?,?,?,?,?,?,?,?,?)"

    system.db.runPrepUpdate(query % values, [qv.value for qv in qualifiedValueList], database=databaseName)

```

---

<div class="post-metadata">

**Author:** ![brendan.moldenhauer](https://avatars.discourse-cdn.com/v4/letter/b/87869e/32.png) [@brendan.moldenhauer](https://forum.inductiveautomation.com/u/brendan.moldenhauer)\
**Post date:** [June 25, 2019, 12:06pm UTC](https://forum.inductiveautomation.com/t/efficiently-move-udt-array-data-to-sql/26062/7 "2019-06-25T12:06:59Z")

</div>

Thanks guys. I will give these both a try.

---

<div class="post-metadata">

**Author:** ![brendan.moldenhauer](https://avatars.discourse-cdn.com/v4/letter/b/87869e/32.png) [@brendan.moldenhauer](https://forum.inductiveautomation.com/u/brendan.moldenhauer)\
**Post date:** [June 28, 2019, 7:58pm UTC](https://forum.inductiveautomation.com/t/efficiently-move-udt-array-data-to-sql/26062/8 "2019-06-28T19:58:51Z")

</div>

```auto
  values = []
  for i in range(240):
    # Since there are 240 blocks, we'll start by adding the blockID to the final list. 
    values.append(i)
    for j in range(9):
      # Here, we add the 9 tag values to the final list.
      # i*9 is used so that each time, we get the next 9 values
      values.append(i*9 + j)

```

How do we get our ‘values’ list into the proper format?

ex.  
[(u’2019-06-28 14:46:57.192’, 0.0, 1.0137302875518799, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0),  
(u’2019-06-28 14:46:57.442’, 0.0, 1.0111638307571411, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0),  
( u’2019-06-28 14:46:57.692’, 0.0, 1.0201462507247925, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0)]

Also the blockID is just coming up as a list at the end of all of the values??

---

<div class="post-metadata">

**Author:** ![brendan.moldenhauer](https://avatars.discourse-cdn.com/v4/letter/b/87869e/32.png) [@brendan.moldenhauer](https://forum.inductiveautomation.com/u/brendan.moldenhauer)\
**Post date:** [July 1, 2019, 6:10pm UTC](https://forum.inductiveautomation.com/t/efficiently-move-udt-array-data-to-sql/26062/9 "2019-07-01T18:10:11Z")

</div>

I got it. I didn’t know that we needed a list of question marks in parenthesis.  
like this

```auto
INSERT INTO tblNumbers (field, tenx) VALUES (?, ?), (?, ?), (?, ?), (?, ?) [1, 2, 3, 4, 5, 6, 7, 8]

```

I also found out that I couldn’t do the 240 record at a time. I was limited to about 235.

Here is my final.

```auto
if newValue.value:
  tagList = []
  questionMarksList = []
  for i in range(200):  
    # Make a list of tag names that we can use to read them all at once.
    # Add tStamp to List
		tagList.append("[default]Plt4_MchSig/Unit0Flop/Unit0Flop_"+str(i)+"_/tStamp")
    
    # Add Temperature to List
		tagList.append("[default]Plt4_MchSig/Unit0Flop/Unit0Flop_"+str(i)+"_/Temperature")
    
    # Add Vibration to List
		tagList.append("[default]Plt4_MchSig/Unit0Flop/Unit0Flop_"+str(i)+"_/Vibration")
		
	# Add VoltsL1 to List
		tagList.append("[default]Plt4_MchSig/Unit0Flop/Unit0Flop_"+str(i)+"_/L1Voltage")
    
    # Add VoltsL2 to List
		tagList.append("[default]Plt4_MchSig/Unit0Flop/Unit0Flop_"+str(i)+"_/L2Voltage")
    
    # Add VoltsL3 to List
		tagList.append("[default]Plt4_MchSig/Unit0Flop/Unit0Flop_"+str(i)+"_/L3Voltage")
    
    # Add AmpsL1 to List
		tagList.append("[default]Plt4_MchSig/Unit0Flop/Unit0Flop_"+str(i)+"_/L1Amps")
    
    # Add AmpsL2 to List
		tagList.append("[default]Plt4_MchSig/Unit0Flop/Unit0Flop_"+str(i)+"_/L2Amps")
    
    # Add AmpsL3 to List
		tagList.append("[default]Plt4_MchSig/Unit0Flop/Unit0Flop_"+str(i)+"_/L3Amps")

		
  tagsIn = system.tag.readAll(tagList)

  valueList = [tag.value for tag in tagsIn]

  questionMarksList = ', '.join(["(?,?,?,?,?,?,?,?,?)"] * (len(valueList)/9))

  query = "INSERT INTO Unit0 (PLCtStamp, temp, vib, VoltsL1, VoltsL2, VoltsL3, AmpsL1, AmpsL2, AmpsL3) VALUES " + ''.join(questionMarksList)

  system.db.runPrepUpdate(query, valueList, 'mSigDB')

```

---

<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:** [July 8, 2019, 1:09pm UTC](https://forum.inductiveautomation.com/t/efficiently-move-udt-array-data-to-sql/26062/10 "2019-07-08T13:09:33Z")

</div>

Sorry, Brenden, have been on vacation and no interwebs. It was rather nice…

Glad you got it sorted out! 🙂