pyodbc azure sql server error in saving json using openjson

Question

i want to save a json array into azure sql server, but i am getting this error:

\serverCalls\sqlManager.py", line 95, in populateNewsLatest curse.execute(f""" pyodbc.Error: ('07002', '[07002] [Microsoft][ODBC Driver 17 for SQL Server]COUNT field incorrect or syntax error (0) (SQLExecDirectW)')

here is the json i want to save:

[
  {
    "source": {
      "id": null,
      "name": "Slashdot.org"
    },
    "author": "msmash",
    "title": "World's Biggest Crypto Fortune Began With a Friendly Poker Game",
    "description": "An anonymous reader shares a report: The Abu Dhabi Grand Prix draws princes, movie stars and world-famous athletes every year to party on Yas Island, the entertainment hub about 30 minutes from the center of downtown. Mingling among them last month was a figu\u2026",
    "url": "https://slashdot.org/story/22/01/10/143214/worlds-biggest-crypto-fortune-began-with-a-friendly-poker-game",
    "urlToImage": "https://a.fsdn.com/sd/topics/bitcoin_64.png",
    "publishedAt": "2022-01-10T14:03:00Z",
    "content": "The Abu Dhabi Grand Prix draws princes, movie stars and world-famous athletes every year to party on Yas Island, the entertainment hub about 30 minutes from the center of downtown. Mingling among the\u2026 [+1785 chars]"
  }, ... ]

sqlmanager.py:

def populateNewsLatest(jsonpacket):
    # print(jsonpacket)
    with pyodbc.connect('DRIVER=' + driver +
                        ';SERVER=tcp:' + server +
                        ';PORT=1433;DATABASE=' +
                        database +
                        ';UID=' + username + ';PWD=' + password) as conn:
        conn.autocommit = True
        with conn.cursor() as curse:
            if curse.tables('newslatest').fetchone():
                # delete if table exists
                curse.execute('DROP TABLE [dbo].[newslatest]')
                print('delete news latest/update')
                # create new instance
                curse.execute(
                    'CREATE TABLE newslatest(author varchar(28),content varchar(max),description varchar(max),publishedAt varchar(28),title varchar(28),url varchar(28),urlToImage varchar(28),id varchar(28),name varchar(28));')
                json_string = json.dumps(jsonpacket)
                print(json_string)
                # dump json data to sql
                curse.execute(f"""
                DECLARE @json NVARCHAR(MAX)
                SET @json=N'{json_string}';
                INSERT INTO [dbo].[newslatest]
                SELECT * FROM OPENJSON(@json) WITH(author varchar(28) '$.author',content nvarchar(max) '$.content',description nvarchar(max) '$.description',
                publishedAt varchar(28) '$.publishedAt',title varchar(28) '$.title',url nvarchar(max) '$.url',
                urlToImage nvarchar(max) '$.urlToImage',id varchar(28) '$.source.id',name varchar(28) '$.source.name');""")
                return {"message": "successfully updated"}
            else:
                print('first time creation news latest')
                curse.execute(
                    'CREATE TABLE newslatest(author varchar(28),content varchar(max),description varchar(max),publishedAt varchar(28),title varchar(28),url varchar(28),urlToImage varchar(28),id varchar(28),name varchar(28));')
                json_string = json.dumps(jsonpacket)
                curse.execute(f"""
                               DECLARE @json NVARCHAR(MAX)
                               SET @json=N'{json_string}';
                               INSERT INTO [dbo].[newslatest]
                               SELECT * FROM OPENJSON(@json) WITH(author varchar(28) '$.author',content nvarchar(max) '$.content',description nvarchar(max) '$.description',
                               publishedAt varchar(28) '$.publishedAt',title varchar(28) '$.title',url nvarchar(max) '$.url',
                               urlToImage nvarchar(max) '$.urlToImage',id varchar(28) '$.source.id',name varchar(28) '$.source.name');""")
                return {"message": "successfully updated(first creation)"}

Using an f-string and `SET @json=N'{json_string}'` leaves you open to SQL injection. What happens if `json_string` contains `'{"name": "O\'Rourke"}'`? Instead, use a parameter placeholder (`?`) and pass the JSON string as a parameter to the `.execute()` statement. — Gord Thompson, Jan 10 '22 at 16:21
@GordThompson i tried using placeholder and replaced the json_string like `SET @json=N?;` but i am getting this error now :`'42S22, "[42S22] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name 'N@P1'. (207) (SQLExecDirectW); [42S22] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)` — utkarsh choudhary, Jan 12 '22 at 15:47
Try `SET @json=?;` (although you really don't need that variable; you can just use the `?` parameter placeholder in the statement itself. — Gord Thompson, Jan 12 '22 at 17:57
thanks , I definitely don't need the extra variable , I just used the placeholder directly. — utkarsh choudhary, Jan 13 '22 at 13:42

score 0 · Answer 1 · answered Jan 12 '22 at 10:11

Below are the few things which needs to be checked:

As Gord suggested check for the json strings.
We can use executemany to run the command if we have more data, as execute might lead to count issues.

Also you can use it in below way:

 sql = "INSERT INTO #temptable VALUES (........)" 
 vals = data.to_numpy().tolist()
 con.executemany(sql_insert, vals)

Along with the above suggestions refer to this SO and discuss.dizzycoding answers. thanks to the answered techies.

pyodbc azure sql server error in saving json using openjson

1 Answers1