CODEWITHSUNDEEP
pythonsqlsql-serverjson
William
William

Reputation: 415

Inserting JSON Data into SQL Server with Python

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.

Upvotes: 3

Views: 26473

Answers (1)

Martijn Pieters
Martijn Pieters

Reputation: 1123420

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.

Upvotes: 2

Related Questions