I would like to receive some help with sending information from two different powertables into the same database table. Currently I am running the system.db.runPrepUpdate(“INSERT INTO (databasetablename) (Column A, Column B,… ColumnJ) VALUES (?,?,…?)”, [Value1, Value2,… Value3].
I am first turning my powertables in pydataset to iterate through them for the amount of rows I have using the following code:
Table1 = system.tag.read("")
Table1 = system.dataset.toPyDataSet(Table1.value)
for row in range(Table1.rowCount):
Col1=Table1.getValueAt(row,0)
Col2= Table1.getValueAt(row,1)
Col3 =Table1.getValueAt(row,2)
Col4= Table1.getValueAt(row,3)
system.db.runPrepUpdate()
for row in range(Table2.rowCount):
Col1=Table2.getValueAt(row,0)
Col2= Table2.getValueAt(row,1)
Col3 =Table2.getValueAt(row,2)
Col4= Table2.getValueAt(row,3)
system.db.runPrepUpdate()
However when I run this code the first powertable gets recorded correctly based on the number of rows I have but the second one just copies the last row and pastes those values into the rows for the second power table.
For example in my database table:
row1 a a
row2 b b
row3 c c
row 4 f f
row 5 f f
row 6 f f
Is there a better way to send data from two powertables to my database table?
I tried this method but received an error since my columns are not the same in each power table. Any way to add multiple columns in a one step process rather than having to go to each dataset and add the columns from using the system.dataset.addColumn?
If so I’d also look at building your update string dynamically to limit how many writes your doing to the database especially if your tables that you are writing from have a lot of data in them.
I am trying to put the data from both tables as well as some data from a couple tags I have into a db table. However the number of rows differ based on what the end user will enter. For example table one can have 2 columns and 3 rows while the next table has 2 different columns and 2 rows.
To account for different dataset rowCount’s, you can do something like this. For how I set this up you would want the columns to be a fixed number for each but as long as the column count doesn’t change something like this should work. If the column count changes it gets a lot more complicated.
As for the other tags. You can easily link them into each row by mixing them into this. If you only want them in the first row then you’d want to add an if statement for the first row.
This is really helpful! However my column count will change. One powertable will have 4 columns say (a, b , c ,d) and the next powertable will have the columns of (e, f) as they wont have the same columns yet I would like to display them in the same row on my database table.
If your column count is changing you can use Table#.getColumnCount() to figure out how many columns you have in the dataset then add another loop inside of the current for loop and build a list instead of individual tags. You can then use the length of your list to build your update query statement since it would have to be dynamic if your column count for the tables changes. I’d also try to lock down a max columns that you expect to see for each dataset thats being used to input. By doing that you can set it up so when your insert loop is built you always put the tables in the same columns in the table for consistency.
One other thing you can do, if your dataset column names are unique between the two tables, you can use Table#.getColumnName(#) to get the column name as you loop through, if you use the same column name in your database you can use it as part of building up your insert query string to set which column in the database to write it to.
You can use zip() to go over both datasets at the same time.
This assumes assumes two things…
The same number of rows in each tag.
All the column names are different.
… and creates a single query. Enter in your two tags at the top. It will print various things as it’s putting them together, so you can see what’s going on. When you’re ready to try writing to the database, just uncomment the last line.
I attempted to document everything it does, but I may still have overlooked something. Feel free to ask any questions you may have.
# Read the tags
tag1 = system.tag.read('').value
tag2 = system.tag.read('').value
# create PyDataSets to iterate over
Table1 = system.dataset.toPyDataSet(tag1)
Table2 = system.dataset.toPyDataSet(tag2)
# Concatenate all column names together
colNames = list(tag1.getColumnNames()) + list(tag2.getColumnNames())
print colNames
# Create a value string with the same number of
# question marks as there are columns (?,?,?,?)
# This will be used in our runPrepUpdate() later
singleValueString = '(' + ','.join('?' * len(colNames)) + ')'
print singleValueString
# Make enough value strings to cover all of the rows
valuesString = ','.join([singleValueString] * len(Table1))
print valuesString
# Create final query string
query = 'INSERT INTO tableName (%s) VALUES ' % ','.join(colNames) + valuesString
# runPrepUpdate requires that the passed vales be in a
# single flat list (i.e. NOT a list of lists).
# We'll start with an empty list and add to it as we go.
flatList = []
# The zip() function will let us iterate over both datasets at once.
for row1, row2 in zip(Table1, Table2):
# Combining the values in each row by adding them together.
# We can't add the rows directly, so we'll convert them to lists.
combinedValues = list(row1) + list(row2)
# += takes a value and adds it to itself. Doing this over
# all the rows will make a flat list of all the values
# to use in runPrepUpdate()
flatList += combinedValues
print query
print flatList
# Finally, write everything to the database.
#system.db.runPrepUpdate(query, flatList)
