How to escape characters in JSON response for SQL INSERT FROM OPENJSON via PYTHON

Question

Getting error when sending API JSON response to SQL Server db due to apostrophe in the values ('). See second JSON response for operator_country_lar

Here is a sample API response:

json_response= '{
"num_results": 455161,
"results": [
    {
        "activity_date": "1975-12-01",
        "activity_id": "50",
        "activity_name": "ORDERED",
        "operator_country_lar": "France",
        "registered_owner_name": "AIR FRANCE"
    },
    {
        "activity_date": "1974-10-01",
        "activity_id": "50",
        "activity_name": "ORDERED",
        "operator_country_lar": "Korea, Democratic People's Republic of",
        "registered_owner_name": "KOREAN AIR LINES"
    }
],
"results_this_page": 2,
"status": 200}'

Serializing the response and executing:

query = "DECLARE @json nvarchar(max)= "+json_response+" INSERT INTO "+self.database+"([activity_date],[activity_name],[operator_name]) 
SELECT * FROM OPENJSON(@json, '$.results') WITH (activity_date date, activity_name nchar(25), operator_name nchar(256))"


cursor.execute(query)
cnxn.commit()

The first instance works fine, but the second set in the response throws an error. How do I escape the apostrophe or is there a more accurate way to write the SQL OPENJSON statement in PYTHON?

Answer 1

This search and replace worked. It may not be perfect but it's relatively fast in processing json. Hope it helps someone.

self.response = json.loads(json.dumps(self.response.json()).replace("'", ""))

Please share below if you find a better solution.