Update a database (SQL) with data from Spotfire

[christopher sawtelle 2]

Hi all,

 

Can someone point me in the direction of how to do this. I would like to bring in data to Spotfire from Excel and then populate it to a SQL database. Periodically I will bring in new data from Excel to add to the current data and rewrite to the data base. Nothing fancey here just a straight forward job.

 

Thanks.

[Sean Riley]

Please see this blog post with a sample IronPython script for writing back to a database from Spotfire:

 

http://spotfired.blogspot.com/2014/04/write-back-to-database-from-spotfi...

 

Script:

 

from Spotfire.Dxp.Data.Import import DatabaseDataSource

from Spotfire.Dxp.Data.Import import DatabaseDataSourceSettings

from Spotfire.Dxp.Application.Visuals import TablePlot

from Spotfire.Dxp.Application.Visuals import VisualTypeIdentifiers

from Spotfire.Dxp.Data import IndexSet

from Spotfire.Dxp.Data import RowSelection

from Spotfire.Dxp.Data import DataValueCursor

from Spotfire.Dxp.Data import DataSelection

from Spotfire.Dxp.Data import DataPropertyClass

 

rowCount = Document.ActiveDataTableReference.RowCount

rowsToInclude = IndexSet(rowCount,True)

 

#Get a cursor to the two columns we want to use. cursor1 is for the key column and cursor2 is for the column selected by the user input

cursor1 = DataValueCursor.Create[int](Document.ActiveDataTableReference.Columns["ProductID"])

cursor2 = DataValueCursor.CreateFormatted(Document.ActiveDataTableReference.Columns[whichCol])

 

#The following section will add a column to the database table using the name specified by the user. This assumes a column with this name does not already exist.

sqlCommand = "ALTER TABLE Products ADD " + colName + " varchar(50);"

dbsettings = DatabaseDataSourceSettings( "System.Data.SqlClient",

"Server=localhost;Database=Northwind;UID=myuser;PWD=mypass",sqlCommand)

ds = DatabaseDataSource(dbsettings)

newDataTable = Document.Data.Tables.Add("temp",ds)

Document.Data.Tables.Remove(newDataTable)

 

#The following section will update the specified column in the database using the key column in the where clause

sqlStr=""

for row in Document.ActiveDataTableReference.GetRows(rowsToInclude,cursor1,cursor2):

value1 = cursor1.CurrentValue

value2 = cursor2.CurrentValue

sqlStr = sqlStr + "UPDATE Products SET " + colName + "='" + value2 + "' WHERE (ProductID=" + str(value1) + ");"

 

sqlCommand = "UPDATE Products " + sqlStr + ";"

dbsettings = DatabaseDataSourceSettings( "System.Data.SqlClient",

"Server=localhost;Database=Northwind;UID=myuser;PWD=mypass",sqlCommand)

ds = DatabaseDataSource(dbsettings)

newDataTable = Document.Data.Tables.Add("temp",ds)

Document.Data.Tables.Remove(newDataTable)

[Victoria M]

Would I absolutely need to create database connection string in the script What if I already have a table I want to add data to and it is loaded into dxp using information link No need to create columns, only add data rows. Basically either use 'insert into table' comand or run stored procedure which will insert data row by row.