I'm looking for some advice with a project. I have written a short program to access some APIs in order to get JSON. What I would really like (need really!) is to send that JSON to SQL Server either as a new table or into an existing one.
I have installed the pyodbc package and can insert random bits and pieces but the JSON won't go anywhere. The JSON, after being subject json.dumps() is a dictionary of three lists, one list of which contains 584 further dictionaries with largely identical keys. It is these keys that I want to serve as my table columns, with the values for each of the 584 dictionaries being the rows.
cnxn = pyodbc.connect(r'DRIVER={SQL Server};Server=SERVER;Database=DB;Trusted_Connection=yes;')
cursor = cnxn.cursor()
cursor.executemany("INSERT INTO TEST (Id1, Id2) VALUES (%(Id1)s, %(Id2)s)", data['BLAH'])
data is the list of dictionaries subset from the JSON I need to access. I've read on stack overflow that a list of dictionaries is fine but the error message tells me "Params must be in a list, tuple, or row" even though my dictionaries are already in a list.
I then tried playing around with SQLAlchemy which is highly regarded but the learning curve is pretty steep. Surely there is a reasonably straightforward way to send some JSON into SQL Server?!
