JSON from API to SQL Server using Python

Valmont

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?!

Talisman

You can directly import JSON into the database. Have you looked at the JSON functionalities built into MS SQL Server? JSON Data (SQL Server)

off.the.walls

Have a look at sqlalchemy and something like marshmallow or flask-io they're good for interacting with databases.

Valmont

Talisman wrote: »

You can directly import JSON into the database. Have you looked at the JSON functionalities built into MS SQL Server? JSON Data (SQL Server)

I was aware of this but I need to automate the process - say once a week or something. If I sent a JSON file to the same location each week with a python program, could I schedule SQL server to pull it in of its own accord?

Valmont

off.the.walls wrote: »

Have a look at sqlalchemy and something like marshmallow or flask-io they're good for interacting with databases.

I've looked at SQLalchemy and I need to study some more before I can use it - a lot of the concepts are beyond me. Something to work towards up to Christmas I suppose. I've ordered Learn Python the Hard Way to fill in the gaps and also to gain a bit more of a theoretical understanding of how the language works. Hopefully this should let me use alchemy better.

Pelvis

Valmont wrote: »

I was aware of this but I need to automate the process - say once a week or something. If I sent a JSON file to the same location each week with a python program, could I schedule SQL server to pull it in of its own accord?

Once you have the T-SQL to complete what you need done, you can create a job in SSMS and schedule it to run whenever you need it to.

Start here.

https://msdn.microsoft.com/en-us/library/ms190268(v=sql.105).aspx