Insert query arguments

Ignition
1

Hi all, I’m relatively new to ignition and don’t come from a coding background but in my query where I am trying to write a dataset to a database table I don’t seem to understand what I need to put as my arguments to reference the dataset. Right now I just have them set with filler strings.

id = "id"
PurchaseOrderID = "PurchaseOrderID"
ProjectID = "ProjectID"
ItemCompanyID = "ItemCompanyID"
ItemDescription = "ItemDescription"
PurchaseQty = "PurchaseQty"
SumOfQtyReceived = "SumOfQtyRecevied"
QtyUnreceived = "QtyUnrecevied"

args =[id,PurchaseOrderID, ProjectID, ItemCompanyID, ItemDescription, PurchaseQty, SumOfQtyReceived, QtyUnreceived]

iquery = "INSERT INTO dbo.s40150_01_010 (id ,PurchaseOrderID, ProjectID, ItemCompanyID, ItemDescription, PurchaseQty, SumOfQtyReceived, QtyUnreceived) VALUES (?,?,?,?,?,?,?,?)" 

for row in range(len(newPyDataSet)):
          system.db.runPrepUpdate(iquery,args,'inventory')
2

If you don’t need any argument manipulation before running the query, I think you could do

for row in newPyDataSet:
	system.db.runPrepUpdate(iquery,row,'inventory')
3

Thank you. I was just misunderstanding that I didn’t did need any arguments. That worked like a charm and it now fills in as expected.

4

prepUpdate requires a list of values for arguments. The pydataset’s data is a list of lists so when you iterate over it, each row is a list, which is why passing row works.

You can address each value in the list by doing

for row in newPyDataSet:
    id = row['id']
    ...

or

for row in newPyDataSet:
    for value in row:
        print value
5

The best way to do this, is to actually build the query inside of a loop and then execute it afterwards. For performance reasons you don’t want to execute a single query for each row.

I would recommend that you do something like this:

iquery = 'INSERT INTO dbo.s40150_01_010 (id ,PurchaseOrderID, ProjectID, ItemCompanyID, ItemDescription, PurchaseQty, SumOfQtyReceived, QtyUnreceived) VALUES {}'
valueArgs = ','.join(['(?,?,?,?,?,?,?,?)'] * newPyDataSet.rowCount)

args = [value for row in pyData for value in row]

system.db.runPrepUpdate(iquery.format(valueArgs),args,'inventory')
1 Like
6

Just be careful to segment--there is a limit on the number of placeholders you can use. (4k, IIRC.)

2 Likes