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.

Martijn Pieters · Accepted Answer · 2014-04-17 17:34:19Z

2

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.

edited Apr 17, 2014 at 17:34

answered Apr 17, 2014 at 17:20

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

William Over a year ago

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 Over a year ago

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

William Over a year ago

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 Over a year ago

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

Martijn Pieters Over a year ago

@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).

|

Collectives™ on Stack Overflow

Inserting JSON Data into SQL Server with Python

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related