Shared Script to insert or update data in a database

Ignition
6

I don’t have the time to go through all of it at once but here is something that may get you started. You feed it the table name you want to insert into and a dataset. The header for the dataset has to match the column names you want to insert into but it will create a single insert query for all the data in the dataset. I’m sure it can be cleaned up more but threw it together real quick. Hopefully it helps get you started.

def in_query(tName,dVals):
#tName is the table name you want to insert into, dVals is a dataset.  The headers must match the column names you want to insert into.
	colr = system.dataset.getColumnHeaders(dVals)
	cols = ','.join([str(elem) for elem in colr]) 
	cCnt = len(colr)
	dVals = system.dataset.toPyDataSet(dVals)
	iVals = None
	
	for x in dVals:
		rData = "("
		for y in range(cCnt):
			if y == 0:
				rData += str(x[y])
			else:
				rData += "," + str(x[y])
				
		rData += ")"
		if iVals:
			iVals += ',' + rData
		else:
			iVals = rData
	
	query = "INSERT INTO %s (%s) VALUES %s" % (tName,cols,iVals) 	
	system.db.runQuery(query)

** I tested that the string it creates looks right but can’t do a database insert currently to test that part of it.