Export a dataset to a sql table

Ignition
1

Hello! I’m trying to export a dataset to a sql table, here is my script:

TableData = event.source.parent.getComponent('Quote_Review').data
# Convert to PyDataset
Data = system.dataset.toPyDataSet(event.source.parent.getComponent('Quote_Review').data)

# Start
txID = system.db.beginTransaction(timeout=5000)

# Loop through the rows in the dataset, and insert them into the database.
for row in Data:
	column1 = row['Id']
	column2 = row['Quote']
	column3 = row['Top_Level_PN']
	column4 = row['Revision']
	column5 = row['Sold_TO']

sql = """
	INSERT INTO [Quotes].dbo.[Quote_Test]
		(Id, Quote, Top_Level_PN, Revision, Sold_TO)
	VALUES
		(?, ?, ?, ?, ?)
"""
system.db.runPrepUpdate(sql, [column1, column2, column3, column4, column5], tx=txID)
	
# Commit and close
system.db.commitTransaction(txID)
system.db.closeTransaction(txID)

The script works but it doesn’t iterate. The dataset I’m exporting has 5 rows (as an example) but the script only causes the last row to populate in the sql table. Any ideas?

1 Like
2

This looks like a simple indentation issue.

Based on your code, the only actions that will iterate are those that set the column variables because those are the only lines indented after the for loop declaration. If you indent the lines that specify and run the query, that should fix your issue.


TableData = event.source.parent.getComponent('Quote_Review').data
# Convert to PyDataset
Data = system.dataset.toPyDataSet(event.source.parent.getComponent('Quote_Review').data)

# Start
txID = system.db.beginTransaction(timeout=5000)

# Loop through the rows in the dataset, and insert them into the database.
for row in Data:
	column1 = row['Id']
	column2 = row['Quote']
	column3 = row['Top_Level_PN']
	column4 = row['Revision']
	column5 = row['Sold_TO']

    sql = """
	INSERT INTO [Quotes].dbo.[Quote_Test]
		(Id, Quote, Top_Level_PN, Revision, Sold_TO)
	VALUES
		(?, ?, ?, ?, ?)
    """
    system.db.runPrepUpdate(sql, [column1, column2, column3, column4, column5], tx=txID)
	
# Commit and close
system.db.commitTransaction(txID)
system.db.closeTransaction(txID)
2 Likes
3

You are right! Thanks so much for the help, I really appreciate it.

1 Like