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.


Answer: 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](http://technet.microsoft.com/en-us/library/ms174335.aspx):

> `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.

