Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
If we do not hit our goal we will be forced to close the site.

Current status: https://keepboardsalive.com/

Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.

JSON from API to SQL Server using Python

  • 05-09-2017 07:14PM
    #1
    Registered Users, Registered Users 2 Posts: 5,857 ✭✭✭


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


Comments

  • Registered Users, Registered Users 2 Posts: 7,151 ✭✭✭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)


  • Registered Users, Registered Users 2 Posts: 1,298 ✭✭✭off.the.walls


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


  • Registered Users, Registered Users 2 Posts: 5,857 ✭✭✭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?


  • Registered Users, Registered Users 2 Posts: 5,857 ✭✭✭Valmont


    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.


  • Closed Accounts Posts: 1,747 ✭✭✭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


  • Advertisement
Advertisement