Inserting the last row of a pydataset into a database table

Ignition
, ,
1

Hi:

I want to insert the last row of a pydataset into an existing database. However, my current script adds the entire dataset each time it is executed. What modifications can I make to my script (below) to ensure only the last row of the dataset is inserted into the database table?

Script:

pydata = system.dataset.toPyDataSet(data)
strQuery = "INSERT INTO Results (t_stamp, serial_numbers, avg) VALUES (?,?, ?)"

for row in pydata:

	t_stamp = row["Time Stamp"]
	serial_numbers = row["Serial Numbers"]
	avg = row["Ave"]
	args = [t_stamp, serial_numbers, avg]
	system.db.runPrepUpdate(strQuery, args)

Additional Information: To avoid DUPLICATE KEY errors, I removed the PRIMARY KEY from my serial_number column. I would like to have a PRIMARY KEY column either with an AUTO-INCREMENT INDEX or my serial_number column.

Thank you.

2

Hi! Welcome to the forum. :slight_smile:

The quickest change would be to move the runPrepUpdate out of the loop:

for row in pydata:
	t_stamp = row["Time Stamp"]
	serial_numbers = row["Serial Numbers"]
	avg = row["Ave"]
	args = [t_stamp, serial_numbers, avg]

system.db.runPrepUpdate(strQuery, args)
1 Like
3

A bit shorter version.

lastRow = pydata[len(pydata)-1]
t_stamp = lastRow["Time Stamp"]
serial_numbers = lastRow["Serial Numbers"]
avg = lastRow["Ave"]

system.db.runPrepUpdate(strQuery,  [t_stamp, serial_numbers, avg])
4

Last one. I promise!

To answer your other question that I quite overlooked about adding a primary key column, (I’m gonna assume MySQL, since you didn’t mention what you’re using.)

ALTER TABLE table_name ADD new_column int NOT NULL AUTO_INCREMENT primary key
5

Hi!

Thanks for welcoming me to the forum; much appreciated!

That’s exactly what I was looking for! Thanks!