Efficiently Move UDT Array Data to SQL

Ignition
1

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.

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.

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.

2

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

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)
1 Like
3

EDIT: @PGriffith 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.

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")
4

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

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")
1 Like
5

@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 :slight_smile:

6

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

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)
1 Like
7

Thanks guys. I will give these both a try.

8 
  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??

9

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

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.

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')
1 Like
10

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

Glad you got it sorted out! :slight_smile: