# Dictionary into SQL row **URL:** \ **Category:** Ignition\ **Created:** [November 21, 2011, 4:26pm UTC](https://forum.inductiveautomation.com/t/dictionary-into-sql-row/3635 "2011-11-21T16:26:06Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![jsham](https://avatars.discourse-cdn.com/v4/letter/j/ac91a4/32.png) [@jsham](https://forum.inductiveautomation.com/u/jsham)\ **Post date:** [November 21, 2011, 4:26pm UTC](https://forum.inductiveautomation.com/t/dictionary-into-sql-row/3635/1 "2011-11-21T16:26:06Z")
Ok, I’ve got a script written which grabs some time-stamped XML data and returns a dictionary where the keys represent the column name. How would I go about getting this dictionary inserted into my sql table as a row? I could run a transaction group where each tag an expression item like ```auto x = myfunction() x[key] ``` but I don’t want to run this script and perform a new XML fetch for every tag. Would rather fetch the XML once and parse it into this dictionary, then access the dictionary to build my insert queries. I’m sure there’s a way to do this, I’m just not coming up with it… ---
**Author:** ![Greg.Buehler](https://avatars.discourse-cdn.com/v4/letter/g/ecc23a/32.png) [@Greg.Buehler](https://forum.inductiveautomation.com/u/Greg.Buehler)\ **Post date:** [November 21, 2011, 6:02pm UTC](https://forum.inductiveautomation.com/t/dictionary-into-sql-row/3635/2 "2011-11-21T18:02:49Z")
[quote=“jsham”]Ok, I’ve got a script written which grabs some time-stamped XML data and returns a dictionary where the keys represent the column name. How would I go about getting this dictionary inserted into my sql table as a row? I could run a transaction group where each tag an expression item like ```auto x = myfunction() x[key] ``` but I don’t want to run this script and perform a new XML fetch for every tag. Would rather fetch the XML once and parse it into this dictionary, then access the dictionary to build my insert queries. I’m sure there’s a way to do this, I’m just not coming up with it…[/quote] your saying you have a table with two columns and you want to store the key and value in this table? ---
**Author:** ![jsham](https://avatars.discourse-cdn.com/v4/letter/j/ac91a4/32.png) [@jsham](https://forum.inductiveautomation.com/u/jsham)\ **Post date:** [November 21, 2011, 6:07pm UTC](https://forum.inductiveautomation.com/t/dictionary-into-sql-row/3635/3 "2011-11-21T18:07:27Z")
More like each key is a column, want to insert the value (value = dict[key]) items in a row. ---
**Author:** ![jsham](https://avatars.discourse-cdn.com/v4/letter/j/ac91a4/32.png) [@jsham](https://forum.inductiveautomation.com/u/jsham)\ **Post date:** [November 21, 2011, 6:12pm UTC](https://forum.inductiveautomation.com/t/dictionary-into-sql-row/3635/4 "2011-11-21T18:12:24Z")
So if my dictionary result is ```auto dictionary = {'t_stamp': '2011-11-11 11:11:11', 'Bob': 89.9, 'Joe': 188.72, 'Sally': 21.44} ``` I want to make a table insert ```auto INSERT INTO table (t_stamp, Bob, Joe, Sally) VALUES ('2011-11-11 11:11:11', 89.9, 188.72, 21.44); ``` ---
**Author:** ![jsham](https://avatars.discourse-cdn.com/v4/letter/j/ac91a4/32.png) [@jsham](https://forum.inductiveautomation.com/u/jsham)\ **Post date:** [November 21, 2011, 6:17pm UTC](https://forum.inductiveautomation.com/t/dictionary-into-sql-row/3635/5 "2011-11-21T18:17:23Z")
Oh yeah, and I’d like this to happen outside of a client, so either a gateway script or even better, a transaction group would be preferred. ---
**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:** [November 21, 2011, 6:47pm UTC](https://forum.inductiveautomation.com/t/dictionary-into-sql-row/3635/6 "2011-11-21T18:47:50Z")
Using your example I made this script to construct the query: ```auto dict = {'t_stamp': '2011-11-11 11:11:11', 'Bob': 89.9, 'Joe': 188.72, 'Sally': 21.44} dictKeys=dict.keys() dictValues=dict.values() query="INSERT INTO table" queryCols="(" queryVals="VALUES(" for x in range(len(dictKeys)): queryCols+=str(dictKeys[x])+"," queryVals+="'"+str(dictValues[x])+"'," query+=queryCols[:-1]+") "+queryVals[:-1]+")" print query ``` ---
**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:** [November 21, 2011, 7:13pm UTC](https://forum.inductiveautomation.com/t/dictionary-into-sql-row/3635/7 "2011-11-21T19:13:12Z")
As a script module dict [dict.py](https://us1.discourse-cdn.com/inductiveautomation/original/1X/67fb3305a6d0cfdcdf96f4fac7f21bd0bd012046.py) (345 Bytes) Used in an expression ```auto runScript(concat("app.dict.toDB(",toStr({TableName}),",",toStr({database}),",",toStr({dictionary}),")")) ``` Pretty sure all the parenthesis are in order. the concatenation is because the runScript function expects a string and you can’t insert a reference directly into the string. Ah well. ---
**Author:** ![jsham](https://avatars.discourse-cdn.com/v4/letter/j/ac91a4/32.png) [@jsham](https://forum.inductiveautomation.com/u/jsham)\ **Post date:** [November 21, 2011, 7:32pm UTC](https://forum.inductiveautomation.com/t/dictionary-into-sql-row/3635/8 "2011-11-21T19:32:28Z")
Thanks! So I could bind that runscript expression to a SQLTag and just change the scan class to determine the interval that I insert. Is there a good way to run something like this in a transaction group? I get lost on all the scope rules with Ignition scripting, but can I run my parsing script as a gateway timer script and store the resulting dictionary in a scope where I can then access the dictionary object from SQLTags? Then I can directly use the value, or bind it to a table or label, or I can write it to the DB using a transaction group, which would allow all of the good things that come with tx groups (triggering, etc). ---
**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:** [November 21, 2011, 7:51pm UTC](https://forum.inductiveautomation.com/t/dictionary-into-sql-row/3635/9 "2011-11-21T19:51:43Z")
You can actually run this as an expression item within a transaction group. 😃 The only thing you may want to consider is combining our two functions into a single one. I’m just not sure how an expression item would handle a dictionary. They’re not quite the same thing as a list. ---
**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:** [November 21, 2011, 8:10pm UTC](https://forum.inductiveautomation.com/t/dictionary-into-sql-row/3635/10 "2011-11-21T20:10:14Z")
Added two more functions: toQuery and toDataSet ```auto def toQuery(table, dict): dictKeys=dict.keys() dictValues=dict.values() query="INSERT INTO "+str(table) queryCols="(" queryVals="VALUES(" for x in range(len(dictKeys)): queryCols+=str(dictKeys[x])+"," queryVals+="'"+str(dictValues[x])+"'," return query+queryCols[:-1]+") "+queryVals[:-1]+")" def toDataSet(dict): import system dictKeys=dict.keys() dictValues=dict.values() rows=[] row=[] for y in range(len(dictKeys)): row.append(dictValues[y]) rows.append(row) print dictKeys print rows return system.dataset.toDataSet(dictKeys,rows) ``` To the IA guys, I have no idea yet on how to assign a dataset within a transaction group like this, though… will it return as a dataset object? EDIT: whoops! Meaning if I use the dataset function above… 😊