How to write a smart scripting

Ignition
1

I need store 10 data into database

image
image259Ă—564 32.2 KB

image
image564Ă—520 26.7 KB

current is my stupid code:

query="""
		INSERT INTO [dbo].[RubberFuYeWidthData]
           ([RecordID]
           ,[SerNum]
           ,[Pcs]
           ,[IndexN]
           ,[Measured]
           ,[RecordTime])
     VALUES
           (?
           ,?
           ,?
           ,?
           ,?
           ,SYSDATETIME())
"""
RecordID=event.source.parent.RecordID
SerNum=event.source.parent.getComponent('SerNum').text
Pcs=event.source.parent.getComponent('PCS').value


Measured1=event.source.parent.getComponent('Measure1').floatValue
Measured2=event.source.parent.getComponent('Measure2').floatValue
Measured3=event.source.parent.getComponent('Measure3').floatValue
Measured4=event.source.parent.getComponent('Measure4').floatValue
Measured5=event.source.parent.getComponent('Measure5').floatValue
Measured6=event.source.parent.getComponent('Measure6').floatValue
Measured7=event.source.parent.getComponent('Measure7').floatValue
Measured8=event.source.parent.getComponent('Measure8').floatValue
Measured9=event.source.parent.getComponent('Measure9').floatValue
Measured10=event.source.parent.getComponent('Measure10').floatValue

args1=[RecordID,SerNum,Pcs,1,Measured1]
args2=[RecordID,SerNum,Pcs,2,Measured2]
args3=[RecordID,SerNum,Pcs,3,Measured3]
args4=[RecordID,SerNum,Pcs,4,Measured4]
args5=[RecordID,SerNum,Pcs,5,Measured5]
args6=[RecordID,SerNum,Pcs,6,Measured6]
args7=[RecordID,SerNum,Pcs,7,Measured7]
args8=[RecordID,SerNum,Pcs,8,Measured8]
args9=[RecordID,SerNum,Pcs,9,Measured9]
args10=[RecordID,SerNum,Pcs,10,Measured10]


system.db.runPrepUpdate(query,args1,'EHCMES')
system.db.runPrepUpdate(query,args2,'EHCMES')
system.db.runPrepUpdate(query,args3,'EHCMES')
system.db.runPrepUpdate(query,args4,'EHCMES')
system.db.runPrepUpdate(query,args5,'EHCMES')
system.db.runPrepUpdate(query,args6,'EHCMES')
system.db.runPrepUpdate(query,args7,'EHCMES')
system.db.runPrepUpdate(query,args8,'EHCMES')
system.db.runPrepUpdate(query,args9,'EHCMES')
system.db.runPrepUpdate(query,args10,'EHCMES')

I write 10 times the code, how to use a smart solution to improve? thanks a lot

2

Create NamedQuery with parameter and just pass the parameter in dynamic way to get the same result using for loop.

3

At start, you’d better to learn basic python knowledge, like for loop，range ， string format and so on.
Python script is easy and useful, you just need some time.
For this question ,using “for i in range(10):” will help a lot.

4

Using xrange(start, end), which generates numbers from start (included) to end (not included):

RecordID = event.source.parent.RecordID
SerNum = event.source.parent.getComponent('SerNum').text
Pcs = event.source.parent.getComponent('PCS').value

args_list = [
	[
		RecordID,
		SerNum,
		i,
		event.source.parent.getComponent('Measure{}'.format(i)).floatValue
	]  for i in xrange(1, 11)
]

for args in args_list:
	system.db.runPrepUpdate(query, args, 'EHCMES')

Python’s basics are quite easy, and widely used so you’ll pretty much always find solutions on google.
I’ll also suggest reading PEP8’s recommendations regarding code style.

2 Likes
5

I would arrange to perform all ten inserts with a single SQL statement (multiple elements after VALUES). Like so, with programmatic generation of the SQL:

query="""
INSERT INTO [dbo].[RubberFuYeWidthData]
  ([RecordID], [SerNum], [Pcs], [IndexN], [Measured], [RecordTime])
VALUES
  %s;
""" % ",\n  ".join(["(?, ?, ?, ?, ?, SYSDATETIME())"] * 10)

container = event.source.parent

RecordID=container.RecordID
SerNum=container.getComponent('SerNum').text
Pcs=container.getComponent('PCS').value
Measures = [container.getComponent('Measure%d' % (n+1)).floatValue for n in range(10)]
allArgs = [z for z in [RecordID,SerNum,Pcs,n+1,x] for (n, x) in enumerate(Measures)]

system.db.runPrepUpdate(query, allArgs, 'EHCMES')
1 Like
6

I think that allArgs should be:

allArgs = [ [RecordID,SerNum,Pcs,n+1,x] for (n, x) in enumerate(Measures) ]

or

allArgs = [ z for z in ([RecordID,SerNum,Pcs,n+1,x] for (n, x) in enumerate(Measures))]

otherwise you get

Screenshot_40
Screenshot_401449Ă—814 123 KB

1 Like
7

thanks for your code, this code is simple for my level to understand

8

@pturmel @zxcslo
I have try the code

query="""
INSERT INTO [dbo].[RubberFuYeWidthData]
  ([RecordID], [SerNum], [Pcs], [IndexN], [Measured], [RecordTime])
VALUES
  %s;
""" % ",\n  ".join(["(?, ?, ?, ?, ?, SYSDATETIME())"] * 10)

container = event.source.parent

RecordID=container.RecordID
SerNum=container.getComponent('SerNum').text
Pcs=container.getComponent('PCS').value
Measures = [container.getComponent('Measure%d' % (n+1)).floatValue for n in range(10)]
allArgs =  [ z for z in ([RecordID,SerNum,Pcs,n+1,x] for (n, x) in enumerate(Measures))]
#[[RecordID,SerNum,Pcs,n+1,x] for (n, x) in enumerate(Measures)] it will report fault

system.db.runPrepUpdate(query, allArgs, 'EHCMES')

here is the fault code

	caused by Exception: Error executing system.db.runPrepUpdate(
INSERT INTO [dbo].[RubberFuYeWidthData]
  ([RecordID], [SerNum], [Pcs], [IndexN], [Measured])
VALUES
  (?, ?, ?, ?, ?),
  (?, ?, ?, ?, ?),
  (?, ?, ?, ?, ?),
  (?, ?, ?, ?, ?),
  (?, ?, ?, ?, ?),
  (?, ?, ?, ?, ?),
  (?, ?, ?, ?, ?),
  (?, ?, ?, ?, ?),
  (?, ?, ?, ?, ?),
  (?, ?, ?, ?, ?);
, [[21, u'EHC800-439598-2021-07', 1.0, 1, 36.0], [21, u'EHC800-439598-2021-07', 1.0, 2, 33.0], [21, u'EHC800-439598-2021-07', 1.0, 3, 38.0], [21, u'EHC800-439598-2021-07', 1.0, 4, 37.0], [21, u'EHC800-439598-2021-07', 1.0, 5, 32.0], [21, u'EHC800-439598-2021-07', 1.0, 6, 39.0], [21, u'EHC800-439598-2021-07', 1.0, 7, 37.79999923706055], [21, u'EHC800-439598-2021-07', 1.0, 8, 36.70000076293945], [21, u'EHC800-439598-2021-07', 1.0, 9, 24.799999237060547], [21, u'EHC800-439598-2021-07', 1.0, 10, 34.900001525878906]], EHCMES, , false, false)
	caused by GatewayException: Unable to decode arguments
	caused by InvalidClassException: failed to read class descriptor

Ignition v8.1.14 (b2022012711)
Java: Azul Systems, Inc. 11.0.13

thanks for your support code.

9

I got the list flattening part scrambled. Try this:

query="""
INSERT INTO [dbo].[RubberFuYeWidthData]
  ([RecordID], [SerNum], [Pcs], [IndexN], [Measured], [RecordTime])
VALUES
  %s;
""" % ",\n  ".join(["(?, ?, ?, ?, ?, SYSDATETIME())"] * 10)

container = event.source.parent

RecordID=container.RecordID
SerNum=container.getComponent('SerNum').text
Pcs=container.getComponent('PCS').value
Measures = [container.getComponent('Measure%d' % (n+1)).floatValue for n in range(10)]
rows = [[RecordID,SerNum,Pcs,n+1,x] for (n, x) in enumerate(Measures)]
allArgs = [z for row in rows for z in row]

system.db.runPrepUpdate(query, allArgs, 'EHCMES')
1 Like
10

it works, thanks for your code that show me another solution to solve the problem