This is my first attempt at using an API. I went with Python and everything seemed to work — I was able to send 17,500+ records to a vendor and they are showing up perfect on their website. The problem is that the vendor is using different IDs then we are. I work for a public school system and this is for our bus transportation. I pass the vendor bus stops and in their response they pass me the ID that was created. I then have to take that ID and pass it into a SQL Server procedure to update a cross reference table. I tested this by just sending a single record and it worked great, so I turned on the process to push all 17,500+. When I checked the cross reference table, I’m seeing unexpected results. Of the 17,500 records, only 13 got updated with the Vendor ID. On top of that, there is a status field that should have gotten updated from “New” to “Processed”, and it didn’t get updated even in the 13 records that got the Vendor ID updated.

I don’t know what I did wrong, but I’m hoping somebody can spot the issue. Here is my python code:

import pyodbc as db 
import requests

url = "<vendor URL>"

def GetData():
    connVendorData = db.connect(<Driver Info>)
    connVendorData.setdecoding(db.SQL_CHAR, encoding='latin1')
    connVendorData.setencoding('latin1')
    
    cursor_Vendor = connVendorData.cursor()
    cursor_Vendor.execute('''exec get_addressBookUpdates''')

    for row in cursor_Vendor: 
        payload = {
            "geofence": { "circle": {
                    "latitude": str(row.latitude),
                    "longitude": str(row.longitude),
                    "radiusMeters": str(row.radius)
                } },
            "formattedAddress": row.formattedAddress,
            "longitude": str(row.longitude),
            "latitude": str(row.latitude),
            "name": row.addressBookTitle
        }
        headers = {
            "accept": "application/json",
            "content-type": "application/json",
            "authorization": "<vendor token>"
        }

        response = requests.post(url, json=payload, headers=headers)

        connMyCompanyData = db.connect(<Driver Info>)
        connMyCompanyData.setdecoding(db.SQL_CHAR, encoding='latin1')
        connMyCompanyData.setencoding('latin1')

        cursor_MyCompany = connMyCompanyData.cursor()
        cursor_MyCompany.execute('''exec update_stopIDs_xref_AfterProcessing ?, ?''', row.status, response.text)
        
GetData()

And here is the stored procedure that is updating the cross reference table:

alter PROC update_stopIDs_xref_AfterProcessing
@status VARCHAR(25),
@json NVARCHAR(MAX)

AS

SET NOCOUNT ON 

--Test Parms
--DECLARE @json nvarchar(MAX)
--DECLARE @status VARCHAR(25) = 'New'

--SET @json = '{
--  "data": {
--    "id": "137279769",
--    "name": "MILLGATE RD @ LA GRANGE RD",
--    "createdAtTime": "2024-05-12T15:12:47.880383692Z",
--    "formattedAddress": "MILLGATE RD @ LA GRANGE RD",
--    "geofence": {
--      "circle": {
--        "latitude": 38.27088968,
--        "longitude": -85.57484079,
--        "radiusMeters": 25
--      }
--    },
--    "latitude": 38.27088968,
--    "longitude": -85.57484079
--  }
--}'

--Load the parms into a #temp table 
DROP TABLE IF EXISTS #temp 
SELECT  JSON_VALUE(@json, '$.data.id')                  vendorID,
        JSON_VALUE(@json, '$.data.formattedAddress')    formattedAddress,
        JSON_VALUE(@json, '$.data.createdAtTime')       createdTime
INTO    #temp 

--Update stopIDs_xref depending on the processing that happened 
IF @status = 'New'
    UPDATE  stopIDs_xref
    SET     vendorId = t.vendorID,
            status = 'Processed'
    FROM    stopIDs_xref sx
            JOIN #temp t ON t.formattedAddress = sx.formattedAddress 
ELSE IF @status = 'Update'
    UPDATE  stopIDs_xref 
    SET     status = 'Processed'
    FROM    stopIDs_xref sx
            JOIN #temp t ON t.formattedAddress = sx.formattedAddress
ELSE IF @status = 'Delete'
    DELETE 
    FROM    stopIDs_xref 
    FROM    stopIDs_xref sx
            JOIN #temp t ON t.formattedAddress = sx.formattedAddress