Good day
I have some problem when i try to work with tables, short: I have data that i take from Mssql and then i need to insert them to MySQL. If some data will change i need to hanlde the changes(Delete, Update, Add). I try to write some code in script but i have problem
Here is some example of code:
def insert_update_tables():
query = "SELECT * FROM transfer_data"
args = []
result = system.db.runPrepQuery(query, args, 'DB')
query2 = "select cast(_IDRREF as bigint) as id, cast(_Fld11787RRef as bigint) as st_in, cast(_Fld11786RRef as bigint) as id_num, cast(_Fld13891RRef as bigint) as shift from _Document11782 where _Date_Time >=dateadd(day,-5,(DATEADD(year,-2000, SYSDATETIME()))) and _Marked =0 and _Fld11795=0 and _Posted =1"
args1 = []
result2 = system.db.runPrepQuery(query2, args1, 'ALK')
if result.getRowCount() > 0:
pydata = system.dataset.toPyDataSet(result2)
pydata1 = system.dataset.toPyDataSet(result)
arr = []
editSet = set(tuple(row) for row in pydata)
masterSet = set(tuple(row) for row in pydata1)
deletions = [list(row) for row in (masterSet-editSet)]
changes =[list(row) for row in (editSet-masterSet)]
if result.getRowCount() == result2.getRowCount() and changes > 0:
i = 0
for change_row in len(changes):
change_row_insert = change_row[0 + i]
query_take = "select cast(_IDRREF as bigint) as id, cast(_Fld11787RRef as bigint) as st_in, cast(_Fld11786RRef as bigint) as id_num, cast(_Fld13891RRef as bigint) as shift from _Document11782 WHERE id = {change_row_insert}"
i = i + 1
dataset_to_update = system.db.runPrepAuery(query_take, [], 'ALK')
if result.getRowCount() == result2.getRowCount() and changes == 0:
pass
if len(deletions) == 0:
pass
if result.getRowCount() == 0:
for index in range(len(result2)):
datacon = list(pdsData[index])
system.db.runPrepUpdate("INSERT INTO transfer_data(id, st_in, id_num, shift) VALUES(?, ?, ?, ?)", datacon, "DB")
Which part takes four minutes; the dataset manipulation(s) in your script, or the actual SQL update(s) you're running?
You're doing a lot of looping over the input datasets, so if they're very large it's absolutely going to bog down. Where are you running this code from, also? Definitely don't run it in a client/designer scope, or there's extra network handshake and serialization latency to deal with.
You should be able to do this completely within MySQL with one or two statements. Likely you need to do some kind of UNION between the two tables and write any updated/missing records to the desired table. A number of examples available on Stack Overflow for what you are asking.
I can`t do this is mysql, main point i take data from MsSQL and insert them into Mysql. But when some data change in MsSQL i need change them in Mysql immediately
My customer have some program "idk". This program send some data to MsSQL.
My task: transver this data to Mysql. Every 2 minute check if data change or not. If change i need to Update my table in Mysql
At the top i write some line of code, but idk how to solve this problem
Ignition is connected to both databases, right? If the data is maintained "live" in MS SQL, why do you need it in MySQL? Ignition can query either database.
Please explain why your customer needs this data in MySQL, instead of just using the data in MS SQL.
Yeah, connected to both, To be honest i dont know why they need in MySQl (maybe in future it will be some logick, idk)
I just have a task to do this, That's all
Thank for your help!
If they're trying to do a one time migration, then just do that - follow the article I posted above, migrate all the data over, and then exclusively use MySQL afterward. Or even in your designer if the script is slow - who cares because you're only running it once.
If they want replication, then use another MsSQL server and you have built-in options of setting that up outside of Ignition.
You're basically being asked to code replication of MsSQL into a MySQL database and 1) I don't see the point in that, there's probably bound to be some issue at some point tbh and 2) it's going to be a lot of work for something you can get built-in if you just use another MsSQL server instead of MySQL
