Python Variable Result to SQL Table

• Paul Stasny

  Mr or Mrs. 500

  Points: 599

  More actions

  May 8, 2020 at 6:25 pm

  #3750029

  Greetings,

  I am very new to using Python but am using the following script in SQL 2019 to get data from SurveyMonkey via SSMS. I'm not sure how to get this data out of the python surveys_json variable into a Nvarchar(Max) column I have created in SQL. I'd like to take the entire Json string and then use SQL scripts to parse this. I will be doing this on a regular basis and was thinking of putting this into a SP and running it via SSIS. I realize there are commercial options for this but that is not possible now. I've played with the OUtput Data Sets and results sets but can't get it to work. Any advice would be appreciated.

  EXECUTE sp_execute_external_script

  @language = N'Python'

  , @script = N'

  import requests

  import json

  client = requests.session()

  headers = {

  "Authorization": "bearer %s" % "apikey",

  "Content-Type": "application/json"

  }

  data = {}

  HOST = "https://api.surveymonkey.net"

  SURVEY_LIST_ENDPOINT = "/v3/surveys/"

  uri = "%s%s" % (HOST, SURVEY_LIST_ENDPOINT)

  surveylist = client.get(uri, headers=headers)

  surveys_json = surveylist.json()

  print(surveys_json)

  '

• Site Owners

  SSC Guru

  Points: 80325

  More actions

  May 9, 2020 at 7:10 pm

  #3750209

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.