Well, here goes again, I am trying in vain to insert multiple rows to SQLServer using pyodbc executemany. the data is in a dict.
When I do this I get
#pyodbc.ProgrammingError: The second parameter to executemany must be a sequence, iterator, or generator.
I can't imagine why a dict would not be iterable, I mean what's the point of it? I have tried converting the dict to a dataframe, no go there. Tried switching from Python3.7 to 3.6, no go there! Can't find anyone to contact at pyodbc, anyone have any idea why this thing does not work, has anybody gotten it to work with executemany? Is there another package I can use?
Anyway sorry for the verbosity any help appreciated!
Here is what Iam using
When I do this I get
#pyodbc.ProgrammingError: The second parameter to executemany must be a sequence, iterator, or generator.
I can't imagine why a dict would not be iterable, I mean what's the point of it? I have tried converting the dict to a dataframe, no go there. Tried switching from Python3.7 to 3.6, no go there! Can't find anyone to contact at pyodbc, anyone have any idea why this thing does not work, has anybody gotten it to work with executemany? Is there another package I can use?
Anyway sorry for the verbosity any help appreciated!
Here is what Iam using
import requests from requests.auth import HTTPDigestAuth import json from pandas.io.json import json_normalize import pandas as pd from pandas import ExcelWriter import random import pyodbc def getDev(): url="https://blahblah/v1/devstuff" headers = {"content-type": "application/json", "Authorization": "Bearer sadsadsad"} requester = requests.get(url, headers=headers) dict3 = requester.json() #Convert to JSON connStr= pyodbc.connect('DRIVER={SQL Server};Server=nnnn;Trusted_Connection=True;DATABASE=aaaa') stmt = connStr.cursor() for key, value in dict3.items(): for idx, x in enumerate(value): myDict["remotecontrol_id"], idx myDict["device_id"],idx myDict["alias"], idx myDict["groupid"], idx myDict["online_state"], idx stmt.executemany("""INSERT INTO dbo.DevReport(remotecontrol_id, device_id, alias,groupid, online_state)) values(?, ?, ?, ?, ?, ?, ?)""", myDict) #Error:Must be sequence, iterator, or generator. connStr.commit() cursor.close() connStr.close() getDev()