使用Python脚本进行API POST调用时出现内部服务器错误

Question

我正在尝试编写这段代码来向组织发送API调用。然而，我正在构建的jsonRecord在使用print时显示了正确的值，但在通过API请求调用使用它时显示了一个错误。代码如下：

cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()

cursor.execute("set nocount on; set rowcount 1; select Right([Code], 5) as courseNumber,  PresentationNumber as presentationNumber, Left ([Date], 4) as fiscalYear, CordinatorEmail as [coordinatorInfo.email], CordinatorFName as [coordinatorInfo.firstName], CordinatorLName as  [coordinatorInfo.lastName],  CordinatorMName as  [coordinatorInfo.middleName], CordinatorTelNumber as [coordinatorInfo.telephone] from bctc.trainingtest.tblClassesALL where (PostCorStatus is NULL and CertType = 'POST') FOR JSON PATH, WITHOUT_ARRAY_WRAPPER")
 
row  = cursor.fetchone() 
while row:
 jRecord = row[0]
 print (jRecord)

 row = cursor.fetchone()
 
 url = "https://dev.post.ca.gov/CourseRostersWebService/Services/CourseRosters.svc/json/UpdateCoordinatorInfo"

 headers = {"Authorization":"Basic dxxxxxxxxxxxxxxx==","content-type":"application/json;charset=UTF-8"}
 
 
 y = requests.post(url, json = jRecord , headers = headers , verify=True)

 

 print(y.text)
 print(y.reason)
 print(y.json())
 print(y.status_code)
 print("response headers >>>>", y.headers)

下面是错误：

{"courseNumber":"30060","presentationNumber":"001","fiscalYear":"2021","coordinatorInfo":{"email":"mawad@test.org","firstName":"Maged","lastName":"Awad","middleName":"K","telephone":"(718)258-9631"}}
{"errorCode":100,"errorDescription":"Unspecified Error Occurred"}
Internal Server Error
{'errorCode': 100, 'errorDescription': 'Unspecified Error Occurred'}
500
response headers >>>> {'Cache-Control': 'private', 'Content-Type': 'application/xml; charset=utf-8', 'Server': 'Microsoft-IIS/10.0', 'X-AspNet-Version': '4.0.30319', 'X-Powered-By': 'ASP.NET', 'X-UA-Compatible': 'IE=edge', 'Date': 'Wed, 08 Sep 2021 16:15:34 GMT', 'Content-Length': '65'}

我还想提一下，当我将jRecord硬编码为json记录时，它起作用了。

不确定构建该对象是否错误，因为它可以正确显示。

请帮帮忙好吗？

Answer 1

我需要一次从SQL表中拉出一条记录来处理和发送API调用。我通过简单地添加下面这行代码修复了这个问题：

jRecord2 = json.loads(jRecord)

这会将JSON SQL输出从字符串转换为JSON。

否则，请求调用将无法识别object SJON格式，并且在将调用发送到端点之前会失败。

谢谢你的帮助。