# Dynamically parsing out data from Table into Custom Property then back into SQL **URL:** \ **Category:** Ignition\ **Tags:** ignition81, perspective\ **Created:** [October 7, 2022, 3:21pm UTC](https://forum.inductiveautomation.com/t/dynamically-parsing-out-data-from-table-into-custom-property-then-back-into-sql/64954 "2022-10-07T15:21:56Z")\ **Posts on this page:** 14\ **Page:** 1
**Author:** ![CMuecke](https://avatars.discourse-cdn.com/v4/letter/c/df705f/32.png) [@CMuecke](https://forum.inductiveautomation.com/u/CMuecke)\ **Post date:** [October 7, 2022, 3:21pm UTC](https://forum.inductiveautomation.com/t/dynamically-parsing-out-data-from-table-into-custom-property-then-back-into-sql/64954/1 "2022-10-07T15:21:57Z")
I am able to get data from a SQL database to a Table in Perspective. Let's say Table A has an Item ID and a percentage. This table could have 1 entry or 15 entries. I would like to transfer these to a custom property. Would I be able to use a for loop for this? ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/5/6/56e6cf87f1202c8677d695c498b2a74ac1d546fd.png) Can I rename these to whatever I want? ---
**Author:** ![djs](https://avatars.discourse-cdn.com/v4/letter/d/e47774/32.png) [@djs](https://forum.inductiveautomation.com/u/djs)\ **Post date:** [October 7, 2022, 3:39pm UTC](https://forum.inductiveautomation.com/t/dynamically-parsing-out-data-from-table-into-custom-property-then-back-into-sql/64954/2 "2022-10-07T15:39:40Z")
What do you want your custom property to be specifically? Do you want it be in the same format as the _key_ prop? If so you can bind your _key_ prop to some custom property elsewhere. > [@CMuecke](#): > > Can I rename these to whatever I want? The name of that key comes from the binding you have set. Whenever the binding updates, the names will go back to AMT and ITEM\_ID (I'm guessing the column names in the database). You can't really change them by just clicking on them. You could change them with a script transform. What does your binding look like? ---
**Author:** ![CMuecke](https://avatars.discourse-cdn.com/v4/letter/c/df705f/32.png) [@CMuecke](https://forum.inductiveautomation.com/u/CMuecke)\ **Post date:** [October 7, 2022, 3:52pm UTC](https://forum.inductiveautomation.com/t/dynamically-parsing-out-data-from-table-into-custom-property-then-back-into-sql/64954/3 "2022-10-07T15:52:42Z")
> [@djs](#): > > What do you want your custom property to be specifically? > > Do you want it be in the same format as the _key_ prop? If so you can bind your _key_ prop to some custom property elsewhere. Let's say Weight and Location. As far as the format goes, it is more of I do not need ALL of the information, just bits and pieces. I know I can bind each one individually, but was hoping to see if I could get this to work without having to do that. ---
**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:** [October 7, 2022, 4:03pm UTC](https://forum.inductiveautomation.com/t/dynamically-parsing-out-data-from-table-into-custom-property-then-back-into-sql/64954/4 "2022-10-07T16:03:49Z")
You _can_ do this with a script transform, e.g.: ```python return [ { "amount": row["AMT"] "itemId": row["ITEM_ID"] } for row in value ] ``` Or, imperatively, if that's more readable than a list comprehension: ```python ret = [] for row in value: ret.append({ "amount": row["AMT"] "itemId": row["ITEM_ID"] }) return ret ``` However, if you _just_ need to rename columns, then I would recommend using aliases in your query; transforms are significantly more "expensive" in terms of performance than directly returning the data in the format you want in the first place. ---
**Author:** ![CMuecke](https://avatars.discourse-cdn.com/v4/letter/c/df705f/32.png) [@CMuecke](https://forum.inductiveautomation.com/u/CMuecke)\ **Post date:** [October 7, 2022, 4:36pm UTC](https://forum.inductiveautomation.com/t/dynamically-parsing-out-data-from-table-into-custom-property-then-back-into-sql/64954/5 "2022-10-07T16:36:37Z")
I will be doing some calculations with the returned values. Now for the fun part. I eventually want to take the information stored in this property and query a SQL database with information in this property. I will need to use a for loop and a query string? ---
**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:** [October 7, 2022, 4:52pm UTC](https://forum.inductiveautomation.com/t/dynamically-parsing-out-data-from-table-into-custom-property-then-back-into-sql/64954/6 "2022-10-07T16:52:46Z")
Don't run a SQL query in a transform. Let's take a step back, lest we go down an [XY problem](https://xyproblem.info/) rabbit hole. You've got one outer SQL query returning a list of items. You want to do some calculations on these items. Then, at some point, you want to issue additional queries? Can you more clearly outline what the flow of data is here? What are operators/end users doing to trigger a query to run, or, if they aren't, can this calculation happen entirely outside of Perspective? ---
**Author:** ![CMuecke](https://avatars.discourse-cdn.com/v4/letter/c/df705f/32.png) [@CMuecke](https://forum.inductiveautomation.com/u/CMuecke)\ **Post date:** [October 7, 2022, 9:26pm UTC](https://forum.inductiveautomation.com/t/dynamically-parsing-out-data-from-table-into-custom-property-then-back-into-sql/64954/7 "2022-10-07T21:26:02Z")
![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/0/10235f5c8d2bd871878d0be9fa2ce79ac7b0897b.png) ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/3/93d9b2e9e61ff373a28398930edb858332e92f6f.png) So here is the operator screen. When the Operator clicks on certain work orders, the custom properties will calculate out. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/a/6a338c9c0fe17872df97305f3a48162b8718c7ca.png) ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/a/e/aef9cab1d57218d667724c23e9670d1b39faf1b7.png) I am using the custom properties as placeholders until the operator gets the work order/run set the way they want it. I am building a query to send back to the database. When the operator is ready, I would like to send the custom properties to a SQL database. (Probably off of a push button or something not automatically.) I suppose these calculations could happen outside of perspective, but I think there will always be a need to edit or change something. Flow of data is SQL to Tables, to Custom Properties, and back to SQL if possible. Let me know if none of this makes sense. ---
**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:** [October 7, 2022, 10:29pm UTC](https://forum.inductiveautomation.com/t/dynamically-parsing-out-data-from-table-into-custom-property-then-back-into-sql/64954/8 "2022-10-07T22:29:30Z")
Got it; that makes more sense. I would say you're totally fine to 'stage' values in custom properties. When you're ready to insert the data, I would at minimum have a named query to handle the actual data insert. You could also write a project library script (e.g. `dataEntry` with some function `enterOperatorData`) that's as "pure" as possible - it accepts whatever parameters you have to store, and sends them into the database. That way you're separating the data entry process (as far as DB logic) from any logic in your Perspective screen, making it easier to change one or the other independently. ---
**Author:** ![CMuecke](https://avatars.discourse-cdn.com/v4/letter/c/df705f/32.png) [@CMuecke](https://forum.inductiveautomation.com/u/CMuecke)\ **Post date:** [October 10, 2022, 12:27pm UTC](https://forum.inductiveautomation.com/t/dynamically-parsing-out-data-from-table-into-custom-property-then-back-into-sql/64954/9 "2022-10-10T12:27:45Z")
If I am doing multiple inserts, would a for loop be the most efficient way to send this back to the database? ---
**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:** [October 10, 2022, 2:37pm UTC](https://forum.inductiveautomation.com/t/dynamically-parsing-out-data-from-table-into-custom-property-then-back-into-sql/64954/10 "2022-10-10T14:37:13Z")
If you don't know the exact number of values, then yes, dynamically issuing N number of queries in a loop is fine. For best performance/atomicity, you should probably wrap in a transaction: [system.db.beginTransaction - Ignition User Manual 8.1 - Ignition Documentation](https://docs.inductiveautomation.com/display/DOC81/system.db.beginTransaction) The network traffic to the DB is basically the same, but the database will 'hold' all the records until the transaction is finalized, at which point it can update indices/insert the actual data into storage in one operation. ---
**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:** [October 10, 2022, 2:50pm UTC](https://forum.inductiveautomation.com/t/dynamically-parsing-out-data-from-table-into-custom-property-then-back-into-sql/64954/11 "2022-10-10T14:50:25Z")
What Paul said, but also consider dynamically constructing "Prep" inserts with multiple VALUES elements. JDBC allows 4k "?" parameters at a time, IIRC. This reduces the number of DB round trips. ---
**Author:** ![CMuecke](https://avatars.discourse-cdn.com/v4/letter/c/df705f/32.png) [@CMuecke](https://forum.inductiveautomation.com/u/CMuecke)\ **Post date:** [October 11, 2022, 6:18pm UTC](https://forum.inductiveautomation.com/t/dynamically-parsing-out-data-from-table-into-custom-property-then-back-into-sql/64954/12 "2022-10-11T18:18:21Z")
com.inductiveautomation.ignition.common.script.JythonExecException Traceback (most recent call last): File "function:runAction", line 5, in runAction TypeError: toPyDataSet(): 1st arg can't be coerced to com.inductiveautomation.ignition.common.Dataset ```auto def runAction(self, event): #txId = system.db.beginTransaction(timeout=5000) #status=2 tableData = self.parent.parent.getChild("LeftContainer").custom.BatchData pyData = system.dataset.toPyDataSet(tableData) WorkOrderDataQuery = "INSERT INTO BATCH (WORKORDER_ID, FORMULA_ID, ITEM_ID, SOURCE_ID, BATCH_WGT VALUES (?, ?)" for row in pyData: args = [row[0], row[1], row[2], row[3], row[4]] system.db.runPrepUpdate(WorkOrderDataQuery, args) #system.db.commitTransaction(txId) #system.db.closeTransaction(txId) ``` ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/8/189db6286dc0eb4613aff0f42516b828b66ee172.png) I am going to assume I am seeing this error because I am trying push a datatype into different one. Am I going down the right path? ---
**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:** [October 11, 2022, 6:21pm UTC](https://forum.inductiveautomation.com/t/dynamically-parsing-out-data-from-table-into-custom-property-then-back-into-sql/64954/13 "2022-10-11T18:21:50Z")
You don't need to coerce `tableData` into a PyDataset; you can directly iterate over it: ```python def runAction(self, event): #txId = system.db.beginTransaction(timeout=5000) #status=2 tableData = self.parent.parent.getChild("LeftContainer").custom.BatchData WorkOrderDataQuery = "INSERT INTO BATCH (WORKORDER_ID, FORMULA_ID, ITEM_ID, SOURCE_ID, BATCH_WGT VALUES (?, ?, ?, ?, ?)" for row in tableData: args = [row['WorkOrderID'], row['FormulaID'], row['ItemId'], row['SourceID'], row['BatchTgt']] system.db.runPrepUpdate(WorkOrderDataQuery, args) #system.db.commitTransaction(txId) #system.db.closeTransaction(txId) ``` ---
**Author:** ![CMuecke](https://avatars.discourse-cdn.com/v4/letter/c/df705f/32.png) [@CMuecke](https://forum.inductiveautomation.com/u/CMuecke)\ **Post date:** [October 11, 2022, 6:54pm UTC](https://forum.inductiveautomation.com/t/dynamically-parsing-out-data-from-table-into-custom-property-then-back-into-sql/64954/14 "2022-10-11T18:54:12Z")
Thank you so much for the help!