All pages from an API into ONE JSON response and Insert in to a table in Snowflake by using Python

Question

I am new to both Python and SNowflake, and I did read the related topics and tried all of them, but it doesn't work for me. The API has a pagination restriction and I was able to get all the data from all pages by using a loop in Python. For some CDC reasons, I want to combine all of the JSONs from each page in ONE JSON object and insert it into a table in Snowflake. I use the code below for getting the data and pushing it to Snowflake:

results=[]
for i in range(1,total_pages+1):
       URL="*********/?page={}".format(i)
       response=requests.get(URL,headers=headers)
       data=response.json()["results"]
       for k in data:
            results.append(k)
   

    
    
def js(obj):
     text = json.dumps(obj, sort_keys=True, indent=4)
      return text
    
final=js(results)
                            
ctx=snowflake.connector.connect(
                               user='**',
                               password = '***',
                               account = '****',
                               warehouse = '****',
                               database = '****',
                               schema = 'LOAD',
                               role='SYSADMIN')
cs=ctx.cursor()
                
try:
cs.execute("insert into test_json_load (select PARSE_JSON('%s'),CURRENT_TIMESTAMP(0) )" % json.dumps(final))
                
               
finally:
cs.close()
ctx.close()

If I use the response the code works with no issue, but it writes the last page to the table as one JSON object. When I use the final, it gives me :

ProgrammingError: 001003 (42000): SQL compilation error: parse error line 1 at position 21,288 near '34'. syntax error line 1 at position 21,290 unexpected '\n'. parse error line 1 at position 21,319 near '34'. syntax error line 1 at position 21,322 unexpected '"'. syntax error line 1 at position 21,327 unexpected '\n'. parse error line 1 at position 21,352 near '34'. syntax error line 1 at position 21,360 unexpected '\n'. parse error line 1 at position 21,383 near '34'. parse error line 1 at position 21,416 near '34'. syntax error line 1 at position 21,418 unexpected '\n'. parse error line 1 at position 21,441 near '34'. syntax error line 1 at position 21,456 unexpected '\n'. parse error line 1 at position 21,486 near '34'. parse error line 1 at position 21,519 near '34'. syntax error line 1 at position 21,521 unexpected '\n'. parse error line 1 at position 21,544 near '34'. syntax error line 1 at position 21,551 unexpected '\n'. parse error line 1 at position 21,575 near '34'. syntax error line 1 at position 21,587 unexpected '\n'. parse error line 1 at position 21,608 near '34'. parse error line 1 at position 21,618 near '34'. syntax error line 1 at position 21,620 unexpected '\n'. parse error line 1 at position 21,635 near '34'. syntax error line 1 at position 21,643 unexpected '\n'. parse error line 1 at position 21,665 near '34'. syntax error line 1 at position 21,673 unexpected '\n'. parse error line 1 at position 21,694 near '34'. parse error line 1 at position 21,707 near '34'. syntax error line 1 at position 21,709 unexpected '\n'. parse error line 1 at position 21,730 near '34'. syntax error line 1 at position 21,738 unexpected '\n'. parse error line 1 at position 21,755 near '34'. parse error line 1 at position 21,776 near '34'. syntax error line 1 at position 21,778 unexpected '\n'. parse error line 1 at position 21,807 near '34'. syntax error line 1 at position 21,815 unexpected '\n'. parse error line 1 at position 21,840 near '34'. syntax error line 1 at position 21,848 unexpected '\n'. parse error line 1 at position 21,871 near '34'. parse error line 1 at position 21,904 near '34'. syntax error line 1 at position 21,906 unexpected '\n'. parse error line 1 at position 21,929 near '34'. parse error line 1 at position 21,937 near '110'. syntax error line 1 at position 21,944 unexpected '\n'. parse error line 1 at position 21,974 near '34'. parse error line 1 at position 22,007 near '34'. syntax error line 1 at position 22,009 unexpected '\n'. parse error line 1 at position 22,032 near '34'. syntax error line 1 at position 22,039 unexpected '\n'. parse error line 1 at position 22,063 near '34'. This input had 7995 total errors.

I have checked the final file and its a valid JSOn.

Would you please help me with this problem?

Thank you in advance for your help.

Answer 1

Copy and paste the JSON text into a JSON Formatter and Validator. This will help you analyze where your JSON is badly formed.

https://jsonformatter.org/ https://jsonformatter.curiousconcept.com/ https://elmah.io/tools/json-formatter/ https://jsonlint.com/

First of all make sure that it is real JSON, with no single quotation marks like this: {'key': 'value'}. While this looks like JSON, it is not. JSON requires double-quotes for all strings.