AVG04
1
a rookie here. would like to get your inputs on my current situation:
I currently have a tag dataset that is identical to a table on the SQL [1Row x 30 Columns].
tag dataset is updated with the modifications made on its properties and I want to update the SQL table after pressing a button on the ignition.
I would like to know if there is a better way of updating the SQL table than using the below query?
UPDATE mytable SET column1 = 'new value 1', column2 = 'new value 2', column3 = 'new value 3', ..., column16 = 'new value 16' WHERE id = 1;
Doing this makes me think that it is better to use a UDT tag than a tag dataset.
Are you aware of runPrepUpdate
?
https://docs.inductiveautomation.com/display/DOC81/Inserting+Data+into+a+Database
query = "INSERT INTO table (col1, col2, col3) VALUES (?,?,?)"
args = [value1, value2, value3]
system.db.runPrepUpdate(query, args)
Tip: for nicely formatted code in your forum posts use the </> button.
AVG04
3
I did looked into that, but what I want to do is to replace the values in an existing row (Row 1). I do think I need a WHERE condition for my query.
The SQL table will just function as a temporary data storage and the data will be deleted and replaced after performing some ignition tasks.
Thanks for the tip!
Oops. I forgot what the question was. There's this too:
https://docs.inductiveautomation.com/display/DOC81/system.db.runUpdateQuery
You should be able to add the WHERE in there.
You should be able to use a WHERE in runPrepUpdate(), as well
AVG04
6
Thanks for your inputs! Will check what works best for my setup.
from itertools import chain
def update_row(table, row_id, **args):
marks = ', '.join("?=?" for _ in args)
values = list(chain.from_iterable(args.items()))
q = "update {} set {} where id = {}".format(table, marks, row_id)
return system.db.runPrepUpdate(q, values)
data = {
'column_name': value,
'foo': 42,
'bar': 21,
'pox': 12
}
update_row('table', row_id, **data)
# or
update_row('table', row_id, column_name=value, foo=42, bar=21, pox=12)