JSON Blob into SQL Server-CodePudding

I have data from a web API that I want to store in a SQL database. Each record in the web API looks like this:

>>> inci[1]
>>> Out[47]: 
{'@odata.etag': 'W/"97914784"',
 'cfius_cta_basisforminorityinterestcontrol_7': False,
 'cfius_excludefromanticipatedcaselog': False,
 'cfius_signgenerateddocuments': False,
 'cfius_mitigationterminationrqstcmttee': False,
 'firstresponseslastatus': 1,
 'cfius_cta_typeoftransaction_5': True,
 'cfius_anticipatedstatus': 'Pending Assignment',
 'cfius_totaldaycount': 1,
 'cfius_frzdys_daycountreview': 0,
 'cfius_draftnoticestagecommentsdue': '2021-12-17T05:00:00Z',
 'resolvebyslastatus': 1} # many many many more columns

The web API has 2500 records like this one above. I want to store each record as a BLOB into a Blob table I created on SQL Server.

tabCreate = ("""
             CREATE TABLE IncidentBLOB
             (incidentID UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
              incidentBLOB varbinary(max)
              )
             """)
             
cur.execute(tabCreate)

cnxn.commit()

A friend advised me to save as a json dump, but even that is not working:

for incident in inci:
    cur.execute("""INSERT INTO (IncidentBLOB) VALUES (?)""", (json.dumps(incident), ))
>>> ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '('. (102) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)")

I have been checking different sources, and tried different string formatting methods but nothing seems to work. Please shed some light on my path.

CodePudding user response：

That query is invalid. Should be of the form INSERT INTO TableName(Column1, Column2) VALUES (?,?), so something like:

 cur.execute("""INSERT INTO IncidentBLOB(IncidentBLOB) VALUES (?)""", json.dumps(incident))