IronPython Script to access data from Web Service using HttpWebRequest and parse returned JSON with JavaScriptSerializer

from System.Net import HttpWebRequest
uri = "https://quantprice.herokuapp.com/api/v1.1/scoop/period?tickers=MSFT&begin=2012-02-19""
webRequest = HttpWebRequest.Create(uri)
response = webRequest.GetResponse()

The variable response now holds a JSON string.

In order to parse the JSON string, response has to first be converted into a "stream" using StreamReader()  so it can then be deserialized using the JavaScriptSerializer() and converted into a dictionary.

from System.IO import StreamReader
import clr
clr.AddReference('System.Web.Extensions')
from System.Web.Script.Serialization import JavaScriptSerializer

streamReader = StreamReader(response.GetResponseStream())

jsonData = streamReader.ReadToEnd()

js = JavaScriptSerializer()

dataDict = js.DeserializeObject(jsonData)

The data can then be looked up using tokens on dataDict:

myColName = []  # variable used to hold the column names
for val in dataDict["datatable"]["columns"]:
    myColName.append(val["name"])
textData =     "t".join(myColName) + "rn"

# build a string representing the data in tab-delimited text format
for quote in dataDict["datatable"]["data"]:
    #print "t".join(str(val) for val in quote)
    textData += "t".join(str(val) for val in quote) + "rn"

Now that the JSON data has been converted into a string, it now can be converted into a stream format that can then be added into a Spotfire Data Table.

# make a stream from the string
stream = MemoryStream()
writer = StreamWriter(stream)
writer.Write(textData)
writer.Flush()
stream.Seek(0, SeekOrigin.Begin)

Create a Spotfire Data Table column structure with the same column structure as the dataset format using the TextDataReaderSettings():

# set up the text data reader
readerSettings = TextDataReaderSettings()
readerSettings.Separator = "t"
readerSettings.AddColumnNameRow(0)
readerSettings.SetDataType(0, DataType.String)
readerSettings.SetDataType(1, DataType.String)
readerSettings.SetDataType(2, DataType.String)
readerSettings.SetDataType(3, DataType.String)
readerSettings.SetDataType(4, DataType.String)
readerSettings.SetDataType(5, DataType.String)
readerSettings.SetDataType(6, DataType.Date)
readerSettings.SetDataType(7, DataType.Currency)
readerSettings.SetDataType(8, DataType.Currency)
readerSettings.SetDataType(9, DataType.Currency)
readerSettings.SetDataType(10, DataType.Currency)
readerSettings.SetDataType(11, DataType.Real)

 # create a data source to read in the stream
 textDataSource = TextFileDataSource(stream, readerSettings)

# add the data into a Data Table in Spotfire
if Document.Data.Tables.Contains("Stock Data"):
     Document.Data.Tables["Stock Data"].ReplaceData(textDataSource)
else:
     newTable = Document.Data.Tables.Add("Stock Data", textDataSource)
     tableSettings = DataTableSaveSettings (newTable, False, False)
     Document.Data.SaveSettings.DataTableSettings.Add(tableSettings)

Attached is a visual of an example dxp that uses this code. It also leverages the text box to allow the users inputs to dynamically alter the input parameters to the Web Service API request. 

License:  TIBCO BSD-Style License