# How to write a smart scripting **URL:** \ **Category:** Ignition\ **Tags:** scripting\ **Created:** [April 9, 2022, 6:37am UTC](https://forum.inductiveautomation.com/t/how-to-write-a-smart-scripting/58249 "2022-04-09T06:37:25Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![yifeiliu08](https://avatars.discourse-cdn.com/v4/letter/y/bbe5ce/32.png) [@yifeiliu08](https://forum.inductiveautomation.com/u/yifeiliu08)\ **Post date:** [April 9, 2022, 6:37am UTC](https://forum.inductiveautomation.com/t/how-to-write-a-smart-scripting/58249/1 "2022-04-09T06:37:25Z")
I need store 10 data into database ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/6/b61ab49707072b83dfbd7c1373dc6249aec84f4c.png) ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/8/789fd6041d38c91eb0cfeb15751896a37f23728e.png) current is my stupid code: ```auto 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 ---
**Author:** ![Baskar\_Arumugam](https://avatars.discourse-cdn.com/v4/letter/b/94ad74/32.png) [@Baskar\_Arumugam](https://forum.inductiveautomation.com/u/Baskar_Arumugam)\ **Post date:** [April 9, 2022, 7:15am UTC](https://forum.inductiveautomation.com/t/how-to-write-a-smart-scripting/58249/2 "2022-04-09T07:15:46Z")
Create NamedQuery with parameter and just pass the parameter in dynamic way to get the same result using for loop. ---
**Author:** ![nideyijuyidong](https://avatars.discourse-cdn.com/v4/letter/n/7cd45c/32.png) [@nideyijuyidong](https://forum.inductiveautomation.com/u/nideyijuyidong)\ **Post date:** [April 9, 2022, 8:51am UTC](https://forum.inductiveautomation.com/t/how-to-write-a-smart-scripting/58249/3 "2022-04-09T08:51:18Z")
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. ---
**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:** [April 9, 2022, 9:15am UTC](https://forum.inductiveautomation.com/t/how-to-write-a-smart-scripting/58249/4 "2022-04-09T09:15:16Z")
Using `xrange(start, end)`, which generates numbers from `start` (included) to `end` (not included): ```python 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](https://peps.python.org/pep-0008/) regarding code style. ---
**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:** [April 9, 2022, 2:25pm UTC](https://forum.inductiveautomation.com/t/how-to-write-a-smart-scripting/58249/5 "2022-04-09T14:25:28Z")
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: ```auto 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') ``` ---
**Author:** ![zxcslo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zxcslo/32/61610_2.png) [@zxcslo](https://forum.inductiveautomation.com/u/zxcslo)\ **Post date:** [April 10, 2022, 7:05am UTC](https://forum.inductiveautomation.com/t/how-to-write-a-smart-scripting/58249/6 "2022-04-10T07:05:35Z")
I think that allArgs should be: ```auto allArgs = [[RecordID,SerNum,Pcs,n+1,x] for (n, x) in enumerate(Measures) ] ``` or ```auto allArgs = [z for z in ([RecordID,SerNum,Pcs,n+1,x] for (n, x) in enumerate(Measures))] ``` otherwise you get ![Screenshot_40](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/d/8d668513f23914125b5b35ce57841fc81fd95a2f.png) ---
**Author:** ![yifeiliu08](https://avatars.discourse-cdn.com/v4/letter/y/bbe5ce/32.png) [@yifeiliu08](https://forum.inductiveautomation.com/u/yifeiliu08)\ **Post date:** [April 10, 2022, 2:26pm UTC](https://forum.inductiveautomation.com/t/how-to-write-a-smart-scripting/58249/7 "2022-04-10T14:26:41Z")
thanks for your code, this code is simple for my level to understand ---
**Author:** ![yifeiliu08](https://avatars.discourse-cdn.com/v4/letter/y/bbe5ce/32.png) [@yifeiliu08](https://forum.inductiveautomation.com/u/yifeiliu08)\ **Post date:** [April 10, 2022, 2:34pm UTC](https://forum.inductiveautomation.com/t/how-to-write-a-smart-scripting/58249/8 "2022-04-10T14:34:43Z")
@pturmel @zxcslo I have try the code ```auto 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 ```auto 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. ---
**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:** [April 10, 2022, 5:00pm UTC](https://forum.inductiveautomation.com/t/how-to-write-a-smart-scripting/58249/9 "2022-04-10T17:00:34Z")
I got the list flattening part scrambled. Try this: ```auto 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') ``` ---
**Author:** ![yifeiliu08](https://avatars.discourse-cdn.com/v4/letter/y/bbe5ce/32.png) [@yifeiliu08](https://forum.inductiveautomation.com/u/yifeiliu08)\ **Post date:** [April 11, 2022, 1:51am UTC](https://forum.inductiveautomation.com/t/how-to-write-a-smart-scripting/58249/10 "2022-04-11T01:51:11Z")
it works, thanks for your code that show me another solution to solve the problem