Dictionary into SQL row

Ignition
1

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

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…

2

[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

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?

3

More like each key is a column, want to insert the value (value = dict[key]) items in a row.

4

So if my dictionary result is

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

INSERT INTO table (t_stamp, Bob, Joe, Sally)
VALUES ('2011-11-11 11:11:11', 89.9, 188.72, 21.44);
5

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.

6

Using your example I made this script to construct the query:

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

As a script module dict
dict.py (345 Bytes)

Used in an expression

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.

1 Like
8

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).

9

You can actually run this as an expression item within a transaction group. :smiley:

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.

10

Added two more functions: toQuery and toDataSet

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… :blush:

2 Likes