CODEWITHSUNDEEP
pythonsqlsql-serverpyodbc
DMAC
DMAC

Reputation: 17

Python PYODBC with OpenJson Issue

I am trying to run the following code in my Python script and it is failing with syntax errors I've not been able top correct. The first insert brings in json data from a web site and works perfectly. The second insert statement formats the json code and puts it in another SQL Table. This code works fine in MSSQL. The second insert refers to missing closing quote but I seem to have an opening and closing quote. It also refers to an unresolved reference openjson and unresolved reference cross apply. I've tried putting opening/closing brackets around both the insert and select statements but no luck. I looked through the pyodbc wiki on github but didn't see a similar example. What am I doing wrong here? Thanks.

cursor.execute("Insert Into InboundJson (json) values (?)", (json.dumps(response_json),))
conn.commit()  -- works fine

cursor = conn.cursor()
cursor.execute("Insert into [CCSBC-DW1].[SurveyMonkey].[dbo].Surveylist (SurveyId, Description, WebSite)

Select J_open.*
from
  dbo.InboundJson j
  cross apply
  openjson(j.[json], '$.data') with ( id          int ,
                                      title  varchar(200),

                                      href    varchar(200)) j_open;")
conn.commit()```

Upvotes: 0

Views: 312

Answers (1)

Parfait
Parfait

Reputation: 107707

Simply use triple-quoted enclosures for multi-line string:

cursor.execute("""INSERT INTO [CCSBC-DW1].[SurveyMonkey].[dbo].Surveylist (SurveyId, Description, WebSite)
                  SELECT J_open.*
                  FROM dbo.InboundJson j
                  CROSS APPLY OPENJSON(j.[json], '$.data') 
                              WITH (id int,
                                    title varchar(200),
                                    href varchar(200)) j_open;
               """)
conn.commit()

Upvotes: 1

Related Questions