Inserting JSON Data into SQL Server with Python

Question

I have a python script that makes a call to an API, submits a request, and then is supposed to insert the result into a Sql Server 2012 table. When it goes to execute the insert into SQL, it breaks. I am currently importing json, requests, and pyodbc into the file. Here is the location where it is breaking:

conn = pyodbc.connect('DRIVER={SQL Server};SERVER={localServer};DATABASE={localDB}')
cursor = conn.cursor()
for record in response:
    print(json.dumps(record))
    cursor.execute("Insert Into Ticket_Info values ?", json.dumps(record))
    cursor.commit()
cursor.close()
conn.close()

It is at the cursor.execute() line where the breakage occurs. This is the error I got when I attempted to run this.

pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '@P1'. (102) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (8180)"

Any help I could get I would appreciate. I have searched and tried several different methods at this point, the only thing that changes is the error.

Solution

The second argument to cursor.execute() must be a sequence of values to interpolate, one for each SQL parameter in your statement.

You gave ODBC a string instead, which is also a sequence, but one that contains (many) more elements (characters) than your query requires.

Use a single-element tuple here:

cursor.execute("Insert Into Ticket_Info values (?)", (json.dumps(record),))

I also put parenthesis around the values section, as per the SQL Server INSERT syntax:

VALUES
Introduces the list or lists of data values to be inserted. There must be one data value for each column in column_list, if specified, or in the table. The value list must be enclosed in parentheses.

Unless Ticket_Info has only one column per row (unlikely, you'd have a primary key column at least), you probably need to specify what column you are inserting your value into:

cursor.execute("Insert Into Ticket_Info (<columnname>) values (?)", (json.dumps(record),))

where you need to replace <columnname> with the actual column name in your table.

Comments

[William]

I executed the script again as: 'for record in response: print(json.dumps(record)) cursor.execute("Insert Into Ticket_Info values ?", (json.dumps(record),)) cursor.commit() cursor.close() conn.close()' I am still getting the same error.

[Martijn Pieters]

@William: there was a second problem I now addressed as well; the parenthesis after VALUES is required.

[William]

Sweet! Got rid of that error, but now I have a new one. pyodbc.Error: ('21S01', '[21S01] [Microsoft][ODBC SQL Server Driver][SQL Server]Column name or number of supplied values does not match table definition. (213) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (8180)') Any Ideas?

[Martijn Pieters]

@William: You didn't specify a column list; INSERT INTO TICKET_INFO (<column name>) VALUES (?).

[William]

Well I am wanting it to insert into every column, if it has data for that column, otherwise it can be null. The table is built where almost every column will allow null values except for the PK. Do I just need to list every column in the table?

[Martijn Pieters]

@William: but you only have one value, a JSON string.

[William]

Correct, that String has a large list of tickets, and each ticket has several items under it, I am wanting one ticket per row, with each ticket item going into it's own column.

[Martijn Pieters]

@William: Then you are going about inserting that data entirely the wrong way. JSON is not a format that your database server will see as separate rows and columns. To the database that's just one opaque piece of data to store in a column for you until such time you want to retrieve it again.

[William]

I marked the question as answered because the original issue was solved, thanks to you, but do you have any advice on a resources I can find about parsing out the JSON request so it can be inserted into the table?

[Martijn Pieters]

@William: You'll have to transform each entry into a sequence of parameters matching your column names per row; use None for empty columns. You can use executemany() for multiple inserts (use a nested structure).